How do I get data from an external worksheet when it's hosted on an Office 365 OneDrive For Business Account?

Question

I'm using the Office 365 Excel Online feature of being able to create an online form (that is publicly shared) to collect responses that are directly saved to a table on a worksheet in the workbook.

To analyse the data I've added extra worksheets employing VBA code and Form Controls. However, the Form Controls are not supported by Excel Online, so I think I need two workbooks. One to store the form data and the other to run analysis on the data. The file containing the form has to remain on Office 365 Onedrive For Business for the feature to work. If I leave the Form Controls in and try to open the file in Excel Online (to edit/share the form), I have to edit a copy that removes the controls - it won't ignore them.

The analysis workbook will be opened by multiple people on multiple PC's. I've enabled anonymous access to the form file and this is what Onedrive gives me: https://carlisleft-my.sharepoint.com/personal/dmartin_carlisleft_onmicrosoft_com/_layouts/15/guestaccess.aspx?docid=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx&authkey=XXXXXXXXXXXXXXXXXXX

I've tried using Get External Data and New Query From File and specifying this URL but I either get access to the Web interface that doesn't download any data or a Sharepoint table related to the URL but not the actual data that I need.

I'm now thinking of using VBA to download a copy of the file, open it locally in the background, read the table data, copy it into the analysis file and then delete the download, close the connection. I'm assuming with the right code, I can make proper use of the docid and authkey.

I've used Application.GetOpenFilename and Workbooks.Open() for locally stored files but it doesn't work for this. I've found this answer Access Shared Files in Onedrive For Business but it's not VBA and not very clear. I also found this that talks about WebDav but is that what I need to do?

Has anyone got any ideas or found a successful way to get Excel data from a OneDrive for Business excel file? I have to be able to get it to open on any Windows 10 PC. So if I have to map network drives, they have to be done on the fly.

Answer 1

It looks as if this problem is not so straight forward. My solution was to turn it on its head. Rather than have two separate files that have to communicate with each other. Just keep a single file that gives Excel Online what it wants; a file saved without form controls.

Using the information from a site that has a very good tutorial and examples for creating form controls with VBA. I was able to write procedures that created the form controls every time I started the worksheet in Excel Desktop. Before the file is saved, they're removed. Excel Online doesn't run the VBA code, so doesn't see any controls and opens the file. Excel Desktop runs the code and creates the controls so that users can use them in the interface.

Just use the Workbook_Open() and Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) events in the ThisWorkbook object to get Excel to run your code.

The code to delete all the form controls for the BeforeSave looks a bit like this:

Sub deleteAll()
'deletes all msoFormControl in the current Workbook
Dim shpControl As Shape
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
For Each shpControl In ws.Shapes
If shpControl.Type = msoFormControl Then
shpControl.delete
End If
Next shpControl
Next ws
End Sub