CODEWITHSUNDEEP
excelvba
chan hong chon
chan hong chon

Reputation: 55

Loop to open all excel file path in a column

I have split the directory in column A and filename in Column B. Trying to loop a macro to open all the files in the columns.

Using the codes below, it opens only the first file. not sure where I had gone wrong. the codes are as below: 

Sub openfiles()
Dim directory As String, fileName As String, sheet As Worksheet, i As    Integer, j As Integer
Dim wb As Workbook
Dim path As Worksheet
Dim row As String
Set wb = ThisWorkbook
Set path = wb.Sheets("sheet1")
row = 2

Application.ScreenUpdating = False
Application.DisplayAlerts = False

directory = path.Range("A" & CStr(row))
fileName = Dir(directory & path.Range("B" & CStr(row)))
Do While fileName <> ""
Workbooks.Open (directory & Dir(directory & path.Range("B" & CStr(row))))
row = row + 1
fileName = Dir()
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

After running, I would except all the files to be open so that I can proceed with other macros.

update: screenshot

here's the screenshot as requested, I can do with the directory and filename in one column. enter image description here

Upvotes: 0

Views: 878

Answers (1)

Mikku
Mikku

Reputation: 6654

Try this:

I prefer using this simple Method in case I already have all the file list in Excel.

Option Explicit
Sub openfiles()

    Dim i As Long
    Dim wb As Workbook
    Dim path As Worksheet
    Set wb = ThisWorkbook
    Set path = wb.Sheets("sheet1")


    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    For i = 2 To path.Range("A2").End(xlDown).row

        If Len(Dir(path.Range("A" & i) & path.Range("B" & i))) > 0 Then
              Workbooks.Open (path.Range("A" & i) & path.Range("B" & i))
        End If
    Next
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

End Sub

Moving on to your code:

I usually resort to this method when I have to open all the files in a Particular folder or I don't have the Name of the File. Because this way you can pass on a Wildcard. So if you want to open all the Workbooks of folder O:\Common\ use this:

Sub openfiles()
Dim directory As String, fileName As String, sheet As Worksheet, i As Integer, j As Integer
Dim wb As Workbook
Dim path As Worksheet
Dim row As String
Set wb = ThisWorkbook
Set path = wb.Sheets("sheet1")
row = 2

Application.ScreenUpdating = False
Application.DisplayAlerts = False

directory = path.Range("A" & CStr(row))
fileName = Dir(directory & "*.*")
Do While fileName <> ""
Workbooks.Open (directory & fileName)
row = row + 1
fileName = Dir()
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

Problem: the problem with your code was, you were passing a Specific File Name to the variable filename, that's why you were only getting one file.

You still want to use your code, Here it is without DIR, unused variables and a bit less complicated.

Sub openfiles()

Dim directory As String
Dim fileName As String
Dim row As String

Set path = ThisWorkbook.Sheets("sheet1")
row = 2

Application.ScreenUpdating = False
Application.DisplayAlerts = False

directory = path.Range("A" & CStr(row))
fileName = path.Range("B" & CStr(row))

    Do While fileName <> vbNullString

        Workbooks.Open (directory & "\" & fileName)

        row = row + 1
        directory = path.Range("A" & CStr(row))
        fileName = path.Range("B" & CStr(row))

    Loop

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

Upvotes: 1

Related Questions