Copy large data from closed workbook to a specific worksheet in destination workbook

Question

I am using this code to copy all the data from a sheet in the closed workbook to destination workbook. However this code loops through each cell and since the source workbook contains 40,000 rows, its taking too long. What changes can be made to this code to copy all the data from source worksheet to a specific worksheet in the destination workbook without looping through cells or if there is an alternative solution that you can provide.Thanks for your help

Sub GetDataDemo() 

Dim FilePath$, Row&, Column&, Address$ 

Const FileName$ = "Book1.xls" 
Const SheetName$ = "Sheet1" 
Const NumRows& = 40000 
Const NumColumns& = 10 
FilePath = ActiveWorkbook.Path & "\" 
 '***************************************
     DoEvents 
Application.ScreenUpdating = False 
If Dir(FilePath & FileName) = Empty Then 
    MsgBox "The file " & FileName & " was not found", , "File Doesn't Exist" 
    Exit Sub 
End If 
For Row = 1 To NumRows 
    For Column = 1 To NumColumns 
        Address = Cells(Row, Column).Address 
        dest. Cells(Row, Column) = GetData(FilePath, FileName, SheetName, Address) 
        Columns.AutoFit 
    Next Column 
Next Row 
ActiveWindow.DisplayZeros = False 
End Sub 


Private Function GetData(Path, File, Sheet, Address) 
    Dim Data$ 
    Data = "'" & Path & "[" & File & "]" & Sheet & "'!" & _ 
    Range(Address).Range("A1").Address(, , xlR1C1) 
    GetData = ExecuteExcel4Macro(Data) 
End Function

Maybe THIS post is what you need? I don't have a way to compare the execution time though. This post pulls data from a closed worksheet in Range without using ExecuteExcel4Macro. — L42
Have you tried opening the closed workbook? It could be much faster to open the closed workbook and copy the entire range without looping — Charles Williams
@Charles.. I can't use open workbook because I don;t want the end user to see the workbook from which the information is imported — vik
Can you guys tell me if there is a way to import a pivot table without opening the source workbbok. The range formula gets the data in table form and I am trying to import a pivot table. I can do it with a workbook.open formula but I need to know if it can be done from a closed workbook. Thanks — vik

user3040632 user3040632 · Accepted Answer · 2013-12-13T12:18:22

I have founs dome good / similar sollution for you on link :

http://social.msdn.microsoft.com/Forums/office/en-US/aa6edf0f-2007-4f25-885c-f03d3df4d853/pulling-values-from-closed-workbook-into-current-workbook-but-on-a-different-sheet

In your case replace :

For Row = 1 To NumRows 
For Column = 1 To NumColumns 
Address = Cells(Row, Column).Address 
dest. Cells(Row, Column) = GetData(FilePath, FileName, SheetName, Address) 
Columns.AutoFit 
Next Column 
Next Row

With :

For Row = 1 To NumRows
For Column = 1 To NumColumns
Address = Cells(Row, Column).Address
Cells(Row, Column + X) = GetDataEleven(FilePath, FileName, SheetName, Address)
Next Column
Next Row
Columns.AutoFit

Copy large data from closed workbook to a specific worksheet in destination workbook

1 Answers