+ Reply to Thread
Results 1 to 5 of 5

"Invalid Call or Argument" in While Loop using MyFile = Dir

Hybrid View

  1. #1
    Registered User
    Join Date
    01-01-2013
    Location
    australia
    MS-Off Ver
    Excel 2007
    Posts
    6

    "Invalid Call or Argument" in While Loop using MyFile = Dir

    Hi guys,
    When running the following code, once all the files have been looped through, I occasionally get an "Invalid call or argument" error at the bottom "myFile = Dir" line. It happens only sometimes, and other times the code executes fine.
    What could I be missing? I know without much more information its a bit vague, but where would you suggest I investigate as to the cause? I'm just about to stick On Error Resume Next above it, since it only ever occurs after all the workbooks have been looped through and the loop is completed anyway - But thats lazy... any idea?


    ''' Get the array of files (excel file types only using .xl* )
            Dim MyFile As String
            MyFile = Dir(MyFolder & "\*.xl*")
            
            ''' Start the file counter
            Dim fileCount As Integer
            fileCount = 0
    
     ''' Start cycling through the files
    Do While MyFile <> ""
            ''' Do some stuff
    
            ''' On to the next workbook file
                    MyFile = Dir
                    fileCount = fileCount + 1
    Loop

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644
    Can we see the missing code?

    That might actually be where the problem lies.
    If posting code please use code tags, see here.

  3. #3
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: "Invalid Call or Argument" in While Loop using MyFile = Dir

    do you have any other calls to Dir in your missing code?
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  4. #4
    Registered User
    Join Date
    01-01-2013
    Location
    australia
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: "Invalid Call or Argument" in While Loop using MyFile = Dir

    The only times I use "Dir" are already shown in the original post. But below is the full code inside the loop....

     Do While MyFile <> ""
                    Debug.Print MyFile
                    Debug.Print (InStr(MyFile, "backup"))
                    
                    '' If the filename doesnt include the word "Backup"
                    If ((InStr(MyFile, "backup") = 0) And (InStr(MyFile, "Backup") = 0)) Then
                                   
                    
                    ''' 1. Add the new formulas and template into the DB
                        Worksheets("ConformanceDBTemplate").Activate
                        ActiveSheet.Range(conformanceDBTemplateRange).Select
                        Selection.Copy
                        
                        Worksheets("CONFORMANCE DB").Select
                        Range("A" & newRecordRow).Select
                        ActiveSheet.Paste
                        
                    ''' 2. Open each workbook
                        'Set wb = Workbooks.Open(Filename:=MyFolder & "\" & MyFile)
                        Set wb = Workbooks.Open(MyFolder & "\" & MyFile, True, True)
                    
                    ''' 3. Copy the reconciliation data from the Block Reconciliatoin file we are currently looking at
                        '''' First - Test to see if the Block Workbook has an X4 Reconciliation worksheet, if not, add it. '''
                        doesR2SheetExist = False
                        For Each ws In wb.Worksheets
                            If ws.Name = "X4 Reconciliation" Then
                              doesR2SheetExist = True
                            End If
                        Next ws
                        
                        wb.Activate
                        If doesR2SheetExist = True Then
                            wb.Worksheets("X4 Reconciliation").Select
                            Range(reconciliationDataRange).Select
                            Selection.Copy
                        Else
                            Call ADD_X4_SHEET
                            wb.Worksheets("X4 Reconciliation").Select
                            Range(reconciliationDataRange).Select
                            Selection.Copy
                        End If
                    
                    ''' 4. Paste the data over to the CONFORMANCE DB worksheet
                        Workbooks(confDBName).Activate
                        Worksheets("CONFORMANCE DB").Select
                        Range(x4ValuesBeginColumnLetter & newRecordRow).Select
                        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                            :=False, Transpose:=False
                    
                    ''' 5. Input the date for the new record
                        Range("A" & newRecordRow).Value = newDate
                        Debug.Print newDate
                        
                    ''' 6. Input the Fret Name, Ver Name & Bench for the new record
                        Range("D" & newRecordRow).Value = wb.Worksheets("Presentation").Range("C2").Value ' Fret Name
                        Range("E" & newRecordRow).Value = wb.Worksheets("Grade Blocks").Range("B2").Value ' Ver Name
                        Range("F" & newRecordRow).Value = wb.Worksheets("Grade Blocks").Range("B3").Value ' Bench                    
                        
                    ''' 7. Close the workbook without saving
                        ' If we want to save, use this code: wb.Save
                        Application.CutCopyMode = False 'Clears the clipboard first
                        wb.Close (False)
                        
                        
                    ''' 8. Update the newRecordRow
                        newRecordRow = newRecordRow + numberOfRowsPerRecord
                        
                    Else
                        
                        backup_count = backup_count + 1
                    
                    End If
                        
                    ''' 9. On to the next reconciliation workbook file
                    MyFile = Dir 
                    fileCount = fileCount + 1
            Loop

  5. #5
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,996

    Re: "Invalid Call or Argument" in While Loop using MyFile = Dir

    What is the code for ADD_X4_SHEET?
    Everyone who confuses correlation and causation ends up dead.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. "Invalid use of ME keyword" when trying to loop through all userform controls
    By max3732 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-07-2012, 03:10 AM
  2. Replies: 3
    Last Post: 04-28-2010, 05:54 PM
  3. "Invalid procedure call or argument" error when creating Pivot table
    By Kaigi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-31-2009, 06:02 AM
  4. Invalid Procedure call or argument
    By T De Villiers in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-25-2006, 10:05 AM
  5. [SOLVED] Invalid "format string" argument in Format fuction
    By jjk in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-14-2005, 05:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1