Excel Import Data From Another Workbook – VBA Codes
To pull data from an external Excel file, use on of these scenarios.
- Closed Excel file: Using VBA Import data with Workbook object
- Opened Workbook: Using VBA Read Excel file.
- External Reference within Worksheets.
- ODBC Data Import from another workbook.
Excel – Pull data from another Workbook using VBA
To understand how to get data from another Excel file, lets assume these 2 workbook names.
- Source: In this workbook, VBA code will be executed to write or import 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 Excel vba import data from another workbook without opening the file manually.
First we will create an Workbook object to refer the external Excel file. And use that object to import 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.
Sub VBA_Read_Data_Another_External_Workbook() '''''Define Object for Target Workbook Dim Target_Workbook As Workbook Dim Source_Workbook As Workbook Dim Target_Path As String '''''Assign the Workbook File Name along with its Path '''''Change path of the Target File name Target_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 File Target_Data = Target_Workbook.Sheets(1).Cells(1, 1) Source_Workbook.Sheets(1).Cells(1, 1) = Target_Data '''''Update Target File Source_data = Source_Workbook.Sheets(1).Cells(3, 1) Target_Workbook.Sheets(1).Cells(2, 1) = Source_data '''''Close Target Workbook Source_Workbook.Save Target_Workbook.Save Target_Workbook.Close False '''''Process Completed MsgBox "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.
Sub Write_To_Open_Excel() Dim wb As Workbook 'Reference Workbook with its name Workbooks("Book2").Worksheets("Sheet2").Activate Workbooks("Book3.xls").Worksheets("Sheet2").Activate 'Search for Each Opened Workbook For Each wb In Workbooks If wb.Name = "Book2" Then wb.Sheets(1).Cells(1, 1) = "Writing To Open Excel Worksheet - Testing" End If Next End 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 in Excel VBA
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.
Sub Data_Import_Recorded_Macro() '''''Change File path and Column Headers to Use this code With 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:=False End With End 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.
Note: To read a Plain Text file or binary file, use VBA File Handling commands as in this link. How to Read contents from a Text File using VBA?
There might be other feasible methods known to others. If there is any, please post a reference as you find.
10 thoughts on “Excel VBA – Import Data from another Workbook – 4 Easy Ways”
Using the first VBA option, I see my data download into my target workbook however then it disappears shortly after. Any thoughts?
Email me or paste the exact code that you executed in your system to debug the issue.
Thoughtful discussion – my colleague yesterday located
http://goo.gl/vshUfgto merge pdf . It’s kind of effortless to use and it’s convenient – I found out that they are giving a 7 day promotion currently
Thank you. This post was very helpful.
Would it be somehow possible to access a closed worksheet locked with a password?
My idea is to have a sheet for each station at a production plant, where the attendants would have access only to some positions. The manager would have a “master file” that would download all necessary informations from the stations.
I have a vague idea how to do it with a workaround, but accessing passworded files would be much better.
Nevermind, I have found the answer. It just needs another parameter (,Password:=”Password”) in Workbooks.Open.
can u please say how t export data from working sheet which is in raw data
But I thіnk уоu ѕhоuld еmрlоу tо uѕе оf grарhісѕ іn еxрlаіnіng ѕоmе of thе
ѕtерѕ tо fоllоw. Fоr іnѕtаnсе,” Tо іmроrt the dаtа frоm Tаrgеt, Gо tо Sоurсе
Wоrkbооk, Dаtа->Frоm Othеr Sоurсеѕ ->Frоm Mісrоѕоft Quеrу аnd Gіvе thе
раth оf thе Tаrgеt Wоrkbооk оr uѕе thе bеlоw соdе bу mоdіfуіng thе Fіlе раth
аnd Cоlumn Hеаdеr Dеtаіlѕ” wоuld hаvе bееn еаѕіlу undеrѕtооd іf ассоmраnіеd by
Specifically with the Data Import option, this can be a real alternative to Power BI or one of the o there high level dashboard that are available on the market today. If you’re a small business owner and have metrics for instance, this is way more affordable and with just a bit of effort you can have an excel dashboard of everything you need for your KPIs.
excellent, thank you