VBA Path Query

[CarlStephens]

The two code lines below are from a macro that does an excel to word mail merge, that references the word and the xlsm files saved locations. The code locates .docx file no issues, but it cannot locate the .xlsm file on my One Drive account, however, if I put on my local drive the code finds the path no problems. Any thoughts on the latter code and what the fix is?

Thank you.

Set wdocSource = wd.Documents.Open("https://carnivalcorp-my.sharepoint....s_Docs/HR_Email_One_Docs/Visa Mail Merge.docx")

strWorkbookName = "https://carnivalcorp-my.sharepoint....urn_co_uk/Documents/Joiners_Docs/Tracker.xlsm"

 

[severynm]

The code locates .docx file no issues, but it cannot locate the .xlsm file on my One Drive account, however, if I put on my local drive the code finds the path no problems

That's because you're not going to be able to do much with HTTP URLs for your documents with VBA. For files synced to your computer in your personal OneDrive, you also need to use a local path, something like C:\Users\%username%\OneDrive\MrExcel.xlsm.

If it's just for you, you can hard code in the username, otherwise if multiple people might have to use the file, you can substitute in the username, like this (its the same idea for SharePoint and OneDrive).

 

[CarlStephens]

That's because you're not going to be able to do much with HTTP URLs for your documents with VBA. For files synced to your computer in your personal OneDrive, you also need to use a local path, something like C:\Users\%username%\OneDrive\MrExcel.xlsm.

If it's just for you, you can hard code in the username, otherwise if multiple people might have to use the file, you can substitute in the username, like this (its the same idea for SharePoint and OneDrive).Thank

 

[CarlStephens]

Thank you for your reply. The path needs to be a URL as there are other users outside of my network that need to use the VBA/file, and the C:\Users path will only work for me. The VBA code works to open the word.docx but not the .xlsm file, which is weird. I have other VBAs that reference a One Drive URL when attaching files stored on my One Drive, and they work fine too. I think it is something to do with the StrWorkBookName function personally.