Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1560
  • Last Modified:

Runtime error 5 for strFile = Dir$()

Dear EE member,
I face runtime error 5: Invalid procedure call or argument in
strFile = Dir$() at the following code:

Function rename()
'File processing
'Purpose: Rename and Move file to dummybaby folder.
Dim strFile As String
Dim strPath As String
Dim strInventory As String
Dim strReserve As String
Dim strOldName As String

strPath = "C:\dummy\*.CSV"
strFile = Dir$(strPath)
strPath = "C:\dummy\"
strInventory = "C:\dummybaby\inventory.csv"
strReserve = "C:\dummybaby\reserveStock.csv"

Do While Len(strFile) > 0
    strOldName = strPath & strFile
    Select Case True
   
    Case UCase$(Left$(strFile, 8)) = "OVERVIEW"
    FileRename strOldName, strInventory, True
       
    Case UCase$(Left$(strFile, 5)) = "QUERY"
    FileRename strOldName, strReserve, True
   
    End Select

    strFile = Dir$()

Loop
End Function

Function FileRename(sOriginalName As String, sNewName As String, Optional bOverWrite As Boolean) As Boolean
On Error GoTo ErrFailed
If Len(Dir$(sOriginalName)) > 0 And Len(sOriginalName) > 0 Then
If bOverWrite = True And Len(Dir(sNewName)) > 0 Then
    'Delete file with same name as new file.
    VBA.Kill sNewName
End If
Name sOriginalName As sNewName
FileRename = True
End If

Exit Function

ErrFailed:
'Failed to rename file
FileRename = False
On Error GoTo 0

End Function


0
peter_chong
Asked:
peter_chong
  • 5
  • 3
1 Solution
 
mbizupCommented:
Try this:

strFile = Dir(strPath)
0
 
mbizupCommented:
Although testing this, Dir$ should work too.  
Check your references, from the VBA Editor:

Tools -> References

Make sure none are labeled "MISSING".
0
 
jerryb30Commented:
Does this have something to do with using strPath in one context, and then changing it?
try changing
strFile = dir$("C:\dummy\*.CSV")
and forget strPath = "C:\dummy\*.CSV"
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
peter_chongAuthor Commented:
Dear mbizup,

You are right! Please explain how it work and not
strFile = Dir$() ?

Regards,
Peter
0
 
peter_chongAuthor Commented:
point add.
0
 
mbizupCommented:
>Please explain how it work and not
Actually, I think it's a fluke that it did work with Dir().  Both Dir and Dir$ are valid functions, and the syntax you are using in your functions is correct for either one.  

I'm guessing that it worked with Dir() because at the time you ran the function, The Dir$ in the second function -- FileRename found an existing file that needed to be overwritten.  The Dir$(sOriginalName) in the second function changes the starting point of the search when dir() is called with no parameters.  When the program flow returns to the first function, the Dir() is looking for the next sOriginalName (instead of the next strPath).  If there had been no file to overwrite, the Dir() in the function will return an error (that is why stopping the loop when len(strFile) = 0 is so important).

(Try deleting the files in the dummybaby folder, and I think you will get that error with both Dir and Dir$).

To resolve this problem, rewrite the second function like this (without using Dir or Dir$):


Function FileRename(sOriginalName As String, sNewName As String, Optional bOverWrite As Boolean) As Boolean
On Error GoTo ErrFailed
Dim fso As New FileSystemObject

fso.CopyFile sOriginalName, sNewName, True
Kill sOriginalName

Exit Function

ErrFailed:
'Failed to rename file
FileRename = False
On Error GoTo 0

End Function
0
 
mbizupCommented:
Check your references (from the vba editor: tools -> references), and make sure that you have Microsoft Scripting Runtime checked.

With the above modification to FileRename, either Dir or Dir$ should work.

btw, Dir and Dir$ are completely equivalent.  I think Dir$ is an older syntax, and is not included in the VBA help file.

The $ sign makes a very slight differencein the string manipulation functions:
Left, Left$
Right, Right$
Mid, Mid$
etc.
The functions without the $ return Variant data; the functions with the $ return string data.
The same is not true with Dir and Dir$, where both return strings.
0
 
peter_chongAuthor Commented:
bonus.
0
 
mbizupCommented:
Glad to help :-)
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now