Using VBA to process data from multiple files in Microsoft Excel
The video guide demonstrates an easy way to extract data from one file and use it in a second file. Although the video was made in Excel 2003, the macros used would still work today. If you have followed the instructions in the video or get AI to write similar code, you should be aware of some of the shortcomings of the method used.
Firstly the dialog box called to locate the Excel file would not work on a Mac. Secondly, it is common for Excel users to need to import data repeatedly from the same location so it may not be desirable to have to select the file location every time you run the macro.
The biggest technical issue in the guide is that it repeatedly looks for an active sheet or workbook. This is a shortcut that is not good programming practice, and it is not even viable when trying to extract data from multiple open workbooks.
One topic not covered in any detail is how to open different sorts of files with different sorts of restrictions such as password protection or formula links to other files. Another is how to safeguard against formatting changes in raw data files as the ideal automated process doesn’t require a user to visually check the source data prior to import.
What follows are some of the questions our experts get asked when trying to process multiple files in Excel. The video shows the simplest answer but we then discuss possible complications. A summary of those is listed in the table below:
How do I let Windows and iOS users locate Excel files to be processed by my macro?
The best option is generally Application.GetOpenFileName. The video guide shows how to call the familiar open file dialog box. The File Picker is one of four boxes available under Application.FileDialog. When the guide was produced, it was our experts’ preferred technique and it is still the most flexible technique available in Windows.
The problem is that it’s not available in iOS. When Excel for Mac 2011 was released without any VBA support that was not a major consideration but, in modern applications, we would prefer Application.GetOpenFileName. An advantage of the GetOpenFileName method is that a single line of code is required to display the pop-up box and record the selected file, such as MyFile = Application.GetOpenFileName. If a user doesn’t select a file, MyFile reads False.
As well as letting a user select a file, one benefit of using a dialog box over a hard coded path is that the system gets to read a path that’s compatible with the machine’s operating system. On Windows, folders are separated by slashes where on a Mac they may be separated by colons. Visual Basic can be written that converts paths to an appropriate format by referencing Application.PathSeparator but our consultants prefer to save that for when strictly required.
It would be nice if files could be located from the agnostic environment of Excel online. Unfortunately there is no native Office Scripts solution to the problem because Scripts cannot access the operating system to produce the required dialog boxes. The problem can be tackled by combining Office Scripts with Power Automate. As that only works within an organisation, it would generally just be adding complication for the sake of it.
How do I let users select a fixed location of a file to be processed?
The video guide starts off by recording a macro to open a CSV file. The code returns a file path in quotation marks. This can be replaced by a reference to an Excel cell or defined range containing the file path that the macro can then open as if it were written directly in the code.
The simplest version of this requires users to type a file’s address directly into an Excel cell. However, Windows explorer does not display full folder addresses or extensions by default so it can be awkward for users to work out where files live. Our consultants prefer the more user-friendly approach of letting users locate files themselves using Application.GetOpenFileName or the FileDialog as in the video. The code for processing a file gets split in two; the first portion puts InputFile in a cell. The second portion reads InputFile back from that cell.
There are two good triggers for selecting a file location. The most intuitive is to use a Worksheet_SelectionChange event that triggers when they click in the cell containing the location. The most visible is a button reading ‘Select File Location’ or similar.
If users aren’t selecting the file each time the process is run, it is possible the file may not be there. If so, any Workbooks.Open code referencing the old file location will fail. You can test for this by suppressing errors whilst the workbook is opened and then verifying that a workbook has opened. Something like ‘If ThisWorkbook.Name = ActiveWorkbook.Name Then’ works for opening a single file, and will return True if no file has been found. When opening multiple files, you might need to compare the count of workbooks open before and after attempting to open the file using Workbooks.Count.
A final complication may occur if multiple users are using the Excel tool and the data file being accessed is stored on Sharepoint. It is then possible that different users will have the file mapped to a different folder path including their username. Our experts can help with these more complex arrangements.
How do I confirm the file a user selects is structured correctly for my macro?
If you are trying to process a data export from third-party software, you will generally be reading from a table so can confirm the headers of the table are unchanged from when the macro was first built. If you are processing another Excel file that’s maintained in your organisation, you should add range names to mark the bits of the source file you need to read. If the required range names or headers are not found, you should prompt the user to check the source data and, if the format has changed, your macro may need updating.
The ideal source file is one in which all required data points are labelled. So if you need a monthly total from cell J100 on a Summary sheet, then there would be a corresponding range name ‘MonthlyTotal’. If someone inserts a column and the cell moves to K100, the ‘MonthlyTotal’ name moves at the same time. In the video guide, the value could be pulled from ActiveWorkbook.Sheets("Summary").Range("MonthlyTotal"), with any error indicating a format change.
The main problem is that you may not have any control over the source file. At Excel4Business, our clients often want to automate the processing of third-party files. If so, we assume cells will move around over time. If you want to read from the column headed Department then your code should scan for the Department heading. If it cannot find the required heading, the source file needs to be reviewed and the code may need to be rewritten. Sometimes that will be a matter of updating the text, such as from Department to Dept. Sometimes it may point to a deeper structural change in the data.
The technique of looking for column headings works very well when processing text exports such as CSV files, and can work with Excel exports from accountancy software. It does not work so well when a customer or supplier wants you to transfer your data into their own bespoke Excel spreadsheet. Our Excel experts are experienced in identifying appropriate markers in files and writing the code required to find them.
How do I automatically process a file that requires manual intervention on opening?
The Workbooks.Open command can be adapted to include any required passwords, automatically update links with other files, or set an appropriate read-only status for the file. If an opened file would need refreshing before you can extract data, you can use the RefreshAll command on the opened file in association with Application.CalculateUntilAsyncQueriesDone if there are any external data connections.
The reasons for adapting the Workbooks.Open command vary. If the file is password protected then, by default, Excel will ask for the password. If the user then enters the password wrong, it will cause the code to error. For that reason, it is good practice to prevent unnecessary pop-up windows appearing whilst code is running using Application.DisplayAlerts = False. If you plan to suppress the pop-ups, you will need to specify the password alongside the filename when opening the workbook.
Workbooks.Open also includes the optional parameters UpdateLinks and ReadOnly. Updating links is worthwhile when the source file contains formula links. The reason for opening the source file read only is that a source Excel file may be set to auto-save on Sharepoint. So, even if you follow the instructions in the video guide and close the file without saving changes, any alterations made by the code may already have been saved. This is most important when you have made deliberate changes to the source data in situ, but can also protect against coding errors made whilst developing the solution.
Similar to updating links, source files may contain PowerQuery tables or pivot tables that require active refreshing. If you are trying to read from a file that itself contains external connections then it may be that the solution’s architecture can be simplified by cutting out the intermediate workbook. Our consultants certainly try to avoid daisy chaining files together due to the risk of using stale data.
How do I reference different opened files without having to refer to active workbooks or sheets?
When a file is opened for processing, it becomes the active workbook and any selected sheet becomes the active sheet. There is the opportunity to assign a more permanent name to both using the Set command. The line ‘Set wbkData = ActiveWorkbook’ means you can reference wbkData instead of ActiveWorkbook. Whilst the newly opened file is active, the two are interchangeable. The benefit is that you are no longer forced to keep the second workbook active, and can even open further workbooks.
The advantage of writing code using the terms ActiveWorkbook and ActiveSheet is that they have a defined meaning in Visual Basic even when your code is not running. Being able to select properties from a dropdown list after typing ‘ActiveSheet.’ makes macros quick to write. Now we have AI assistance available, that advantage is somewhat negated. Therefore we would recommend always giving sheets and workbooks their own names using the Set command.
In theory you can call a workbook anything using the Set command. However, experienced programmers prefer to use terms like wkData, wbkData or variants, where the prefix is consistently used to identify workbooks. It means you can instantly identify a reference to a workbook even when code isn’t running. It also helps avoid the trap of trying to assign a generic name like ‘Data’ to a sheet and workbook at the same time.
Note that you don’t need to refer to active sheets or workbooks at all. You can use a line of the form ‘Set wbkData = Workbooks.Open Filename:=DataFile’ to streamline your code further. You would then define sheets within the opened workbook using an expression like ‘Set shtData = wbkData.Sheets(1)’ if there is only one sheet in the source data file. The argument for keeping the steps distinct is to guard against the possibility that Visual Basic might trip over itself and start trying to process a workbook before the Excel application had successfully opened it.
It is good practice to give a codename to each Excel sheet within a workbook using the Properties window in Visual Basic Editor. It is a natural extension to assign codenames to any sheet referenced by your code using the Set command. It is certainly faster to refer to shtData.Cells(1,1) than having to ensure your desired sheet is selected first e.g. ActiveWorkbook.Sheets("Data").Select before referring to ActiveSheet.Cells(1,1). It is also less prone to breaking as the preceding code develops.