Macro to get Data from Another Workbook in Excel

Add to Favorites
Author:

Macro to get data from a workbook, closed or open, over a network or locally on your computer.

This is a versatile and self-contained macro that opens another workbook, gets information from it, and then closes that other workbook. This happens so fast that you won't even notice what is happening; it should feel seamless.

(This technique will also allow you to change data or input data into another Excel workbook using VBA and macros.)

Macro to Get Data from Another Workbook

Sub get_data()

 

Dim Wb1 As Workbook

 

'Dont update the screen - makes it work faster

Application.ScreenUpdating = False

 

'Open the other workbook

'Input the FULL path to the file, including its extension

Set Wb1 = Workbooks.Open("C:\sample.xls")

 

'You can do whatever you want here with the other workbook - it is now open.

'This just copies some cells.

Wb1.Sheets(1).Range("A1:A10").Copy

 

'Close the workbook from which we just got some data and make sure not to save it in case accidental changes were made to it.

Wb1.Close SaveChanges:=False

 

'Turn on screen updating again - makes Excel usable

Application.ScreenUpdating = True

 

End Sub

How to use the Macro

C:\sample.xls is the path to the Excel file that you want to open. This must be the full path. It can be for a file that is located on your computer or over a network. Also, don't forget the file extension.

Wb1 is the object that references the workbook that the macro opens. That means that whenever you want to reference this workbook in the code, you write Wb1 and then select the sheet and do something.

For example, to select cell A1 on Sheet1, you would do this: Wb1.Sheets("Sheet1").Range("A1").Select

Make sure that you put all of the code that gets data from the workbook after the line that opens the workbook: Set Wb1 = Workbooks.Open("C:\sample.xls"), and before the line of code that closes the workbook: Wb1.Close SaveChanges:=False.

If you want to save any changes that were made in the workbook that you opened, change the False to True in this line: Wb1.Close SaveChanges:=False.

Notes

This macro allows you to get data from another workbook, or put data into it, or do anything with that workbook.

The code is a template that allows you to simply access another Excel file.                                           

Download the file attached to this tutorial to get the macro in Excel.


Downloadable Files: Excel File

Question? Ask it in our Excel Forum


Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Similar Content on TeachExcel
Get Data from Separate Workbooks in Excel
Tutorial: How to get data from separate workbooks in Excel. This tutorial includes an example using ...
Getting Data from a UserForm
Tutorial: How to get data from a UserForm in Excel, including from: text inputs (TextBox), list boxe...
Remove Comments from a Workbook in Excel - All Comments Deleted - Macro VBA
Macro: Excel macro that will remove/delete all of the comments from an entire Excel workbook....
Output the File Path to and Name of a Workbook in Excel - UDF
Macro: Free Excel UDF (user defined function) that displays the full file path and name of an Exc...
Input Form to Get Data and Store it in Another Tab in Excel
Tutorial: How to make a user input form in Excel and have the data stored on another worksheet at th...
Update, Change, and Manage the Data Used in a Chart in Excel
Tutorial: In this tutorial I am going to show you how to update, change and manage the data used by ...
Tutorial Details
Downloadable Files: Excel File
Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course