CODEWITHSUNDEEP
excelvba
Abhishek Jain
Abhishek Jain

Reputation: 27

Unable to Open the onedrive Excel file using VBA Code

I have a excel file on onedrive location and I have written a function first to replace the "/" with "\" in the file sharepoint path. However I am trying to open the file using below syntax, but unable to open

Set objLogExcel = CreateObject("Excel.Sheet")

Set objLogWorkbook = objLogExcel.Application.Workbooks.Open(path,False,False)

The above line is adding .xlsx extension in the file path, any suggestion ?

Upvotes: 1

Views: 3770

Answers (1)

FaneDuru
FaneDuru

Reputation: 42236

Try this way, please:

  1. Firstly, manually open the workbook in discussion;

    In the open workbook VBE put the next code, in any module:

    Sub testFullName() Debug.Print ThisWorkbook.FullName End Sub

In this way you will have the online characteristic full name. Something like this: "https://d.docs.live.net/fdb7a15aac1d9134/TestWorkbook.xlsx".

  1. Copy the above full name like it is and put it in the next Sub:

    Sub testOpenWBOneDrive() Dim wbFullName, objLogExcel As Object Set objLogExcel = CreateObject("Excel.Application") objLogExcel.Visible = True wbFullName = "https://d.docs.live.net/fdb7a15aac1d9134/TestWorkbook.xlsx" objLogExcel.Workbooks.Open (wbFullName) End Sub

Of course, you must replace the example full path with yours, obtained as explained above...

Edited: In order to be able to use the code for both cases (Online - Offline) you have to proceed in the next way:

  1. Determine if an internet connection exists:

Copy, please the next function declaration on top of your module (in the declarations area):

Private Declare PtrSafe Function InternetGetConnectedState Lib "wininet.dll" _
                            (ByRef dwflags As Long, ByVal dwReserved As Long) As Long

Use the next function in that module:

Private Function isInternetConON() As Boolean
  isInternetConON = InternetGetConnectedState(0&, 0&)
End Function

Then, use the next code to deal with OneDrive workbook opening, even offline:

Private Sub testOpenOneDriveOnlineOffline()
  Dim sfilename As String, Xl As Object, xlsheet As Object
  sfilename = "https://d.docs.live.net/fdb7a15aac1d9134/Test.xlsm"
 Set Xl = CreateObject("Excel.Application") ' or Set xl = CreateObject("Excel.Sheet")
     Xl.Visible = True

  If isInternetConON Then
    Set xlsheet = Xl.Workbooks.Open(fileName:=sfilename, ReadOnly:=False)
 Else
    'In case of internet connection beeing down:
    'Note: It works only if you uncheck:
    'OneDrive -> More -> Settings -> Office "Use Office applications to sync Office files that I open!
    Dim sLocalODPath As String
    sLocalODPath = Environ("onedrive") & "\" 'If your workbook is not in the OneDrive folder root,
                                             'you have to add the other folder(s), to build the path
    sfilename = Right(sfilename, Len(sfilename) - InStrRev(sfilename, "/")) ': Debug.Print sfilename: Stop
    Set xlsheet = Xl.Workbooks.Open(fileName:=sLocalODPath & sfilename, ReadOnly:=False)
 End If
End Sub

Upvotes: 4

Related Questions