Open a Single File Import Data with VBA

The following Excel VBA example will concentrate on the import of data from a single workbook with VBA.  It is quite a common problem when dealing with data split between a couple or multiple Excel workbooks.  The question to answer is how do you import data from another Excel file efficiently with VBA.

The following VBA procedure opens a single file called England.xlsm and copies the data from A1:F100.  It then pastes the data into cell A1 of sheet1.  The opened Excel file England.xlsm is then closed with VBA.

Option Explicit
OpenImp() 'Excel VBA to Open a single file and import data.
Dim owb As Workbook
Dim sh As Worksheet

Set sh=Sheet1
'Open a file called England (Change to suit).
Set owb=Workbooks.Open("C:\Test\England.xlsm")

sh.Range("A1").PasteSpecial xlPasteValues
owb.Close False 'Close opened workbook don't save

End Sub

The workbook just opened will close and will not be saved. There will now be fresh data on sheet1 of your open workbook.