Macro to get Data from Another Workbook in Excel

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

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.