Excel Macro Enabled Spreadsheets or Excel Binary Worksheets

[Chris Waller]

Hi All,

I work for a big organisation that is moving from using a folder structure to save files to using SharePoint. I believe Excel macros won't work in SharePoint, however, if we open these Excel Spreadsheets with the desktop app, the macros can be run. The question I have is, is there a way to identify a spreadsheet that will work in the desktop app ie the code is self contained? Is there a way to identify spreadsheets that will require some remediation because the VBA is referring to something outside the current spreadsheet? Perhaps you may know of a different way of identifying these problems. TIA

 

[severynm]

I believe Excel macros won't work in SharePoint, however, if we open these Excel Spreadsheets with the desktop app, the macros can be run

Two points here:

1. VBA macros absolutely work for files stored in SharePoint, however, this brings us to point 2.
2. There's a difference between where files are stored and how they are opened. At the business I work at, all common files are stored in a SharePoint directory. Now, because these files are technically online, you have the option to open them either via the web app (in a web browser) or with the excel desktop app. If you open the SharePoint file with the desktop app, you have the full functionality of excel (including macros). You are absolutely correct that if you open the file with the web app macros will not work - but this is not because the file is stored in SharePoint; it is because of the tool you are using to edit the file.

The best way to differentiate between the files with and without VBA is to be very careful about file extensions: *.xlsx files will not have any VBA, while *.xlsm files would. For this reason I might suggest avoiding using *.xlsb, because that extension allows it to be ambiguous whether the file contains any VBA or not.

If you're in the process of transitioning to SharePoint, then I'm afraid you'll have to just go file by file and examine the code to see what needs to be changed. If you are interacting with other excel files and need to do that in SharePoint too, you might want to refer to the following thread:

Running Code on sharepoint

I have some excel workbooks with VBA to amongst other things run code “onopen event” to open a workbook with a database I use for data validation. I have been asked to upload these to sharepoint but how do I get the code to open the documents in excel rather than a web page, and how do I find...

www.mrexcel.com