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]

does looping files work on this? if so How to check for 10 different file names and then assign specific functions based on the keyword of files. Help needed.

 

[Norie]

The code you posted should allow the user to select multiple files and the names of those files should be returned as an array in FName.

You could then loop through the array of filenames, open each file, apply whatever code is appropriate to the file and then close it.

 

[THE_NEW_XL_GUY]

yea correct.. reason I asked because am very week in loops-arrays, I don't know much of loops and arrays. so requested here. thanks for suggestion.

 

[THE_NEW_XL_GUY]

@Norie I have tweaked some code to my requirement.. kindly correct it and help me to open filenames in array one by one to perform diff macros each

Sub ListFiles()
Dim MyFolder As String
Dim MyFile As String
Dim j As Integer
Dim fNameAndPath As Variant, wb As Workbook, ws As Worksheet

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

myfile= Dir(fnameAndpath & "\*.xls")
Do While MyFile <> ""
    j = j + 1
    Cells(j, 1).Value = MyFile
    MyFile = Dir
Loop
End Sub

 

[Norie]

Here's basic code to open each of the workbooks the user has selected.

Sub ListFiles()
Dim fNameAndPath As Variant, wb As Workbook, ws As Worksheet
Dim MyFolder As String
Dim MyFile As Variant

    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)
        
        ' do stuff with workbook that has been opened
        
        wb.Close SaveChanges:=False
        
    Next MyFile
    
End Sub

 

[THE_NEW_XL_GUY]

thanks for that! just one more thing.. how to call specific file name in loop? example.. there are 5 files and 5 diff macros. search test1.xls file and run macro test1 close it, then search for test2.xls and run macro test2 and close... so on

 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
        
        wb.Close SaveChanges:=False
        
    Next MyFile
    
End Sub

Help me with this.

 

[Norie]

You have the filename of the workbook that's been opened in both MyFile and wb.Name, so you can use either of those to determine the code to run against the workbook.

 

[THE_NEW_XL_GUY]

okay.. kinda understood what you meant.. New to this just trying to analyse it.. heres what I got till now:

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
            'do my macro1(function)

          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

One last thing.. can you just correct my syntx/type errors in above and give some small idea on how to check those myfile filenames with set of keywords like using "*te*" "*st2*" as my file names changes every day with some changes. thanks a ton for so much of info given before.

 

[Norie]

What do macro1, macro2 etc actually do?