Chapter Fourteen: How To Use Data From Excel

There are times when you will need to manually copy the data from one Excel file to the next. You can always automate this process if necessary, and also ensure that the data is copied correctly. You can also verify if the copied data has no duplications and no figure is entered into an incorrect location. This will help you save a lot of time.

You can either write the code in the Workbook_Open() event or include a function in the ThisWorkBook object to perform this function. When you write the code in the former event, the compiler will ensure that all the figures are copied over correctly when the source file is open.

When you want to develop the code, you should open the destination Excel file, and press the shortcut Alt+F8. The ThisWorkBook module can be found under the Microsoft Excel Objects in the Project Explorer window. You should now open the window and choose the “Workbook” object from the object dropdown [46] .

Option Explicit

Private Sub Workbook_Open()

Call ReadDataFromCloseFile

End Sub

Sub ReadDataFromCloseFile()

​ On Error GoTo ErrHandler

​ Application.ScreenUpdating = False

​ Dim src As Workbook

​ ' OPEN THE SOURCE EXCEL WORKBOOK IN "READ ONLY MODE".

​ Set src = Workbooks.Open("C:\Q-SALES.xlsx", True, True)

​ ' GET THE TOTAL ROWS FROM THE SOURCE WORKBOOK.

​ Dim iTotalRows As Integer

​ iTotalRows = src.Worksheets("sheet1").Range("B1:B" & Cells(Rows.Count, "B").End(xlUp).Row).Rows.Count

​ ' COPY DATA FROM SOURCE (CLOSE WORKGROUP) TO THE DESTINATION WORKBOOK.

​ Dim iCnt As Integer     ​ ' COUNTER.

​ For iCnt = 1 To iTotalRows

Worksheets("Sheet1").Range("B" & iCnt).Formula =

src.Worksheets("Sheet1").Range("B" & iCnt).Formula

​ Next iCnt

​ ' CLOSE THE SOURCE FILE.

​ src.Close False         ​ ' FALSE - DON'T SAVE THE SOURCE FILE.

​ Set src = Nothing

​ ErrHandler:

​ Application.EnableEvents = True

​ Application.ScreenUpdating = True

End Sub

Property Application.ScreenUpdating

In the first line of the code, you will see that the Application.ScreenUpdating property is set to false. This will help you improve the speed of the macro that was written.

Open the Source File and Read Data

The next step is to open the source workbook that you are copying the information from. Remember that Excel will open the source file in the read only state, which will ensure that no changes are made to the source file.

Set src = Workbooks.Open("C:\Q-SALES.xlsx", True, True )

Once you have obtained the necessary information, the compiler will count the number of rows that are present in the source Excel workbook. This loop will run, and the data will be copied accurately from the source to the destination.

' COPY DATA FROM SOURCE (CLOSE WORKGROUP) TO THE DESTINATION FILE.

For iCnt = 1 To iTotalRows

Worksheets("Sheet1").Range("B" & iCnt).Formula =

src.Worksheets("Sheet1").Range("B" & iCnt).Formula

Next iCnt

Once the data has been copied over, you can set the property Application.ScreenUpdating to true.