troubleshooting Question

Runtime error 5 for strFile = Dir$()

Avatar of peter_chong
peter_chong asked on
Microsoft Access
9 Comments1 Solution2164 ViewsLast Modified:
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$()

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

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

End Function

Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 9 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros