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
Sub OpenImp() 'Excel VBA to Open a single file and import data.
Sub 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")
owb.Sheets("Data").Range("A1:F100").Copy
sh.Range("A1").PasteSpecial xlPasteValues
owb.Close False 'Close opened workbook don't save
End Sub
Dim sh As Worksheet
Set sh=Sheet1
'Open a file called England (Change to suit).
Set owb=Workbooks.Open("C:\Test\England.xlsm")
owb.Sheets("Data").Range("A1:F100").Copy
sh.Range("A1").PasteSpecial xlPasteValues
owb.Close False 'Close opened workbook don't save
The workbook just opened will close and will not be saved. There will now be fresh data on sheet1 of your open workbook.