Invalid procedure call or argument error

Hi

I have the following code to open each file in a directory and then call on two other macros. It works for a little but then stops and I receive the "Invalid procedure call or argument" error at the line "Filename = Dir". Is there something I have wrong?

Code the open workbooks:
Sub test()
Dim wbk As Workbook
Dim Filename As String
Dim Path As String
Path = "C:\Users\M\Desktop\Test\"
Filename = Dir(Path & "*" & ".xlsx")
'--------------------------------------------
 Do While Len(Filename) > 0  'IF NEXT FILE EXISTS THEN
    Set wbk = Workbooks.Open(Path & Filename)
    '
    Call Copy_To_Worksheets
    Call Test_File_Exist_With_Dir_v1
            
    wbk.Close True
    Filename = Dir 'error is here
Loop
End Sub

Do you have a Dir in one of the other routines?

any way this can be solved Kenneth?

I know of 4 ways. Some make an array of filenames. You can then iterate the array. If you have just the root folder to iterate, I can show you the Dir() method to create an array. Here is one such method.

'http://spreadsheetpage.com/index.php/tip/getting_a_list_of_file_names_using_vba/
Sub Test_GetFileList()
    Dim p As String, x As Variant, i As Integer

    p = ThisWorkbook.Path & "/*.xls"
    x = GetFileList(p)
    Select Case IsArray(x)
        Case True 'files found
            MsgBox UBound(x), , "Count of Found Files"
            Sheets("Sheet1").Range("A:A").Clear
            For i = LBound(x) To UBound(x)
                Sheets("Sheet1").Cells(i, 1).Value = x(i)
            Next i
        Case False 'no files found
            MsgBox "No matching files"
    End Select
End Sub

Function GetFileList(FileSpec As String) As Variant
'   Returns an array of filenames that match FileSpec
'   If no matching files are found, it returns False

    Dim FileArray() As Variant
    Dim FileCount As Integer
    Dim FileName As String
    
    On Error GoTo NoFilesFound

    FileCount = 0
    FileName = Dir(FileSpec)
    If FileName = "" Then GoTo NoFilesFound
    
'   Loop until no more matching files are found
    Do While FileName <> ""
        FileCount = FileCount + 1
        ReDim Preserve FileArray(1 To FileCount)
        FileArray(FileCount) = FileName
        FileName = Dir()
    Loop
    GetFileList = FileArray
    Exit Function

'   Error handler
NoFilesFound:
    GetFileList = False
End Function

My batch method gives you all the power and options of the command shell's DIR method which is nothing like VBA's Dir() method.

HTML Code: 

this obviously gives me the names of the files but how can I use this in relation to my original problem Kenneth?

The test routines show how. Once you have the array, just iterate it in a For i = lbound(arrayname) to ubound(arrayname) or a For Each variantvariable in arrayname loop.

Yes, it is in the "Test_File_Exists_With_Dir_v1" macro, as shown below"

Sub Test_File_Exist_With_Dir_v1()
    Dim FilePath As String
    Dim TestStr As String
    Dim sh As Worksheet
    Dim DestWb As Workbook
    Dim ws1 As Worksheet
    
        
    For Each sh In Worksheets
       If sh.Name <> "Venue Table" Then
       
        'path and file to check
        FilePath = ThisWorkbook.Path & "\" & sh.Name & " " & "2016" & ".*"
        
        'reset testStr
        TestStr = ""
        
        'set teststr
        On Error Resume Next
        TestStr = Dir(FilePath)
        On Error GoTo 0
        
        If TestStr = "" Then
                        
            If sh.Visible = -1 Then
                sh.Copy
                'Set Destwb to the new workbook
                Set DestWb = ActiveWorkbook
                
                'Save the new workbook and close it
                With DestWb
                    .SaveAs ThisWorkbook.Path & "\" & sh.Name & " " & "2016", FileFormat:=51
                    .Close False
                End With
            End If
        Else
            If Right(TestStr, 4) = "xlsx" Then
                Set DestWb = Workbooks.Open(ThisWorkbook.Path & "\" & TestStr) 'change xlsx if this is not your default excel extention
                
                Set ws1 = ActiveSheet
                sh.Range("A2:N" & sh.Cells(Rows.Count, "A").End(xlUp).Row).Copy
                ws1.Range("A" & ws1.Cells(Rows.Count, "A").End(xlUp).Row + 1).PasteSpecial xlPasteValuesAndNumberFormats
                ws1.Range("A2").CurrentRegion.Sort Key1:=ws1.Range("A2"), Order1:=xlAscending, Key2:=ws1.Range("C2"), Order2:=xlAscending, Header:=xlGuess
                DestWb.Close True
            Else
                MsgBox TestStr & " exist but is not an Excel file"
            End If
        End If
    End If
    Next
        
End Sub

thank you Kenneth.

Where would I place the code above in relation to both of my macros?

thanks Kenneth. Unfortunately I do not know how to do this. If you could assist me further, that would be really appreciated.

'Code the open workbooks:
Sub test()
  Dim wbk As Workbook, sFileName As String, sPath As String
  Dim i As Long, v() As Variant
  
  sPath = "C:\Users\M\Desktop\Test\"
  sFileName = sPath & "*.xlsx"
  
  'Make a varaint array of filenames.
  v() = GetFileList(sFileName)
  
  For i = LBound(v) To UBound(v)
    'Debug.Print v(i)
    Set wbk = Workbooks.Open(sPath & v(i))
    Test_File_Exist_With_Dir_v1
    Copy_To_Worksheets
    wbk.Close True
  Next i
End Sub

Function GetFileList(FileSpec As String) As Variant
'   Returns an array of filenames that match FileSpec
'   If no matching files are found, it returns False

    Dim FileArray() As Variant
    Dim FileCount As Integer
    Dim FileName As String
    
    On Error GoTo NoFilesFound

    FileCount = 0
    FileName = Dir(FileSpec)
    If FileName = "" Then GoTo NoFilesFound
    
'   Loop until no more matching files are found
    Do While FileName <> ""
        FileCount = FileCount + 1
        ReDim Preserve FileArray(1 To FileCount)
        FileArray(FileCount) = FileName
        FileName = Dir()
    Loop
    GetFileList = FileArray
    Exit Function

'   Error handler
NoFilesFound:
    GetFileList = False
End Function

thanks Kenneth, worked perfectly.