CODEWITHSUNDEEP
vbaexcelchallenge-response
Anton
Anton

Reputation: 13

(Edited)VBA Select files with specific letters and numbering out of identical names with added variation

I'm starting to work with macros and I have a list of over 100 files to open in VBA with names such as: Mark001.xls Mark_001_initial.xls Mark001_improvement.xls Mark002.xls Mark002_initial. Marc002_Improvement.xls Pol001.xls ...

Sub Macro1()
Dim FilesToOpen

FilesToOpen = Application.GetOpenFilename _
    (FileFilter:="EXLS Files(*.xls), *.xls," & "Mark??? Files (Mark???.xls), Mark???.xls", MultiSelect:=True, Title:="EXLS Files To Open")

End Sub

This opens a window to select files, however there are no files to select,it doesn't identify them

There are also identical files in .txt format, but I only need to open the ones that are of type Mark001.xls, the larger names are irrelevant.

Also I need to copy them to a different folder. I tried to use OpenFile, SerchFile, and selectFile applications but had no success.

Thank you very much!

Upvotes: 0

Views: 1505

Answers (1)

paul bica
paul bica

Reputation: 10715

For Application.GetOpenFilename wildcards work only for the file extension as in

FileFilter:="Excel files (*.xl*)," & "*.xl*"

That will return all *.xls and *.xlsx files

Other Options:

Application.FileDialog (msoFileDialogOpen Or msoFileDialogFilePicker)

.

Sub openWildFile()     'use params to make it generic: "ByVal partialName As String"

    Const partialName   As String = "*Mark_"
    Const partialExt    As String = "*.xl*"

    Dim selectedFile    As String, dlg As Object        'A 3rd option is to use APIs

    Set dlg = Application.FileDialog(msoFileDialogOpen) 'Or (msoFileDialogFilePicker)

    With dlg

        .Title = "Select " & partialName & " File"

        With .Filters
            .Clear
            .Add partialName & " Files", partialExt
        End With

        .AllowMultiSelect = True
        .InitialFileName = partialName & partialExt

        If (.Show <> 0) Then selectedFile = Trim(.SelectedItems.Item(1))

    End With
End Sub

.

To copy files use this:

FileCopy SourceFile, DestinationFile    ' Copy source file to target

Upvotes: 1

Related Questions