Vba Code to open multiple files from a specific directory

Hi Friends,

I am a new visitor to this forum and looking forward for some help!!

I am looking for a vba code which would open multiple files on a specific folder. The directory and path is listed on a sheet. The code also needs to move on to the find the next sheet if the current ones are missing from the folder.

To provide the details:

File name 1
File name 2
File name 3
File name 4
File name 5

Path is provided at - Sheets("Sheet1").Range("B3")
File names are provided from range - Sheets("Sheet1").Range("D3") to Sheets("Sheet1").Range("D8")I did try starting off with a code like below:


If Dir(Sheets("Sheet1").Range("B3").Value & Sheets("Sheet1").Range("C3").Value & "_" & Sheets("Sheet1").Range("D3").Value) = vbNullString Then

Workbooks.Open Filename:=Sheets("Sheet1").Range("B3").Value & Sheets("Sheet1").Range("C4").Value & "_" & Sheets("Sheet1").Range("D4").Value, UpdateLinks:=0


Else

Workbooks.Open Filename:=Sheets("Sheet1").Range("B3").Value & Sheets("Sheet1").Range("C3").Value & "_" & Sheets("Sheet1").Range("D3").Value, UpdateLinks:=0

Call Paste

End If


But the error popped up as filename on cell D3 & D4, both were missing. Thanks for all your help in advance.!
Regards
N

try this, obviously you can get rid of the msgboxes if you want to:
You need to set a reference to Microsoft Scripting runtime in the VBA/ tools / references

Code:

---

Sub checkfl()
Dim FSO

Set FSO = CreateObject("Scripting.FileSystemObject")

Dim objFSO As New FileSystemObject

wkbk = ActiveWorkbook.Name


Worksheets("sheet1").Range("b3") = Path
Path = Worksheets("sheet1").Range("b3")


 For i = 3 To 8
      Workbooks(wkbk).Activate
        newname = Worksheets("sheet1").Range(Cells(i, 4), Cells(i, 4))
       
        filepath = Path & newname
        If objFSO.FileExists(filepath) Then
          MsgBox (filepath & " has been found")
          Workbooks.Open Filename:=filepath
        Else
          MsgBox (filepath & " Not found")
        End If
Next i
End Sub

---

I didn't understand this at all so obviously the code doesn't do it:

Quote:

---

The code also needs to move on to the find the next sheet if the current ones are missing from the folder.

---

Quote:

---

Originally Posted by offthelip

try this, obviously you can get rid of the msgboxes if you want to:
You need to set a reference to Microsoft Scripting runtime in the VBA/ tools / references

Code:

---

Sub checkfl()
Dim FSO

Set FSO = CreateObject("Scripting.FileSystemObject")

Dim objFSO As New FileSystemObject

wkbk = ActiveWorkbook.Name


Worksheets("sheet1").Range("b3") = Path
Path = Worksheets("sheet1").Range("b3")


 For i = 3 To 8
      Workbooks(wkbk).Activate
        newname = Worksheets("sheet1").Range(Cells(i, 4), Cells(i, 4))
       
        filepath = Path & newname
        If objFSO.FileExists(filepath) Then
          MsgBox (filepath & " has been found")
          Workbooks.Open Filename:=filepath
        Else
          MsgBox (filepath & " Not found")
        End If
Next i
End Sub

---

I didn't understand this at all so obviously the code doesn't do it:

---

----------------------------------

Thanks a bunch for the superfast reply. That did help, but issue not sorted yet. below is the code which i tweaked a bit to suit the directory path

Sub checkfl()
Dim FSO

Set FSO = CreateObject("Scripting.FileSystemObject")

Dim objFSO As New FileSystemObject

wkbk = ActiveWorkbook.Name


' Worksheets("sheet1").Range("b3") = Path
Path = Worksheets("sheet1").Range("b3")


For i = 3 To 8
Workbooks(wkbk).Activate
newname = Worksheets("sheet1").Range(Cells(1, i), Cells(1, i))

filepath = Path & newname
If objFSO.FileExists(filepath) Then
Workbooks.Open Filename:=filepath
Call Paste

Else

End If
Next i
End Sub

------------------------------

Cell B3 - File path
Cell C3 - File name 1st half - For eg. ABC_
Cell D3 - File name 2nd half - For eg : 12344.csv

And the range goes from C3, D3 to C8, D8

The new name is not getting picked up. While testing the macro, the path gets picked up, but the "newname" shows as "", which i am assuming the file name is not getting picked up.

Any suggestions as to how i can rectify this.

Thanks for your help in advance
N

The probelm is the way you are addressing the name which originally you said were in the range D3 to D8, in the code I wrote I used the
Range(cells(row, col), cells(row,col)) addressing mode, this is to make it easy to use an index

Code:

---

newname = Worksheets("sheet1").Range(Cells(i, 4), Cells(i, 4))

---

the index loop goes from 3 to 8 and the column is col 4, ( A,B,C,D that makes D column 4)
so my code picks up D3 then D4 then D5, etc.

Your code is not picking up anything like what you want

Code:

---

newname = Worksheets("sheet1").Range(Cells(1, i), Cells(1, i))

---

this will pick in sequence Row 1, column 3 (C1) , thne Row 1 column 4 ( D1), then (E1)


I think what you want to do do now is very simple , you need to concatenate C3 and D3 , thenn C4 and D4
so change the code to :

Code:

---

newname = Worksheets("sheet1").Range(Cells(i, 3), Cells(i, 3))  &  Worksheets("sheet1").Range(Cells(i, 4), Cells(i, 4))

---

Quote:

---

Originally Posted by offthelip

The probelm is the way you are addressing the name which originally you said were in the range D3 to D8, in the code I wrote I used the
Range(cells(row, col), cells(row,col)) addressing mode, this is to make it easy to use an index

Code:

---

newname = Worksheets("sheet1").Range(Cells(i, 4), Cells(i, 4))

---

the index loop goes from 3 to 8 and the column is col 4, ( A,B,C,D that makes D column 4)
so my code picks up D3 then D4 then D5, etc.

Your code is not picking up anything like what you want

Code:

---

newname = Worksheets("sheet1").Range(Cells(1, i), Cells(1, i))

---

this will pick in sequence Row 1, column 3 (C1) , thne Row 1 column 4 ( D1), then (E1)


I think what you want to do do now is very simple , you need to concatenate C3 and D3 , thenn C4 and D4
so change the code to :

Code:

---

newname = Worksheets("sheet1").Range(Cells(i, 3), Cells(i, 3))  &  Worksheets("sheet1").Range(Cells(i, 4), Cells(i, 4))

---

---

---------------

Thanks a ton for your help! That worked.. !!

Thanks & Regards
N