Programming: How To Use VBA To Import Data From Another Excel File

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
fileFind and open the latest weekly reportCopy 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.
Is the weekly report file in the same folder as the total sales
file?Will the weekly report have the same name every time you import it?Are the fields in the main file consistent with the new data?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.

agel vietnam lua dao