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.
Question? Ask it in our Excel Forum
- Skill Level: All Levels
- |
- 5 hours+
How to make a fully featured professional form in Excel that is unbreakable. This includes how to use the form to store, view, edit, and delete data from a data storage worksheet.
- Skill Level: All Levels
- |
- 2 hours
Send Emails from Excel using VBA and Macros. This course starts from the Basics and builds up to more advanced examples with attaching workbooks, worksheets, PDF's, automatically sending emails, including a signature, error handling, increasing speed, and more.
Subscribe for Weekly Tutorials
BONUS: subscribe now to download our Top Tutorials Ebook!
Tutorial: How to get data from separate workbooks in Excel. This tutorial includes an example using ...
Tutorial: How to get data from a UserForm in Excel, including from: text inputs (TextBox), list boxe...
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 this tutorial I am going to show you how to update, change and manage the data used by ...
