CODEWITHSUNDEEP
excelvba
Thompson Ho
Thompson Ho

Reputation: 71

Prompt user to select multiple files and perform the same action on all files

The user has hundreds of text files and requires making a change in the data before saving and closing them.

Below is the code I have for a single file; how can I make it handle multiple files?

Sub transformTxt()
    vFileName = Application.GetOpenFilename("Text Files (*.edi),*.txt") 'prompt file selection
    Workbooks.OpenText Filename:=vFileName, _
                       Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, _
                       TextQualifier:=xlDoubleQuote, _
                       ConsecutiveDelimiter:=False, Tab:=False, _
                       Semicolon:=False, Comma:=False, Space:=False, _
                       Other:=True, OtherChar:="*", TrailingMinusNumbers:=True, _
                       Local:=True                              'open *delimited txt in excel
    
    Call Transform                                      'replace values in prompt file
    
    ActiveWorkbook.SaveAs Filename:=SaveToDirectory & ActiveWorkbook.Name & ".xls", _
                          FileFormat:=xlWorkbookNormal
    ActiveWorkbook.Close                                  'save and close it
End Sub

Upvotes: 2

Views: 17593

Answers (1)

41686d6564
41686d6564

Reputation: 19641

Use Application.FileDialog with AllowMultiSelect = True:

Sub SelectMultipleFiles()
    Dim fDialog As FileDialog
    Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
    With fDialog
        .AllowMultiSelect = True
        .Title = "Please select the files"
        .Filters.Clear
        .Filters.Add "All supported files", "*.txt;*.edi"
        .Filters.Add "Text Files", "*.txt"
        .Filters.Add "EDI files", "*.edi"

        If .Show = True Then
            Dim fPath As Variant
            For Each fPath In .SelectedItems
                ' Use 'fPath' to do whatever you want.
            Next
        End If
    End With
End Sub

Hope that helps.

Upvotes: 9

Related Questions