Extract info from files in a folder except for certain name

gailb
3StarLounger
Posts: 254
Joined: 09 May 2020, 14:00

Extract info from files in a folder except for certain name

Post by gailb »

With the code below, I need to extract info from files in a folder except for one file. The file to skip is, "Template.xlsx", and below is what I tried.

Code: Select all

Do While myFile <> "" Or myFile <> "Template.xlsx"

Code: Select all

Sub LoopAllExcelFilesInFolder()

    Dim wb          As Workbook
    Dim wsSrc       As Worksheet: Set wsSrc = ActiveWorkbook.Sheets(1)
    Dim myPath      As String
    Dim myFile      As String
    Dim strFile     As String
    Dim Path        As String
    Dim myMaster    As String
    Dim r           As Long: r = 1

    myPath = ActiveWorkbook.Path & Application.PathSeparator
    strFile = "*.xlsx"
    myFile = Dir(myPath & strFile)
    
    Do While myFile <> "" Or myFile <> "Template.xlsx"
        Set wb = Workbooks.Open(Filename:=myPath & myFile)
        DoEvents
        r = r + 1
        wsSrc.Cells(r, 2) = myFile
        wsSrc.Cells(r, 3) = wb.Sheets(1).Range("M2")
        wsSrc.Cells(r, 4) = wb.Sheets(1).Range("M17")
        wb.Close SaveChanges:=True
        DoEvents
        myFile = Dir
    Loop

End Sub

User avatar
StuartR
Administrator
Posts: 12577
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: Extract info from files in a folder except for certain name

Post by StuartR »

This code won't work because you exit the loop as soon as you find Template.xlsx. Try

Code: Select all

Sub LoopAllExcelFilesInFolder()

    Dim wb          As Workbook
    Dim wsSrc       As Worksheet: Set wsSrc = ActiveWorkbook.Sheets(1)
    Dim myPath      As String
    Dim myFile      As String
    Dim strFile     As String
    Dim Path        As String
    Dim myMaster    As String
    Dim r           As Long: r = 1

    myPath = ActiveWorkbook.Path & Application.PathSeparator
    strFile = "*.xlsx"
    myFile = Dir(myPath & strFile)
    
    Do While myFile <> "" 
        If myFile <> "Template.xlsx"
               Set wb = Workbooks.Open(Filename:=myPath & myFile)
               DoEvents
               r = r + 1
               wsSrc.Cells(r, 2) = myFile
               wsSrc.Cells(r, 3) = wb.Sheets(1).Range("M2")
               wsSrc.Cells(r, 4) = wb.Sheets(1).Range("M17")
               wb.Close SaveChanges:=True
               DoEvents
        End If
        myFile = Dir
    Loop

End Sub

StuartR


gailb
3StarLounger
Posts: 254
Joined: 09 May 2020, 14:00

Re: Extract info from files in a folder except for certain name

Post by gailb »

Thanks Stuart. Works great.