0

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!

Anton
  • 13
  • 4
  • Hi, and welcome to SO! Try reading [How do I ask a good question?](http://stackoverflow.com/help/how-to-ask) to increase the likelihood that you'll get an answer. Also, this isn't a code-writing service, so post what you've got (just edit your post and add it in) and pinpoint where it's failing, and somebody will stop by to help you fix it. – FreeMan Jun 13 '15 at 02:28
  • Use wildcards `Mark_???.TXT` Search google on how to use wildcards with `DIR` to open files from a particular folder. – Siddharth Rout Jun 13 '15 at 05:59
  • Thank you @FreeMan for clarifying the format. – Anton Jun 13 '15 at 18:39
  • Thanks @Siddharth Rout for the MARK_???.TXT it was what i was looking for. However the code is not working yet. The DIR command is only for sellecting one file as I understand but I will have to go through over a 100 of them and need to open each of them as worksheets in one workbook – Anton Jun 13 '15 at 18:39

1 Answers1

1

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
paul bica
  • 10,557
  • 4
  • 23
  • 42
  • Thank you so much `@paul bica`!!! I made a small change in `.InitalFileName=partialName` Otherwise the extension part `*` would overwrite the boundaries of the partial name, It would be complete name instead of partial. Also, Why is **If 0 then** part needed? – Anton Jun 14 '15 at 03:29
  • I'm glad you got it working. The **If 0 then** part is for you to choose between the 2 types of dialogs - you can change it to **If 1 then** to see the other one, but they are similar so I'll just take that part out – paul bica Jun 14 '15 at 03:44
  • Thanks! Got the idea! – Anton Jun 14 '15 at 20:41