I have an Excel project with VBA macros, where I create a subfolder with a certain name in the folder containing the project xlsm file (unless the subfolder already exists). Then, I produce a host of new files that I to save in my subfolder. I use omething like FSO.CreateFolder(ThisWorkbook.path + "" + NewFolderName), and it worked great, till my company switched to syncing my Documents folder with OneDrive. Now, ThisWorkbook.path returns a string like "https://hfc-my.sharepoint.com/personal/[username]_[domain]/Documents/..." instead of "C:\Users\[username]\Documents\...". Note the URL instead of a drive specification, and slashes instead of backslashes. How do I fix this (apart from moving my project to a folder unaffected by OneDrive)?
Actually, I have wrapped FSO.CreateFolder into this function (lifted from another forum post, and working wonderfully up until now):
My problem is, the FSO.FileExists(path) and FSO.FolderExists(path) never turn true for these sharepoint paths, though the FSO.GetParentFolderName(path) works well with these paths.Function MyCreateFolder(path As String, Optional FSO As Object) As Boolean
If FSO Is Nothing Then Set FSO = CreateObject("Scripting.FileSystemObject")
If FSO.FileExists(path) Then
MyCreateFolder = False
ElseIf FSO.FolderExists(path) Then
MyCreateFolder = True
ElseIf MyCreateFolder(FSO.GetParentFolderName(path), FSO) Then
If FSO.CreateFolder(path) Is Nothing Then
MyCreateFolder = False
Else
MyCreateFolder = True
End If
Else
MyCreateFolder = False
End If
End Function
Bookmarks