0

I have wrote a code which has browse dialog to select multiple files and save file names in array and loop them one by one perform action based on keyword in filename.

  1. as it has multi select option, I want to perform specific action based on keyword in filename
  2. am stuck in looping file names in array. I dkw where am going wrong
  3. correct my syntax if I wrong, bear with me as am very new to VBA

  4. appreciate any help

    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= "*test_one*" 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="*test_last*" then
        'similar to macro1
    elseif myfile=test3.csv then
        similar to macro1
    end if.
wb.Close SaveChanges:=False
Next MyFile
End Sub
braX
  • 11,506
  • 5
  • 20
  • 33
nvbie
  • 3
  • 1
  • 3
  • Please clarify 'stuck in looping'. Do you mean you are stuck with an error, stuck inside the loop? If its an error please describe the exact error. Either way its always helpful to use the Debug option to Step through the code. – shash Feb 23 '18 at 16:42
  • thanks for reply. am not stuck in loop or with any errors. its not working as expected. it just opens up first file and does nothing. How it should have worked: opens dialog box to browse for multiple files and run code according to keyword of filename. How it is: just opens first file if am selecting 3 files for an example and does nothing. Hope this clears up my question – nvbie Feb 26 '18 at 04:14

1 Answers1

1
if myfile= "*test_one*" then

If you're looking for part of a filename then you want something like this:

If LCase(myfile) Like "*test_one*" Then
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • Hi thanks on correction. Yes am looking for keyword in a filename which is present in array of filenames. and then perform required action based on keyword of that file. – nvbie Feb 26 '18 at 04:16