0

I turned on Reference (Microsoft Scripting Runtime), and successfully tested .CreateFolder into the Sharepoint structure ("https:\myCompany.sharepoint.com\sites...)

Two weeks later and it fails (error 52). I double-checked the Reference, but I can't figure out what's changed. In testing, I found using my local-path ("C:\Users\Dan\myCompany...) to the same directory in Sharepoint works. Obviously, hard-coding my path won't work for users! It appears using the "https" path has a permission problem, maybe?

I'm stuck. Any ideas would be greatly appreicated.

Dan
  • 11
  • 2
  • I should add that the VBA Excel line sSharepointPath = Application.ThisWorkbook.Path is how I was getting the "https:\\..." path to Sharepoint, not hard-coding it myself. – Dan Feb 21 '22 at 07:36
  • http paths do not work the same way as local/UNC file paths - they're two very different protocols. Some of that is abstracted away by Office (eg. you can open an Excel file from an http path) but it soon becomes apparent once you step away from that and into VBA... – Tim Williams Feb 21 '22 at 19:25
  • If you and your users have at least synchronized the folder containing the workbook to your PC's local drive using the OneDrive app, you can use [this solution](https://stackoverflow.com/a/73577057/12287457) to convert the result of `This Workbook.Path` to a local path and create the folders locally, where the OneDrive app will then automatically sync them back online. – GWD Sep 05 '22 at 23:31

1 Answers1

0

I had similar problems due to onedrive situation, there is need to locate local save of onedrive and than append location for your save to it. Here is snippet from my code:

    If ThisWorkbook.Path Like "https*" Then

    strEnviron = Environ("OneDriveCommercial")
    strWorkbookPath = Right(ThisWorkbook.Path, Len(ThisWorkbook.Path) - 31)
    strWorkbookPath = Right(strWorkbookPath, Len(strWorkbookPath) - (InStr(1, strWorkbookPath, "Documents", vbTextCompare) + 9))
    strEnvironFullPath = strEnviron & "\" & strWorkbookPath
Else
    strEnvironFullPath = ThisWorkbook.Path
End If
LukasJin
  • 1
  • Thanks for idea, but I looked and don't have a similar environment variable for Sharepoint. – Dan Feb 22 '22 at 03:12