时间戳

首页 > 解决方案 > FileSystemObject - 无法打开保存在文件夹中的文件

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

问题描述

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

运行时错误 13：类型不匹配

正在抛出这行代码

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

标签： excelvba

解决方案

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

像这样的东西应该工作：

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

推荐阅读