how to read a text using condition if

Question

I have an issue and I need your help. here is the problem. I have inside a folder some excel files that I have to open automatically in order to make some operations. Those files have the same name except the number of the files like this:

Folder name : Extraction_Files Files name : - "System_Extraction_Supplier_1" - "System_Extraction_Supplier_2" - "System_Extraction_Supplier_3"

The number of files can change so i used a loop Do While to count the number of files, then the plan is to use a loop for I =1 to ( number of files) to open all of theme.

please read my code. I know that i used a wrong way to read file name using a loop for but I share it because I don't have an other idea.

Here is my code :

Sub OpenFiles ()

    Dim MainPath as String 
    Dim CommonPath as String 
    Dim Count As Integer
    Dim i As Integer

   ' the main path is " C:\Desktop\Extraction_Files\System_Extraction_Supplier_i"
   'with i = 1 to Count ( file number )


    CommonPath = "C:\Desktop\Extraction_Files\System_Extraction_Supplier_*"

   'counting automatically the file number

    Filename = Dir ( CommonPath )

    Do While Filename <> "" 

       Count = Count + 1 
       Filename = Dir ()

    Loop

'the issue is below because this code generate a MsgBox showing a MainPath with the index i like this
'"C:\Desktop\Extraction_Files\System_Extraction_Supplier_i" 
' so vba can not find the files

    For i = 1 To count 

      MainPath = "C:\Desktop\Extraction_Files\System_Extraction_Supplier_" & "i" 
      MsgBox  MainPath  & 
      Workbooks.Open MainPath 

    Next 

End Sub 

what is the best approach to this?

Answer 1

Why not count as you open them. You're already identifying them so why not open each file as you go:

Sub OpenFiles()

    Dim Filename As String
    Dim CommonPath As String
    Dim Count As Integer

    CommonPath = "C:\Desktop\Extraction_Files\"

    Filename = Dir(CommonPath & "System_Extraction_Supplier_*")

    Do While Filename <> ""

        MsgBox Filename
        Workbooks.Open CommonPath & Filename
        Count = Count + 1
        Filename = Dir()

    Loop

End Sub

PS. It might be worth adding .xl* or similar to the end of your search pattern to prevent Excel trying to open files that aren't Excel files:

Filename = Dir(CommonPath & "System_Extraction_Supplier_*.xl*")