none
Office 365 - Excel VBA Macro - Programmatic Opening an Excel Workbook RRS feed

  • Question

  • Dear Community,

    In our SharePoint site, we have Password Protected Excel Workbooks in a Library. 

    One of these workbook (called "Summary Workbook") uses VBA code to gathers data from other workbooks in the SharePoint Library (all workbooks password protected). VBA code basically uses the "Workbooks.Open" construct supplying it with the password and path (password/path picked from within Summary Workbook itself).

    e.g.
    Set wbkProtected = Application.Workbooks.Open(Filename:=WorkBookPath, Password:=WorkbookPW, UpdateLinks:=False, ReadOnly:=True)

    This has been working fine so far until we migrated to Office-365. Due to "password protection" feature Office-365 can't open it online so it gives a dialog box asking to open it in Desktop Excel and then some additional messages and finally a dialog box for Office-365 credentials again. After these diaglog boxes, the summary sheet opens fine in desktop excel. But when we run macro to gather data from other workbooks using the above "Workbooks.Open" then:

    (a) Asks for Office-365 cloud credential again  
    (b) It does seem to accept credentials, but gives Macro error on the line following the "Workbooks.Open" (the line shown below). The error given is "Run-time error '9': Subscript out of range". 

       Set wbkProtected = Application.Workbooks.Open(Filename:=WorkBookPath, Password:=WorkbookPW, UpdateLinks:=False, ReadOnly:=True)
       Set PayPeriod = wbkProtected.Worksheets(RptMonth & MonthPart).Range("S7:S7")

    The error might be because the "Workbooks.Open" didn't execute successfully, it didn't also ask for all the dialog boxes etc.

    Appreciate any guidance, if there is a way to use VBA Macros to connect to cloud and open "password protected" Excel Workbooks.

    Thanks so much.

    Monday, August 29, 2016 2:19 PM

Answers

  • Hi David,

    Thanks so much for the efforts.

    Today we had a conference call with our Office 365 migration team and Microsoft Support Engineer and issue is now fixed with no changes to the VBA code (only changed the path of-course to point to the new sharepoint path in cloud).

    The exact same VBA code able to detect that I am already signed into cloud and didn't ask me for credentials again and was able to fetch the data from password protected Excel sheets stored in cloud.

    We tried a large number of options/settings etc, multiple restarts - but I think what might have made the difference is below:

    • Reset the browser by clicking on the gear icon of the browser > Internet options > Advanced tab > click on Reset > once the browser is reset, restart the computer 
    • We added the site to the trusted sites by clicking on the gear icon of the browser > Internet options > Security tab > Trusted Sites > Sites > add the SharePoint site *.sharepoint.com
    • In Internet Options > Security tab > Custom level > scroll to the bottom of the window and under User Authentication, we selected the option "Automatic logon with current username and password" > OK
    • In Internet Options > click on Advanced > select the "Use SSL 2.0" and "Use SSL 3.0" certificates > OK
    • We went to Control Panel > Credential Manager > we removed all "Microsoft Office16" credentials
    • We also went to the registry and followed the path- HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{9203C2CB-1DC1-482D-967E-597AFF270F0D}\InprocServer32
    • We renamed Interceptor.dll to OWSSUPP.dll
    • Restarted the computer 

    Thanks,

    Vivek

    • Proposed as answer by David_JunFeng Wednesday, August 31, 2016 8:00 AM
    • Marked as answer by David_JunFeng Thursday, September 8, 2016 1:16 AM
    Tuesday, August 30, 2016 4:27 PM

All replies

  • >>>Appreciate any guidance, if there is a way to use VBA Macros to connect to cloud and open "password protected" Excel Workbooks.

    According to your description, since this issue is complex, I'm trying to involve some senior engineers into this issue and it will take some time. Your patience will be greatly appreciated.

    Sorry for any inconvenience and have a nice day!
    Tuesday, August 30, 2016 5:44 AM
  • Hi David,

    Thanks so much for the efforts.

    Today we had a conference call with our Office 365 migration team and Microsoft Support Engineer and issue is now fixed with no changes to the VBA code (only changed the path of-course to point to the new sharepoint path in cloud).

    The exact same VBA code able to detect that I am already signed into cloud and didn't ask me for credentials again and was able to fetch the data from password protected Excel sheets stored in cloud.

    We tried a large number of options/settings etc, multiple restarts - but I think what might have made the difference is below:

    • Reset the browser by clicking on the gear icon of the browser > Internet options > Advanced tab > click on Reset > once the browser is reset, restart the computer 
    • We added the site to the trusted sites by clicking on the gear icon of the browser > Internet options > Security tab > Trusted Sites > Sites > add the SharePoint site *.sharepoint.com
    • In Internet Options > Security tab > Custom level > scroll to the bottom of the window and under User Authentication, we selected the option "Automatic logon with current username and password" > OK
    • In Internet Options > click on Advanced > select the "Use SSL 2.0" and "Use SSL 3.0" certificates > OK
    • We went to Control Panel > Credential Manager > we removed all "Microsoft Office16" credentials
    • We also went to the registry and followed the path- HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{9203C2CB-1DC1-482D-967E-597AFF270F0D}\InprocServer32
    • We renamed Interceptor.dll to OWSSUPP.dll
    • Restarted the computer 

    Thanks,

    Vivek

    • Proposed as answer by David_JunFeng Wednesday, August 31, 2016 8:00 AM
    • Marked as answer by David_JunFeng Thursday, September 8, 2016 1:16 AM
    Tuesday, August 30, 2016 4:27 PM