How to Import data from Excel to Excel?
Use one of the appropriate methods explained to access data from an external Excel file.
- Using VBA import using workbook object (closed Excel file).
- Using VBA read Excel file (opened workbook).
- Using External reference within Worksheets.
- Using ODBC data import option.
Note: If we are trying to read a Plain Text file or binary file, we can use VBA File Handling commands as in this link. How to Read contents from a Text File using VBA?
Excel to pull data from another Workbook using VBA
To understand how to extract data from another Excel file, consider there are 2 workbooks as listed.
- Source: In this workbook, VBA code will be executed to fetch or write data from/to Target file.
- Target: This workbook has the data that the Source Workbook will read or modify through VBA.
1. VBA To Update Closed Workbook
This is done with the help of VBA Macro. We will create an object to refer to external Excel file and use that object to import any data into our Active workbook or Source Workbook.
Let’s see the actual VBA code for this purpose. Copy paste the below code to VB Editor and execute the code by pressing F5. Make sure that the Target file exists in correct path as mentioned in Target_Path in the code, before executing the code.
12345678910111213141516171819202122232425262728293031Sub VBA_Read_External_Workbook()'''''Define Object for Target WorkbookDim Target_Workbook As WorkbookDim Source_Workbook As WorkbookDim Target_Path As String'''''Assign the Workbook File Name along with its Path'''''Change path of the Target File nameTarget_Path = "D:\Sample.xlsx"Set Target_Workbook = Workbooks.Open(Target_Path)Set Source_Workbook = ThisWorkbook'''''With Target_Workbook object now, it is possible to pull any data from it'''''Read Data from Target FileTarget_Data = Target_Workbook.Sheets(1).Cells(1, 1)Source_Workbook.Sheets(1).Cells(1, 1) = Target_Data'''''Update Target FileSource_data = Source_Workbook.Sheets(1).Cells(3, 1)Target_Workbook.Sheets(1).Cells(2, 1) = Source_data'''''Close Target WorkbookSource_Workbook.SaveTarget_Workbook.SaveTarget_Workbook.Close False'''''Process CompletedMsgBox "Task Completed"End Sub
2. VBA Read Excel file or Write To Open Workbook?
If a workbook is already in opened and executing, then you can reference that Excel with its name through ‘Workbooks’ collection. You have to use the workbook name itself inside the code to read or write content as mentioned in this sample code.1234567891011121314Sub Write_To_Open_Excel()Dim wb As Workbook'Reference Workbook with its nameWorkbooks("Book2").Worksheets("Sheet2").ActivateWorkbooks("Book3.xls").Worksheets("Sheet2").Activate'Search for Each Opened WorkbookFor Each wb In WorkbooksIf wb.Name = "Book2" Thenwb.Sheets(1).Cells(1, 1) = "Writing To Open Excel Worksheet - Testing"End IfNextEnd Sub
3. External Reference to Import Data from another Workbook
With this technique, in the Excel we pull data from another cell by using references. For example, in Cell A1 if we need to get date from Cell B1, we enter “=B1” in cell A1. This is a reference that is made within the scope of current workbook.
In our example if we need to refer the Target sheet, use the command as below.
This will fetch the data from the external workbook.
Reference from Microsoft: How to create External reference and pull data from another excel?
4. Data Import Option or ODBC
This is similar to Data Import facility available in Excel. To do this, the Target workbook should be having some table defined in it.
To import the data from Target, Go to Source Workbook, Data->From Other Sources ->From Microsoft Query and Give the path of the Target Workbook or use the below code by modifying the File path and Column Header Details.12345678910111213141516171819202122232425Sub Data_Import_Recorded_Macro()'''''Change File path and Column Headers to Use this codeWith ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _"ODBC;DSN=Excel Files;DBQ=D:\Sample.xlsx;DefaultDir=D:;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;" _, Destination:=Range("$A$1")).QueryTable.CommandText = Array( _"SELECT `Sheet1$`.Column1, `Sheet1$`.Column2, `Sheet1$`.Column3" & Chr(13) & "" & Chr(10) & "FROM `D:\Sample.xlsx`.`Sheet1$` `Sheet1$`" _).RowNumbers = False.FillAdjacentFormulas = False.PreserveFormatting = True.RefreshOnFileOpen = False.BackgroundQuery = True.RefreshStyle = xlInsertDeleteCells.SavePassword = False.SaveData = True.AdjustColumnWidth = True.RefreshPeriod = 0.PreserveColumnInfo = True.ListObject.DisplayName = "Table_Query_from_Excel_Files".Refresh BackgroundQuery:=FalseEnd WithEnd Sub
These are some of the methods that are available to import from an external workbook to current active workbook. But still, this is not the limit. There might be other feasible methods known to others. If there is any, please post a reference as you find.
Previous Post: VBA Merge Text Files – Combine Data Files Into One with Excel
Next Post: Lock System at Scheduled Time with Excel
Excel VBA to Import Data – From Another Workbook – Query Macro