Extract data from a closed workbook in VBA

Question

Error Received: Run-Time error '1004'. Application-defined or object-defined error. I've searched the forum for similar topics but could never get a good answer. I am trying to extract data from a closed workbook. Here is sample of my code. I need the last row from the source data, but there are many workbooks and each one will have a different last row.

Option Explicit

Sub CopyFromClosedWorkbook2()
' Defines variables
Dim wb1 As Workbook, wb2 As Workbook 
Dim fname As String, fpath As String, shtname As String
Dim tgtlr As Long
Dim current_lcn As Long
Dim current_row As Long
Dim i As Long: i = 2
' Disable screen updating to reduce screen flicker
Application.ScreenUpdating = False

' Define which workbook is which
current_lcn = Cells(2, 3).Value
Set wb1 = Workbooks("RBOM test.xlsm")
tgtlr = Cells(Rows.Count, 3).End(xlUp).Row
fpath = "C:\users\folder1\My Documents\folder2\folder3\"
fname = "filename.xlsx": shtname = "filename"
With ThisWorkbook.Sheets("Sheet1").Range("AQ2:FO2")
  .Formula = "=INDEX('" & fpath & "[" & fname & "]" & shtname & "'!A:A,  COUNTA('" & fpath & "[" & fname & "]" & shtname & "'!$A:$A)-1)"
  .Value = .Value
End With

' Re-enable screen updating
Application.ScreenUpdating = True
End Sub

the filename is the same as the shtname

I eventually want to get it so where it'll loop through the rest of the files. But since I've haven't found a way to do this without opening the workbook. I've read that someone developed a code where you didn't have to open the workbook, but you needed the range pre determined, whereas mine is different depending on the last row. The last part of the code was my attempt at the beginning of incorporating the code into a for loop and pasting the data down with the same criteria.

Possible duplicate of Pulling data from a closed workbook macro — M--
I tried changing by using the above link as an example. So after .copy I have: destination:=wb1.sheets("Sheet1").range(Cells(2,43), Cells(2,171)), but I still get the error: — Jeff
include your updates in your question by editing and avoid from putting them into comments. — M--
You can link to the question I provide and say that in reference to that you applied following changes in your code and you get the same error or different error on the specific part of your code — M--
It's simple. You can't! You have to open existing workbook before you start copying data. — Maciej Los

A.S.H A.S.H · Accepted Answer · 2017-05-23T21:20:54

So you need to replace this part

Set wb2 = Workbooks.Open(...)
....
wb2.Close

With some formula that references the closed WB and catches automatically its last row. For this you need to have a column in the source sheet that is always populated. Suppose this column is A. You can try to replace the code above with this:

Dim fName As String, fPath As String, shtName As String
fPath = "C:\Users\folder1\My Documents\folder2\folder3\"
fName = "filename.xlsx": shtName = "mySheet"

With ThisWorkbook.Sheets("Sheet1").Range("AQ2:FO2")
  .Formula = "=INDEX('" & fPath & "[" & fName & "]" & shtName & _
    "'!A:A, COUNTA('" & fPath & "[" & fName & "]" & shtName & "'!$A:$A)-1)"
  .Value = .Value
End With

This will fetch the last row from the closed WB through a formula, then fix the value. Now you can loop on many workbooks using Dir (for example), by changing each time the variable fName.

p.s.: setting a formula and making it working without opening the WB works very well with Excel files (i.e .xlsx) but less well with .csv files, where the formula needs that the WB opens to refresh.

Extract data from a closed workbook in VBA

1 Answers