Excel VBA Dir() 文件类型更改时出错

Question

我试图更好地理解 Dir功能。我有一个 Dir循环对所有 .csv 执行操作目录中的文件，但当循环遇到另一种文件类型时，如 .txt ，它将出错而不是继续下一个 .csv .元素。

这是我的代码的相关部分。

strSourceExcelLocation = "\\rilinfs001\Interdepartmental\Billings & Deductions\Billing Coordinators\MCB Reports\East\Monthly Quality MCBs (CMQ & SECMQ)\2019\Individual_Reports\" & fldr & "\"

strWorkbook = Dir(strSourceExcelLocation & "*.csv*")

Do While Len(strWorkbook) > 0
    'Open the workbook
    Set wbktoExport = Workbooks.Open(Filename:=strSourceExcelLocation & strWorkbook)

    'Export all sheets as single PDF
    Call Export_Excel_as_PDF(wbktoExport)

    'Get next workbook
    strWorkbook = Dir

    'Close Excel workbook without making changes
    wbktoExport.Close False
Loop

所以如果只有 .csv目录中的文件，那么这工作正常。当它遇到另一种文件类型时，就会发生错误。

错误在线

strWorkbook = Dir

运行时错误 5:无效的过程调用或参数

我在 .csv 中使用通配符的方式是否遗漏了什么？一开始？

谢谢

Answer 1

解决了我的问题。

问题似乎是因为当我调用另一个程序时，我有另一个 Dir如果该文件夹不存在，则在该子文件夹中创建一个新文件夹。所以基本上我有一个 Dir在 Dir ，这显然是不好的。

我将文件夹创建部分移到了我的程序的最开始，所以它在我开始 Dir 之前执行。用于循环所有 CSV文件。

Option Explicit

Sub Loop_Dir_for_Excel_Workbooks()
Dim strWorkbook As String, wbktoExport As Workbook, strSourceExcelLocation As String, fldr As String, strTargetPDFLocation As String, d As String

strTargetPDFLocation = "\\nhchefs001\Accounting\IMAGING\BILLINGS & DEDUCTIONS\EAST MCB FILES\"
'***** Creating a folder to save the PDFs in. Naming the folder with today's date *****
d = Format(Date, "mm-dd-yyyy")
strTargetPDFLocation = "\\nhchefs001\Accounting\IMAGING\BILLINGS & DEDUCTIONS\EAST MCB FILES\" & d & "\"
If Len(Dir(strTargetPDFLocation, vbDirectory)) = 0 Then MkDir strTargetPDFLocation

fldr = InputBox("Input the EXACT Folder Name that you want to create PDFs for")

strSourceExcelLocation = "\\rilinfs001\Interdepartmental\Billings & Deductions\Billing Coordinators\MCB Reports\East\Monthly Quality MCBs (CMQ & SECMQ)\2019\Individual_Reports\" & fldr & "\"

'Search all Excel files in the directory with .xls, .xlsx, xlsm extensions
strWorkbook = Dir(strSourceExcelLocation & "*.csv")

Do While Len(strWorkbook) > 0
    'Open the workbook
    Set wbktoExport = Workbooks.Open(Filename:=strSourceExcelLocation & strWorkbook)

    'Export all sheets as single PDF
    Call Export_Excel_as_PDF(wbktoExport, strTargetPDFLocation)

    'Close Excel workbook without making changes
    wbktoExport.Close False

    'Get next workbook
    strWorkbook = Dir

Loop

End Sub