Macro to get Data from Another Workbook in Excel

Add to Favorites

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

Similar Content on TeachExcel
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...
Import a Worksheet from One Workbook to Another in Excel
Tutorial: In Excel, you can quickly copy an entire worksheet from one workbook to another workbook. ...
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 ...
Get User Submitted Data from a Prompt in Excel using VBA Macros
Tutorial: How to prompt a user for their input in Excel. There is a simple way to do this using VBA ...