Results 1 to 3 of 3

Open a Excel file with a certain prefix using VBA?

This is a discussion on Open a Excel file with a certain prefix using VBA? within the Excel Questions forums, part of the Question Forums category; Is there a way to open a Excel file with a certain prefix using VBA ? Like I have my ...

  1. #1
    New Member
    Join Date
    Apr 2017
    Posts
    1

    Question Open a Excel file with a certain prefix using VBA?

    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?

  2. #2
    Board Regular
    Join Date
    Jun 2014
    Location
    CH
    Posts
    192

    Default Re: Open a Excel file with a certain prefix using VBA?

    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

    Code:
     "C:\excel files1\ABC" & variable & ".xlsx"
    that should point you in the right direction.

    Regards,

  3. #3
    Board Regular
    Join Date
    Jul 2009
    Posts
    1,328

    Default Re: Open a Excel file with a certain prefix using VBA?

    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.

    Code:
    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
    Last edited by daverunt; Apr 21st, 2017 at 12:54 PM.

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com