Variable Directory in Loop

Question

I'm trying to make a macro that allows the user to select a file (excel file) which then is used to copy down information to the active workbook from that selected file. I don't know how to include the file's variable directory into the code. Anyone got an idea?

Sub Ref()

    Dim Path As String
    Path = Application.GetOpenFilename

    Dim r As Integer
    r = 1

        For r = 1 To 1000
            If Not IsEmpty(Range(Path(Cells(r, 1)))) Then
            Cells(r, 1) = Range(Path(Cells(r, 1)))
            End If
        Next
End Sub

This question was poorly worded, please see the following post for a more adequately worded question with the proper solution. (It turned out to be extremely simple). http://stackoverflow.com/questions/30851007/vba-directory-referencing/30851530#30851530 — Markus, Jul 07 '15 at 16:02

score 0 · Accepted Answer · answered Jun 03 '15 at 17:32

It seems that you have a list of Excel workbooks together with their path(s) in column A. You are going to have to open the workbook if you want to retrieve information from the cell(s) in that workbook.

This generic framework should help you get started on a sub that loops through the values in the active workbook's active worksheet's column A, opens each file listed and transfers the value from A1 to the original workbook's column B.

Sub ref()
    Dim wb0 As Workbook, wb1 As Workbook
    Dim r As Long, lr As Long

    Set wb0 = ActiveWorkbook

    With wb0.Sheets("Sheet1")
        lr = .Cells(Rows.Count, 1).End(xlUp).Row
        For r = 1 To lr
            If Not IsEmpty(.Cells(r, 1)) Then
                Set wb1 = Workbooks.Open(.Cells(r, 1).Value2)
                .Cells(r, 2) = wb1.Sheets("Sheet1").Cells(1, 1).Value
                wb1.Close False
            End If
        Next r
    End With

End Sub

You will have to expand on that for your own individual situation but I believe you should see the process as you loop through the workbooks listed in column A.

What I was going for was there to be only ONE directory that is chosen at startup and for there to be a simple: _Copy cells from the other workbook if not empy_ — Markus, Jun 04 '15 at 13:49
The following link shows what I was actually intending to accomplish. Sorry for the confusing post. [link](http://stackoverflow.com/questions/30851007/vba-directory-referencing) — Markus, Jul 07 '15 at 16:15

Variable Directory in Loop

1 Answers1