This is the fifth 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 this blog post, we’ll explain how you can save data in a recordset. A recordset is a virtual matrix of rows and columns, which you can navigate using certain methods. Simply put: a recordset is a virtual table in your computer’s memory.
Recordsets are mainly used in database environments – for example, it’s an invaluable help when programming in MS Access. Recordsets are filled with SQL queries. A simple example is ‘Select * from Table’ (or ‘Select * from Range’ in Excel).
In this blog post, we’ll show you one way to use recordsets, but we won’t go into detail about everything you can do with them.
Recordsets are structured as follows:
‘BeginOfFile’ (BOF) and ‘EndOfFile’ (EOF) are markers that indicate whether you’re between the first and the last record in the recordset or not. If you save data in a recordset and both markers are ‘true’, your recordset is empty.
Each row consists of several columns that depend on the data structure you’ve added to the set.
There are two types of recordsets you can use. This depends on the type of database you’re using. This, in turn, is usually determined by the personal preference of the developer who will work with it.
The recordset types are called ADO and DAO. The methods to navigate them and read or edit data in both types are nearly identical. In this blog post, we’ll use ADO.
Recordsets are an alternative to retrieving data from another file or worksheet. You can save data in a recordset and immediately close the data file. After that, you can edit or analyse the data in the recordset.
Note: A recordset pulls data from a file. The file is only open while this action is being executed and is closed afterward. The advantage here is that you don’t have to pay attention to the file after retrieving the data you need. It won’t show up in the Task Manager and developers won’t need any code to close the file.
In this blog, we’ll show you two examples in which a recordset is used to collect and copy data. In the first example, we’ll open a large file with 5,000 rows and 100 columns.
Before you can start your code, you’ll need to reference the ADO library. (Microsoft ActiveX Data Objects 6.1 Library)
In the VBA interface, you can do this as follows:
References are listed alphabetically. When you reopen the References screen after selecting your reference, you’ll see your selection at the top of the screen.
The VBA code looks like this:
Sub CopyWithADODB()
‘ Reference to: Microsoft ActiveX Data Objects 6.1 Library
‘5000 Rows & 10000 Columns (File size 12,3 MB Type:Binary)
Dim myConnection As String
Dim RS As ADODB.Recordset
Dim mySQL As String
Dim strPath As String
Application.ScreenUpdating = False
strPath = “C:\Xylos\BLOG\SPEED_BLOG_EXCEL\ALotOfData.xlsb”
myConnection = “Provider=Microsoft.ACE.OLEDB.12.0;” & _
“Data Source=” & strPath & “;Extended Properties=Excel 12.0”
mySQL = “SELECT * FROM [Sheet1$A1: CV5000]”
Set RS = New ADODB.Recordset
RS.Open mySQL, myConnection, adOpenUnspecified, adLockUnspecified
Range(“A1”).CopyFromRecordset RS
RS.Close
Set RS = Nothing
Application.ScreenUpdating = True
End Sub
There are several obstacles you could run into when writing this code. The Excel version number (12.0 in this example) needs to be correct and the SQL command can’t contain any errors or typos. The connection could also contain a long list of parameters, which makes your code prone to error messages. It’s better to start without these parameters and gradually add them as you’re testing.
Alternatively, you could read an entire worksheet instead of a fixed area:
‘mySQL = “SELECT * FROM [Sheet1$]”
The CopyFromRecordset method pastes all content in the recordset into the indicated cell.
In the next example, we’ll carry out the same action via the standard method for opening a file via workbooks.open. Executing this code takes about as long as the recordset method. But if you repeat it, it only takes 3 seconds to execute. This is because the graphic layout is still in your computer’s memory. If you were to close Excel and execute the code again, the first round would take 12 seconds again.
You could conclude that both options take an equal amount of time. Still, the recordset method is just a little faster. Both methods open the file, but the Excel command ‘workbooks.open’ also has to handle the graphic side. The more files you open with the recordset method, the more time you’ll save.
Sub OpenFileCopyToDestination()
Dim wbData As Workbook
Dim wbMain As Workbook
Debug.Print Now
Set wbMain = ActiveWorkbook
Application.ScreenUpdating = False
Set wbData = Workbooks.Open(“C:\Xylos\BLOG\SPEED_BLOG_EXCEL\ALotOfData2.xlsb”)
wbData.Sheets(1).Cells(1, 1).CurrentRegion.Copy wbMain.Sheets(“Sheet1”).Cells(1, 1)
wbData.Close False
Application.ScreenUpdating = True
Debug.Print Now
End Sub
In our next blog post, we’ll use the same technique to open several files and transfer data to a target file.
Next blog post: How to use a recordset to read data from multiple files without opening them (6/7).
Are you following this blog series and do you feel like you need to brush up on Excel? Be sure to register for one of our open Excel courses.
