Get Data from Closed Workbooks

[JamieArvato]

Hi, I'm looking at pulling in data from several workbooks into one.

Some of these workbooks will be open by another user some wont.

I was originally looking to set-up code to create 15 ODBC links each time I ran it but thought that there must be an easier way to copy and paste without opening the files?!

I have the following code off an internet site but it just brings back #REF's

Sub test()
GetValuesFromAClosedWorkbook "X:\", "File1.xls", _
"DataRange", "A1:K1000"
End Sub

Sub GetValuesFromAClosedWorkbook(fPath As String, _
fName As String, sName, cellRange As String)

With Sheets("Sheet5").Range(cellRange)


.FormulaArray = "='" & fPath & "\[" & fName & "]" _
& sName & "'!" & cellRange
.Value = .Value

End With
End Sub


thanks

 

[byundt]

I've used John Walkenbach's code to retrieve data from closed workbooks many times--but only for a single cell. Walkenbach shows code to retrieve an entire array worth of information a single cell at a time.

http://j-walk.com/ss/excel/tips/tip82.htm

 

[JamieArvato]

The array code above will not work as it is going forwards and backwards from the file for every cell, I was looking at bringing in 44k rows by 12 cols = 528k cells.

So I've wrote code which checks the file exists, checks its not open, opens the files read-only, copies what it needs to moves back and pastes it.

Along with a load of formatting code this is opening files, copying 2.5k rows, and closing them in 4-6 seconds, so with the c.15 files I'm using an update takes 1 1/2 minutes which is fine.

Even when I tried ODBC links to the c.15 files it would take about 5 mins and would open any files that someone was in!

Thanks for your help.