Consulting

Results 1 to 5 of 5

Thread: Vba Code to open multiple files from a specific directory

  1. #1
    VBAX Newbie
    Joined
    Jan 2018
    Posts
    3
    Location

    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

  2. #2
    VBAX Expert
    Joined
    May 2016
    Posts
    604
    Location
    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
    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:

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

  3. #3
    VBAX Newbie
    Joined
    Jan 2018
    Posts
    3
    Location
    Quote Originally Posted by offthelip View Post
    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
    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

  4. #4
    VBAX Expert
    Joined
    May 2016
    Posts
    604
    Location
    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

    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

    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 :

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

  5. #5
    VBAX Newbie
    Joined
    Jan 2018
    Posts
    3
    Location
    Quote Originally Posted by offthelip View Post
    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

    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

    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 :

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •