Sometimes you'll want to access another spreadsheet from an existing Excel file.
If you're adding data from another file on a regular basis this process might be an ideal candidate for automation with VBA.
An example might be a weekly report from your sales staff and you want to add the new data to an existing file called total sales.
Here's what you might be doing manually once a week:

• Open the total sales file
• Find and open the latest weekly report
• Copy and add the data into the total sales spreadsheet

VBA can be used to automate the entire process, but you do need to consider various issues that might be unique to your own situation.

1. Is the weekly report file in the same folder as the total sales file?
2. Will the weekly report have the same name every time you import it?
3. Are the fields in the main file consistent with the new data?
4. Where will you put the new data in the main file?

Opening Another File And Importing The Contents It's fairly straight forward to open another file in VBA, but you do need to specify the location; you can access this through the activeWorkbook.
path function which returns the folder address of the current workbook.

' open the file weeklyFile = ActiveWorkbook.
Path & "\20aug2012Sales.
xls" Workbooks.
Open Filename:=weeklyFile Range("A1").
CurrentRegion.
Select ' copy and paste the contents Application.
DisplayAlerts = False Selection.
Copy ActiveWindow.
Close Range("A1").
End(xlDown).
Offset(1, 0).
Select ActiveSheet.
Paste Although this is a fairly simple code snippet, you might need to adjust it slightly for your own situation: 1.
Application.
DisplayAlerts=false This line not really needed; it just asks the user if the application should keep the copied data on the clipboard before closing the weekly file and the default response is yes.
Alternatively we could have kept the weekly file open until after pasting the data.
2.
Location of the weekly file If the weekly sales data is in a different folder to the main file the location needs to be specified directly.
The logical idea would be to keep the new files in a separate folder:

totalsales.
xsl weeklysales/20Aug2012Sales.
xls

2.
The name of the weekly sales files As you might get a new sales file each week the name could be constantly changing but if the file is named in a consistent format you can use an input box to ask for the file name:
weeklySales = InputBox("Sales File for Week ending:") weeklySales=weeklySales & "Sales.
xls" In this case we've just asked the user for the date, instead of typing in the full file name; if you know the naming convention will always be the same, this can be elegant solution.
Summary You've seen how just 8 lines of code will open an Excel spreadsheet and import the data into an existing spreadsheet.
With a little thought applied to your own situation this is another example of how VBA can make your own work with Excel much more efficient and productive.