Macro to get Data from Another Workbook in Excel
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.
Macro: Excel macro that will remove/delete all of the comments from an entire Excel workbook....
Macro: Free Excel UDF (user defined function) that displays the full file path and name of an Exc...
Tutorial: How to make a user input form in Excel and have the data stored on another worksheet at th...
Tutorial: In Excel, you can quickly copy an entire worksheet from one workbook to another workbook. ...
Tutorial: In this tutorial I am going to show you how to update, change and manage the data used by ...
Tutorial: How to prompt a user for their input in Excel. There is a simple way to do this using VBA ...
