excel - 提示用户选择多个文件并对所有文件执行相同的操作

Question

用户有数百个文本文件，需要在保存和关闭数据之前对数据进行更改。
以下是我对单个文件的代码；我怎样才能让它处理多个文件？

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

Answer 1

使用 Application.FileDialog 与 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

希望有帮助。