VBA userform-Select multiple workbooks with diff names through browse dialogue.

[THE_NEW_XL_GUY]

Hi guys,

Need a way to select multiple csv/xls files with different names through browse function.

code I have now:

Fname = Application.GetOpenFilename(FileFilter:="XLS Files (*.xls),*.xls,CSV Files (*.csv),*.csv", Title:="Select a file or files",
            MultiSelect:=True)

I have around 10 files with different names, names way vary slightly each time I browse.

All I want to know is there a way to search the folder with keywords of file and then open the files and perform action specified with that keyword,
close the opened 10 files. I have 10 macros to be performed on them

thanks in advance.

 

[THE_NEW_XL_GUY]

fNameAndPath = Application.GetOpenFilename(fileFilter:="Excel Files (*.CSV), *.CSV", Title:="Select File To Be Opened", MultiSelect:=True)
    If Not IsArray(fNameAndPath) Then Exit Sub

    For Each MyFile In fNameAndPath
        Set wb = Workbooks.Open(MyFile)==========(how to search for specific file name in myfile array.. 
        
        ' do stuff with workbook that has been opened

          if myfile= test1.csv then
             


          elseif myfile=test2.csv then
            'do my macro2

          elseif myfile=test3.csv then
            'do my macro3

end if.

        
        wb.Close SaveChanges:=False
        
    Next MyFile
    
End Sub

 

[THE_NEW_XL_GUY]

updated code in macro1 place

fNameAndPath = Application.GetOpenFilename(fileFilter:="Excel Files (*.CSV), *.CSV", Title:="Select File To Be Opened", MultiSelect:=True)
     If Not IsArray(fNameAndPath) Then Exit Sub

     For Each MyFile In fNameAndPath
         Set wb = Workbooks.Open(MyFile)==========(how to search for specific file name in myfile array.. 
         
         ' do stuff with workbook that has been opened

           if myfile= test1.csv then
              
  
               Set Fnd1 = Range("A1")
          Qty1 = WorksheetFunction.CountIf(Rows(1), "*shop3**high*")
    For Cnt1 = 1 To Qty1
       Set Fnd1 = Rows(1).Find("*shop3**high*",Fnd1, , xlWhole, , , False, , False)
       max_num = Application.WorksheetFunction.Max(Fnd1.EntireColumn)    ' maxnum value is copied to a cell in submit button
    Next Cnt1

           elseif myfile=test2.csv then
             'similar to macro1

           elseif myfile=test3.csv then
             similar to macro1

 end if.

         
         wb.Close SaveChanges:=False
         
     Next MyFile
     
 End Sub

kindly correct syntax or typo..

 

[THE_NEW_XL_GUY]

can we search just a keyword of file name like

if myfile= "*inputvalues*.csv"

@Norie any update mate?

correct my syntx please

 

[THE_NEW_XL_GUY]

is the syntax correct?

 

[THE_NEW_XL_GUY]

@Norie anything am missing?

fNameAndPath = Application.GetOpenFilename(fileFilter:="Excel Files (*.CSV), *.CSV", Title:="Select File To Be Opened", MultiSelect:=True)
     If Not IsArray(fNameAndPath) Then Exit Sub

     For Each MyFile In fNameAndPath
         Set wb = Workbooks.Open(MyFile)==========(how to search for specific file name in myfile array.. 
         
         ' do stuff with workbook that has been opened

           if myfile= test1.csv then
              
  
               Set Fnd1 = Range("A1")
          Qty1 = WorksheetFunction.CountIf(Rows(1), "*shop3**high*")
    For Cnt1 = 1 To Qty1
       Set Fnd1 = Rows(1).Find("*shop3**high*",Fnd1, , xlWhole, , , False, , False)
       max_num = Application.WorksheetFunction.Max(Fnd1.EntireColumn)    ' maxnum value is copied to a cell in submit button
    Next Cnt1

           elseif myfile=test2.csv then
             'similar to macro1

           elseif myfile=test3.csv then
             similar to macro1

 end if.

         
         wb.Close SaveChanges:=False
         
     Next MyFile
     
 End Sub

 

[THE_NEW_XL_GUY]

Hi @Norie
Am still stuck with here, really appreciate some help from you on the below

 fNameAndPath = Application.GetOpenFilename(fileFilter:="Excel Files (*.CSV), *.CSV", Title:="Select File To Be Opened", MultiSelect:=True)
     If Not IsArray(fNameAndPath) Then Exit Sub

     For Each MyFile In fNameAndPath
         Set wb = Workbooks.Open(MyFile)==========(how to search for specific file name in myfile array.. 
         
         ' do stuff with workbook that has been opened

           if myfile= test1.csv then
              
  
               Set Fnd1 = Range("A1")
          Qty1 = WorksheetFunction.CountIf(Rows(1), "*shop3**high*")
    For Cnt1 = 1 To Qty1
       Set Fnd1 = Rows(1).Find("*shop3**high*",Fnd1, , xlWhole, , , False, , False)
       max_num = Application.WorksheetFunction.Max(Fnd1.EntireColumn)    ' maxnum value is copied to a cell in submit button
    Next Cnt1

           elseif myfile=test2.csv then
             'similar to macro1

           elseif myfile=test3.csv then
             similar to macro1

 end if.

         
         wb.Close SaveChanges:=False
         
     Next MyFile
     
 End Sub