Excel VBA tip 4: Open several files and transfer data to a destination file

This is the fourth in a series of 7 blog posts explaining how to speed up data transformation in Excel VBA with a few simple techniques.

1. Copy cells with VBA
2. Add data to a table
3. Open a file and transfer data to a target file
4. Open a file and transfer data to a destination file
5. Save data in a recordset
6. Use a recordset to read data from multiple files without opening them
7. Create a recordset based on data from 2 different worksheets

This blog post is a variation on the example from our previous blog post, where we transferred data from one file to a target file. This time, we’ll transfer data from multiple files. This means we’ll have to refer to a folder. You can add the folder name in the code or have the end user select it in a dialog.

First, we’ll show you how to open several files in a folder, close them after transferring the data and clean up the object variables you used. After that, we’ll discuss transferring the data; we’ve covered the basics of this process in the third blog post.

Let’s start by selecting the folder which contains the files we’re about to open. The first example will use a fixed folder name. The second example illustrates how to let the user select a folder.

Let’s start by creating three variables. One for the folder, one for the name of the file you want to open and one where you’ll store the workbook.

Dim myfolder As String
Dim myFile As String
Dim wbData As Workbook

The built-in ‘Dir’ function lets you transfer a file or folder to a variable. If the folder doesn’t contain the requested content, it’ll return an empty text string.

myfolder = “C:\Users\Pibo”
myFile = Dir(myfolder & “*.xlsx”)

This example uses a wildcard character ‘*’. When this task is executed, the first file that meets the requirements will be moved to the variable. Please note that this is just the filename at this stage. Later on, you’ll use this variable to open the file.

If you incorporate this in a loop, it’ll look as follows:

Do While myFile <> “” <> “”
Set wbData = Workbooks.Open(myfolder & “\” & myFile)
‘VBA Coding
wbData.Close False
Set wbData = Nothing
‘VBA Coding
myFile = Dir
Loop

The ‘myFile = Dir’ task will give the next file in the folder back to the variable. It’s similar to the ‘Next’ command in a ‘for-next’ loop. The loop stops when there are no files left (myfile = “”).

Tip: Try to close the opened file as quickly as possible. If you want to edit the data, it’s better to copy the data to the target file first, close the source file and then edit the target file.

We’ll complete the code with three extra variables and the material from the previous blog post of this series. The example below copies all data from the opened file to the target file. We’ll also need to determine the correct row number every time to copy the new data correctly.

Sub OpenMultipleFiles()
Dim fldr As FileDialog
Dim myfolder As String
Dim myFile As String
Dim wbData As Workbook
Dim wbSource As Workbook
Dim wsSource As Worksheet
Dim lngRow As Long
Application.ScreenUpdating = False
Set wbSource = ActiveWorkbook
Set wsSource = wbSource.Worksheets(1)
myfolder = “C:\Users\Pibo”
myFile = Dir(myfolder & “\*.xlsx”)
lngRow = wsSource.Cells(1, 1).CurrentRegion.Rows.Count + 1
Do While myFile <> “”
Set wbData = Workbooks.Open(myfolder & “\” & myFile)
wbData.Sheets(1).Cells(1, 1).CurrentRegion.Copy wsSource.Cells(lngRow, 1)
wbData.Close False
Set wbData = Nothing
lngRow = wsSource.Cells(1, 1).CurrentRegion.Rows.Count + 1
myFile = Dir
Loop
Set wsSource = Nothing
Application.ScreenUpdating = True
End Sub

In this example, I’ve opened 14 identical files with 264 cells of data each. The above code takes about 6 seconds on my device. The size of each file is 9 Kb. You lose a lot of time physically opening the files. In the example, this takes as long as opening the file itself. And it happens 14 times.

In the next blog post of this series, we’ll show you how to keep track of data in a recordset. The blog post after that teaches you how to use this recordset to extract data from a file without physically opening it.

Extra: If you don’t want to add a fixed folder to your code and want to let the user choose instead, you can use the example below:

Dim fldr As FileDialog
Dim myfolder As String
Dim myFile As String
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
.Title = “Select a Folder”
.AllowMultiSelect = False
.InitialFileName = “C:\Users\Pibo”
If .Show <> -1 Then Exit Sub
myfolder = .SelectedItems(1)
End With
myFile = Dir(myfolder & “\*.xlsb”)

The ‘FileDialog FolderPicker’ is a dialog window that’s similar to ‘Open File’, but it it lets you select a folderinstead of an individual file. The user selects a folder (but doesn’t open it) and confirms their choice with ‘OK’.

The other properties in the code are as follows:

• ‘AllowMultiselect’: False indicates that the user is only allowed to select one folder.
• ‘InitialFileName’: Refers to the folder shown in the dialog window.
• ‘Show’: If the user decides to click ‘Cancel’, then show = true.
• ‘SelectedItems(1)’: The name of the first item in the selection (only one folder here) is saved in the ‘myfolder’ variable.

Next blog post: How to save data in a recordset (5/7).

Did reading this blog make you hungry for more Excel or Power BI? Be sure to check out our courses.