This is the sixth in a series of 7 blog posts that delve deeper into how you can speed up data transformation in Excel VBA.
- Copy cells with VBA
- Add data to a table
- Open a file and transfer data to a target file
- Open a file and transfer data to a destination file
- Save data in a recordset
- Use a recordset to read data from multiple files without opening them
- Create a recordset based on data from 2 different worksheets
In the previous blog post, we explained how you can save data in a recordset. This time, we’ll show you how to use this technique to open all files in a folder and transfer their data to a target file. If you don’t know how to open a file, you can find this information in the previous blog posts in this series.
In this post, we’ll compare the traditional method (workbooks.open) to the recordset method.
For this example, I’m using 20 identical files, each containing 10 columns and 5,000 rows of data. The result will be a worksheet containing 10 columns and 100,000 rows. All files are in the same folder.
Note: For this solution, we’ll use a traditional list instead of a table. See blog post 2 in this series to learn how to copy data to a table.
First, write the code to check all files in a folder. This code doesn’t open the files; it just enters their names into the variable. You can display the name of each file in the Debug Window or use a ‘messagebox’. If the loop works, you can add data transfer to the code.
Sub GetDataFromFiles()
Dim fldr As FileDialog
Dim strPath As String
Dim myFolder As String
Dim myFile As String
strPath = “C:\Xylos\BLOG\” ‘ Dialog will start from this folder
Set fldr = Application.FileDialog(msoFileDialogFolderPicker) ‘ Select Folder, do not open folder
With fldr
.Title = “Select a Folder”
.AllowMultiSelect = False ‘ Select only 1 folder
.InitialFileName = strPath ‘ Folder location
If .Show <> -1 Then Exit Sub ‘ Quit when user cancels
myFolder = .SelectedItems(1)
End With
myFile = Dir(myFolder & “\*.xlsb”)
Do While myFile <> “” <> “”
Debug.Print myFile
myFile = Dir() ‘ Get Next File
Loop
End Sub
Keep in mind that the dialog box is meant to select a folder. Don’t double-click to view the folder content; instead, select the appropriate folder and click ‘OK’ to confirm.
Tip: In the above example, the command ‘Debug.print’ displays the name of each file in the folder in the Immediate Window. If the window isn’t open, you can open it with the shortcut ‘CTRL+G’.
Now you can add the code that opens each file in turn, copies the data and transfers them to the target file.
It’s best to start with the traditional method where files are opened, read and closed with the ‘workbook’ object.
The code now looks as follows:
Sub GetDataFromFiles()
Dim fldr As FileDialog
Dim strPath As String
Dim myFolder As String
Dim myFile As String
Dim wbMain As Workbook
Dim wsMain As Worksheet
Dim wbData As Workbook
Dim wsData As Worksheet
Dim lngPasteRow As Long
strPath = “C:\Xylos\BLOG\” ‘ Dialog will start from this folder
Set fldr = Application.FileDialog(msoFileDialogFolderPicker) ‘ Select Folder, do not open folder
With fldr
.Title = “Select a Folder”
.AllowMultiSelect = False ‘ Select only 1 folder
.InitialFileName = strPath ‘ Folder location
If .Show <> -1 Then Exit Sub ‘ Quit when user cancels
myFolder = .SelectedItems(1)
End With
Application.ScreenUpdating = False
Debug.Print Now
myFile = Dir(myFolder & “\*.xlsb”)
Set wbMain = ActiveWorkbook
Set wsMain = ActiveSheet
lngPasteRow = 2
Do While myFile <> “” <> “”
Set wbData = Workbooks.Open(myFolder & “\” & myFile)
Set wsData = wbData.Sheets(1)
wsData.Cells(1, 1).CurrentRegion.Copy wsMain.Cells(lngPasteRow, 1)
Set wsData = Nothing
wbData.Close False
lngPasteRow = wsMain.Cells(1, 1).End(xlDown).Row
myFile = Dir() ‘ Get Next File
Loop
Application.ScreenUpdating = True
Debug.Print Now
End Sub
It takes about 8 seconds to execute this code. Without ‘screenupdating’, it takes longer because all files are opened separately – you can even see them flash on the screen.
In the next example, we’ll use recordsets as we’ve explained in the previous blog post.
Sub GetDataFromFiles()
Dim fldr As FileDialog
Dim strPath As String
Dim myFolder As String
Dim myFile As String
Dim wbMain As Workbook
Dim wsMain As Worksheet
Dim wbData As Workbook
Dim wsData As Worksheet
Dim lngPasteRow As Long
‘ New variables
Dim myConnection As String
Dim RS As ADODB.Recordset
Dim mySQL As String
Application.ScreenUpdating = False
strPath = “C:\Xylos\BLOG\” ‘ Dialog will start from this folder
Set fldr = Application.FileDialog(msoFileDialogFolderPicker) ‘ Select Folder, do not open folder
With fldr
.Title = “Select a Folder”
.AllowMultiSelect = False ‘ Select only 1 folder
.InitialFileName = strPath ‘ Folder location
If .Show <> -1 Then Exit Sub ‘ Quit when user cancels
myFolder = .SelectedItems(1)
End With
Application.ScreenUpdating = False
Debug.Print Now
myFile = Dir(myFolder & “\*.xlsb”)
Set wbMain = ActiveWorkbook
Set wsMain = ActiveSheet
lngPasteRow = 2
Do While myFile <> “” <> “”
myConnection = “Provider=Microsoft.ACE.OLEDB.12.0;” & _
“Data Source=” & myFolder & “\” & myFile & “;Extended Properties=Excel 12.0”
mySQL = “SELECT * FROM [Sheet1$]”
Set RS = New ADODB.Recordset
RS.Open mySQL, myConnection, adOpenForwardOnly, adLockOptimistic
wsMain.Cells(lngPasteRow, 1).CopyFromRecordset RS
RS.Close
Set RS = Nothing
lngPasteRow = wsMain.Cells(1, 1).End(xlDown).Row
myFile = Dir() ‘ Get Next File
Loop
Application.ScreenUpdating = True
Debug.Print Now
End Sub
It now takes up to 4 seconds less to execute this code. In other words: if you need to read a long list of files, this method saves you a lot of time.
Next blog post: How to create a recordset based on data from 2 different worksheets (7/7).
Did reading this blog make you hungry for more Excel or Power BI? Be sure to check out our open Excel courses. See you soon in Antwerp or Brussels!
