Search code examples
vbaexcelms-wordonedrive

How to open file on my Onedrive with VBA if I don't have internet connection?

I have a macro that opens a file on my Onedrive. If I am currently connected to the Internet, the macro runs perfectly fine. When I am disconected from the internet the macro is unable to open the file, even if I am referencing a path to my local Drive. For example

Set wdApp = New Word.Application
'make word Visible'
wdApp.Visible = True
wdApp.Activate
'open File'
wdApp.Documents.Open (D:\OneDrive\file.dotx")
Solution

  • Use ENVIRON to retrieve the current user folder or the local OneDrive folder.

    Set wdApp = New Word.Application
'make word Visible'
wdApp.Visible = True
wdApp.Activate
'open File'
wdApp.Documents.Open environ("OneDrive") & "\file.dotx"

    By default, the local OneDrive folder should be a top level folder within the user folder.

    Set wdApp = New Word.Application
'make word Visible'
wdApp.Visible = True
wdApp.Activate
'open File'
wdApp.Documents.Open environ("USERPROFILE") & "\OneDrive\file.dotx"

    Open a command window and type SET at the command prompt to see the current environment variables. A full list of env vars is also available within the System applet under System Summary, Software Environment, Environment Variables.