Excel VBA: Import data from a closed workbook.

What is the fastest and most stable VBA code that will import a range of data from another workbook without opening it.

Copy Ranges B2:B2000 from a closed workbook located at: C:\Files\Samples\Test.xlsx and past it to Cell C200 in Sheet1 of the current workbook.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
you got to open the workbook in order to read its content but you can make it invisible while you copy its content.

does this approach make sense to you?
* Assign the range to an array and the set the value of the target range = array.
* Use ADODB to populate a recordset variable and the use the CopyFromRecordset method of a range in the target worksheet.

* If you step out of the VBA environment, you might use OpenSDK and the .Net framework to directly read the xlsx data as a file.  No need to start another Excel instance or open another workbook.

* Use a third-party utility to read the xlsx data directly.  Similar to the above suggestion but your code remains in the VBA environment.

* You might want to play with the QueryTable feature.  After the import, you can break the link, keeping the imported data.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ShumsExcel & VBA ExpertCommented:

Give this a try:
Sub CopyFromClosedWB()
Dim MyPath As String, MyFile As String, FileName As String
Dim SourceWB As Workbook, TargetWB As Workbook
Dim SourceWS As Worksheet, TargetWS As Worksheet

'Disable Events
With Application
    .ScreenUpdating = False
    .DisplayStatusBar = True
    .StatusBar = "!!! Please Be Patient...Updating Records !!!"
    .EnableEvents = False
    .Calculation = xlManual
End With

'Set Variables
MyPath = "C:\Files\Samples\"
MyFile = MyPath & "Test.xlsx"
FileName = Dir(MyFile)
Set SourceWB = Application.Workbooks.Open(MyPath & FileName)
Set SourceWS = SourceWB.Worksheets("Sheet1") 'Change your sheet name here
Set TargetWB = Application.ThisWorkbook
Set TargetWS = TargetWB.Worksheets("Sheet1") 'Change your sheet name here

'Copy from Source to Target WB
TargetWS.Range("C200").PasteSpecial xlPasteAll
Application.CutCopyMode = False

'Close Source Workbook
Application.DisplayAlerts = False
SourceWB.Close SaveChanges:=False
Application.DisplayAlerts = True
FileName = Dir

'Enable Events
With Application
    .ScreenUpdating = True
    .DisplayStatusBar = True
    .StatusBar = False
    .EnableEvents = True
    .Calculation = xlAutomatic
End With

End Sub

Open in new window

ouestqueAuthor Commented:
Thanks Guys!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.