Using Current Directory to retrieve data without opening Excel Workbook (Smarter Selective Imports w/VBA)

Question

(Excel 2010) I'm trying to grab specific lines from a variety of "target" workbooks in different but similar folders. I have found that I am able to pull this data when the source ("LM", the workbook the code is executing in, and that I want to pull the data to) and target workbooks are in the same folder without opening the target's workbook, but when they are in different locations (as they will be in practice) I receive a "Subscript out of range" error for the

LM.Worksheets("Sheet1").Range("B" & i + 1 & ":G" & i + 1).Value = _
          Workbooks(filename).Worksheets("Sheet1").Range("B6:G6").Value

line. I have tried:

Using every variant & combination on pathname, dirname & filename, etc. as the argument for the latter Workbooks(). I have also had it MsgBox me the pieces and whole of pathname and filename to look at, which are error-free.
Replacing the latter Workbooks(filename) with a workbook variable (lets call it Targ), like LM (which works fine)
Changing the path with ChDir and ChDrive (& I have confirmed that the CurDir() is in fact the target directory when this is running) and doing the above
Using ThisWorkbook instead of LM for the call
Basically every permutation of the above ideas

Here is a stripped-down (because confidential stuff was in there) version of the code (which works fine if I un-comment Workbooks.Open and Workbooks.Close, but I want a more efficient method since this is a busy network and people are in-and-out of these files all the time. The fact that I can do this without opening the files if they're in the same folder tells me I'm onto something...)

Sub Import()
    Dim directory As String, fileName As String, LM As Workbook, i as Integer
    Set LM = Workbooks("LM.xlsm")

    i = 1

    Dim DirArray As Variant

    'this is the array that handles the variations on the path, doesn't seem to be the problem
    DirArray = LM.Worksheets("Sheet2").Range("DirTable")

    Do While i <= UBound(DirArray)

       directory = DirArray(i, 1)

       dirname = "C:\blahblahblah"
       fileName = Dir(dirname & "*.xl??")
       pathname = dirname & fileName

       ChDir dirname
       ' Workbooks.Open (dirname & fileName)

       LM.Worksheets("Sheet1").Range("B" & i + 1 & ":G" & i + 1).Value = _
             Workbooks(filename).Worksheets("Sheet1").Range("B6:G6").Value

        i = i + 1

    '  Workbooks(fileName).Close

    Loop
End Sub

If I could just figure out what is different when they're in the same folder! Navigating with ChDir and ChDrive doesn't seem to do any good...

In what way? By assigning an intermediate variable? Not sure how that helps... — basaltanglia
Inter = Workbooks(pathname).Worksheets("Metrics").Range("B6:G6").Value LM.Worksheets("Sheet2").Range("B" & i + 1 & ":G" & i + 1).Value = Inter Debugger flags the first line, same error message — basaltanglia
Workbooks(filename) when you do that, you need entire path I think. — findwindow
I've tried Workbooks(pathname), Workbooks(dirname & filename), Workbooks(dirname & "\" & filename) And other variants. And when I have msgbox or the debugger return the variables, they all look like I would expect... — basaltanglia
That syntax only works on open workbooks, regardless of whether they're in the same folder as the macro file or not... — Tim Williams

Tim Williams Tim Williams · Accepted Answer · 2016-05-27T19:37:20

It's unclear exactly what you want to do, but this should be a working version of your posted code.

Is there only one Excel file per folder? Did you want to use directory in place of the hard-coded DIRNAME ?

Sub Import()

    Const DIRNAME As String = "C:\blahblahblah\"
    Dim directory As String, fileName As String, LM As Workbook, i As Integer
    Dim DirArray As Variant, wb As Workbook

    Set LM = Workbooks("LM.xlsm") 'ThisWorkbook ?
    DirArray = LM.Worksheets("Sheet2").Range("DirTable").Value

    For i = 1 To UBound(DirArray, 1)

        directory = DirArray(i, 1) 'what are these values ?

        fileName = Dir(DIRNAME & "*.xl??")

        If fileName <> "" Then

            'ChDir dirname '<< you do not need this if you pass the full path to Open...
            Set wb = Workbooks.Open(filename:=DIRNAME & fileName, _
                                    ReadOnly:=True, UpdateLinks:=0)

            LM.Worksheets("Sheet1").Range("B" & (i + 1) & ":G" & (i + 1)).Value = _
                       wb.Worksheets("Sheet1").Range("B6:G6").Value

            wb.Close False 'no save

        End If
     Next
End Sub

Using Current Directory to retrieve data without opening Excel Workbook (Smarter Selective Imports w/VBA)

1 Answers