• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 471
  • Last Modified:

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.
2 Solutions
Ryan ChongCommented:
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.
ShumsDistinguished Expert - 2017Commented:

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!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now