VBA Path Query

[CarlUK]

The two code lines below are from a macro that does an excel to word mail merge, and each line references the word and the xlsm file 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, but I need it to be one my One Drive. 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 (https://carnivalcorp-my.sharepoint.com/:w:/r/personal/carl_stephens_seabourn_co_uk/Documents/Joiners_Docs/HR_Email_One_Docs/Visa%20Mail%20Merge.docx)")

strWorkbookName = "https://carnivalcorp-my.sharepoint....urn_co_uk/Documents/Joiners_Docs/Tracker.xlsm (https://carnivalcorp-my.sharepoint.com/%F0%9F%85%B1%EF%B8%8F/r/personal/carl_stephens_seabourn_co_uk/Documents/Joiners_Docs/Tracker.xlsm)"

[JKwan]

I don't recall if I got it to work directly with One Drive or not.... But this is how I got it to work.... Do a Sync with your One Drive and then point to your locally synced Excel file

[CarlUK]

Thank you. The path needs to be a http web address as otherwise people outside of my network need to use the file. If I put my local path, then they will not be able to access the file.

[Paul_Hossler]

Just guessing, but if the docx works, but the xlsm doesn't then I looked for differences in the pattern




https://carnivalcorp-my.sharepoint.com/:w:/r/personal/carl_stephens_seabourn_co_uk/Documents/Joiners_Docs/HR_Email_One_Docs/Visa%20Mail%20Merge.docx


https://carnivalcorp-my.sharepoint.com/%F0%9F%85%B1%EF%B8%8F/r/personal/carl_stephens_seabourn_co_uk/Documents/Joiners_Docs/Tracker.xlsm




The %20 are hex for a space so that's ok, but the :w: in the docx doesn't match the stuff in the xlsm


Can you revise the address of the xlsm?



https://carnivalcorp-my.sharepoint.com/:w:/r/personal/carl_stephens_seabourn_co_uk/Documents/Joiners_Docs/HR_Email_One_Docs/Visa%20Mail%20Merge.docx


https://carnivalcorp-my.sharepoint.com/:w:/r/personal/carl_stephens_seabourn_co_uk/Documents/Joiners_Docs/Tracker.xlsm

[CarlUK]

Thanks Paul. I tried that too, but it didn’t work.

[georgiboy]

I only get the '/:x/' and '/:w/' when I view the link in a specific way, I managed to get my links to export without that part, your links would look like the below if they were equivalent:

https://carnivalcorp-my.sharepoint.com/personal/carl_stephens_seabourn_co_uk/Documents/Joiners_Docs/Tracker.xlsm?web=1
https://carnivalcorp-my.sharepoint.com/personal/carl_stephens_seabourn_co_uk/Documents/Joiners_Docs/HR_Email_One_Docs/Visa%20Mail%20Merge.docx?web=1

Might be worth a try.

Cheers

[CarlUK]

Cheers fella. Tried it but alas it did not work.

[georgiboy]

Hmmm really not sure then. Is the Excel file password protected to open?

Other than that the only other suggestion I would have would be to clean the Office Document Cache as this has caused me issues before.

Hope you get it sorted soon

[CarlUK]

The spreadsheet is not password protected either. I’ll try your other suggestion. Thank you.