This is the third post in a series of 7 blog posts explaining how to speed up data transformation in Excel VBA with a few simple techniques.
- 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
This blog post teaches you how to open a file and transfer data to a target file. It’s an introduction to the next posts in this series.
You’ll need a data file with a few columns, like the example below. You can choose the number of rows as well.
To do this smoothly, we’ll use object variables to refer to the workbooks and worksheets we’ll need during the transfer.
The two main object types we’ll use are ‘WorkBook’ and ‘WorkSheet’.
We’ll need two of each: one for the main or target file and one for the source file containing the data.
We’ll set up the variables as follows:
Dim wbMaster As Workbook
Dim wsMaster As Worksheet
Dim wbData As Workbook
Dim wsData As Worksheet
The first thing we’ll do is link our initial file.
Set wbMaster = ActiveWorkbook
Set wsMaster = wbMaster.Worksheets(“Database”)
Next, we’ll open the file containing the data and assign it to the variables.
Set wbData = Workbooks.Open(“C:\Users\Pibo\Data.xlsx”)
Set wsData = wbData.worksheets(1)
Let’s assume that you need to copy the entire data range in the source file to the target file. By using the objects we’ve defined, we won’t have to think about which workbook or worksheet is active.
The ‘copy’ method includes a ‘destination’ argument, so you won’t need to select anything before pasting the data.
wsData.Cells(1, 1).CurrentRegion.Copy wsMaster.Cells(1, 1)
All data from the source file are now in the target file, so all that’s left to do now is close the source file. Don’t forget to clear the variables you’ve used.
wbdata.Close False
Set the ‘SaveChanges’ parameter to False – after all, you haven’t changed the source file.
Clear the variables when you’re done. You’ll no longer need the reference to the file. If you want to make sure the space you used doesn’t remain in the code, do the following:
Set wbData = nothing
This is what the complete code should look like:
Dim wbMaster As Workbook
Dim wsMaster As Worksheet
Dim wbData As Workbook
Dim wsData As Worksheet
Set wbMaster = ActiveWorkbook
Set wsMaster = wbMaster.Worksheets(“Database”)
Set wbData = Workbooks.Open(“C:\Users\MyName\Data.xlsx”)
Set wsData = wbData.worksheets(1)
wsData.Cells(1, 1).CurrentRegion.Copy wsMaster.Cells(1, 1)
wbdata.Close False
Set wbData = nothing
This method is the easiest way to open a file and grab data from it.
Note: The downside of this method is that executing the code takes as much time as opening the file manually. The larger the file, the longer it takes to execute the code.
Note: Try to close the data file as quickly as possible. As soon as the data are in your target file, you’ll no longer need the source file, so it’s good manners to not keep it locked for other users any longer than needed.
In the next blog, we’ll use the same method to open several files and add their data to a target file. Later on in this series, we’ll take data from files without opening them.
Up next: How to open several files and transfer data to a target file (4/7).
Are you following this blog series and do you feel like you need to brush up on the subject? Be sure to register for our ‘Excel: VBA Programming’ course.
