Consulting

Results 1 to 9 of 9

Thread: VBA Path Query

  1. #1
    VBAX Regular
    Joined
    Dec 2021
    Posts
    8
    Location

    VBA Path Query

    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")

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

  2. #2
    VBAX Expert
    Joined
    Aug 2004
    Posts
    776
    Location
    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

  3. #3
    VBAX Regular
    Joined
    Dec 2021
    Posts
    8
    Location
    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.

  4. #4
    VBAX Sage
    Joined
    Apr 2007
    Posts
    8,098
    Location
    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

    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  5. #5
    VBAX Regular
    Joined
    Dec 2021
    Posts
    8
    Location
    Thanks Paul. I tried that too, but it didnít work.

  6. #6
    VBAX Expert
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    625
    Location
    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.c...ker.xlsm?web=1
    https://carnivalcorp-my.sharepoint.c...rge.docx?web=1

    Might be worth a try.

    Cheers
    I was not told it was impossible, so i did it.

  7. #7
    VBAX Regular
    Joined
    Dec 2021
    Posts
    8
    Location

    Thumbs down

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

  8. #8
    VBAX Expert
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    625
    Location
    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
    I was not told it was impossible, so i did it.

  9. #9
    VBAX Regular
    Joined
    Dec 2021
    Posts
    8
    Location
    The spreadsheet is not password protected either. Iíll try your other suggestion. Thank you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •