CODEWITHSUNDEEP
excelvba
TBoulz
TBoulz

Reputation: 351

Excel VBA Dir() Error when file type changes

I'm trying to better understand the Dir function. I have a Dir loop to take action on all .csv files in the directory, but when the loop comes across another file type, like .txt, it will error instead of moving on to the next .csv. item.

This is the relevant portion of my code.

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

So if there are only .csv files in the directory, then this works fine. When it comes across another file type, an error occurs.

The error is on line

strWorkbook = Dir

Run-time error 5: Invalid procedure call or argument

Am I missing something with the way I use the wildcards in the .csv at the beginning?

Thank you

Upvotes: 1

Views: 707

Answers (2)

TBoulz
TBoulz

Reputation: 351

Solved my issue.

The problem seems to have been because when I called another procedure, I had another Dir in that sub to create a new folder if one didn't already exist. So basically I had a Dir in a Dir, which apparently is bad.

I moved the folder creation part to the very beginning of my procedure, so it is executed before I begin the Dir for looping through all the CSV files.

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

Upvotes: 2

Vityata
Vityata

Reputation: 43585

Try to hardcode the path and give it a try again. Probably the error is something really small in the hardcoding. E.g., in the code below, replace C:\Users\username\Desktop\AAA\ with the path of the file. Then run it. Do not forget the last \. It should work:

Sub TestMe()

    Dim workbookPath As String
    workbookPath = Dir("C:\Users\username\Desktop\AAA\" & "*.csv")

    Do While Len(workbookPath) > 0
        Debug.Print workbookPath
        workbookPath = Dir
    Loop

End Sub

Upvotes: 0

Related Questions