Open a Excel file with a certain prefix using VBA?

[Sunsadymark]

Is there a way to open a Excel file with a certain prefix using VBA?

Like I have my files stored in a folder C:\excel files1\ with name being ABC001.xlsx and DEF002.xlsx.

I have written two macros to process them individually, which involves opening both files, process and then closing them. But I want to do my work in just one click. What should I do?

What if I have multiple folders like excel files3, excel files4, excel files5, excel files6 all with excel files named using same prefix like ABCxxx and DEFxxx. Can we write a code to be able to open them and process them all?

 

[sastoka]

Hi there,

Yes it is indeed possible,

assigning a variable to the actual name of the file and have update each time you move on to the next file.

the path will look like something like that

 "C:\excel files1\ABC" & variable & ".xlsx"

that should point you in the right direction.

Regards,

 

[daverunt]

Here's a quick example of checking one selected folder.
You need to list your prefixes in a column and put a start button next to it to call the macro.
Select the prefix
Press Start
Select the folder and the first file in it.
It should open files in the folder that match the prefix, throw up a message box, which is where you process the file, then close the file when you press OK.

It might give you some pointers to how you want to do it.

To recurse through folders you'll need to search for recursing through folders using FileSystemObject because you can't do it with the Dir method.

Sub Select_Files()


Prfx = ActiveCell.Value  ' relies on you selecting the cell with the prefix. You should add error checking in case empty cell selected.
       
'Display Open Dialog
    PrefxFile = Application.GetOpenFilename("Files (*.*)," & _
    "*.*", 1, "Select File", "Open", False)
    
'If the user cancels file selection then exit
    If TypeName(PrefxFile) = "Boolean" Then
        Exit Sub
    End If
    
   
    sFile = Dir("*.*")
               
'Cycle through the directory
 Do While sFile <> ""
 
'See if sFile name contains selected prefix
 If InStr(sFile, Prfx) > 0 Then
 
'Open it if it matches
    Workbooks.Open sFile
    Set sFile = ActiveWorkbook

'Process it
    MsgBox ("file " & ActiveWorkbook.Name & " open - do something with it")

      
'Close the csv file
    sFile.Close False
    
   
 End If
 sFile = Dir
 Loop
    
End Sub