excel - FileSystemObject - 无法打开保存在文件夹中的文件

Question

下面是用于打开文件并将所需数据粘贴到这些文件中并在完成工作后关闭这些文件的代码。当我运行这段代码时，没有文件在打开和

Run-time error-13 : Type mismatch

正在抛出这行代码

Set fldr = fso.GetFolder(Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1))

请提出错误在哪里。尽管它在其他文件中运行良好:

Sub split()

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Dim fso As New FileSystemObject
    Dim fl As File
    Dim fldr As Folder
    Dim akb As Workbook
    Dim tkb As Workbook
    Dim RawAL As Worksheet
    Dim RawAL1 As Worksheet
    Dim RawSR As Worksheet
    Dim RawSR1 As Worksheet
    Dim lrow As Integer
    Dim ALSummary As Worksheet
    Dim ALSummary1 As Worksheet

    MsgBox "Please select the path where Access Log Report are being saved."
    Application.FileDialog(msoFileDialogFolderPicker).Show

    Set fldr=fso.GetFolder(Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1))
    lrow = Cells(Rows.Count, 2).End(xlUp).Row

    For Each fl In fldr.Files
        Workbooks.Open fl

        Set akb = ActiveWorkbook
        Set tkb = ThisWorkbook
        Dim i As Integer

        Set RawAL = akb.Sheets("Raw Access Log")
        Set RawAL1 = tkb.Sheets("Raw Access Log")
        Set RawSR = akb.Sheets("Raw Submittal Report")
        Set RawSR1 = tkb.Sheets("Raw Submittal Report")
        Set ALSummary = akb.Sheets("Access Log Summary")
        Set ALSummary1 = tkb.Sheets("Access Log Summary")

        RawAL.Visible = xlSheetVisible
        RawAL.AutoFilterMode = False
        RawAL.Range("a1").CurrentRegion.ClearContents
        RawAL1.Activate
        RawAL1.AutoFilterMode = False
        RawAL1.Range("a1").CurrentRegion.AutoFilter Field:=1, _
                Criteria1:=ALSummary1.Range("b5"), Operator:=xlFilterValues
        RawAL1.Range("A1:f65000").SpecialCells(xlCellTypeVisible).Copy
        RawAL.Range("a1").PasteSpecial xlPasteValues

        RawSR.Visible = xlSheetVisible
        RawSR.AutoFilterMode = False
        RawSR.Range("a1").CurrentRegion.ClearContents
        RawSR1.Activate
        RawSR1.AutoFilterMode = False
        RawSR1.Range("a1").CurrentRegion.AutoFilter Field:=1, _
                Criteria1:=ALSummary1.Range("b5"), Operator:=xlFilterValues
        RawSR1.Range("A1:E500").SpecialCells(xlCellTypeVisible).Copy
        RawSR.Range("a1").PasteSpecial xlPasteValues
        Application.CutCopyMode = False

        RawSR.Visible = xlSheetHidden
        ALSummary.Activate
        akb.Close True

    Next fl
End Sub

Answer 1

您应该使用 .GetFolder()使用后的方法.show ，从 .show 中获取值确保确实选择了一个文件夹(这将返回 -1 )，然后使用 .selectitems() 中的字符串继续传递文件夹路径。

像这样的东西应该工作:

with application.filedialog(msoFileDialogFolderPicker)
    if .show = -1 then 
        set fldr = fso.getfolder(.selecteditems(1))
    else
        msgbox "No Folder selected"
    end if
end with