asked on

VBA in MS Access Scripting.FileSystemObject Invalid procedure call error Runtime error 5

Hello Experts.

I have the following:

Set fso = CreateObject("Scripting.FileSystemObject")
fso.MoveFile "C:\Documents and Settings\pardoe2\My Documents\Blank Solution Tabs\Blank Solution Tabs Satyam All*.xls", _
"C:\Documents and Settings\pardoe2\My Documents\Blank Solution Tabs\Archive\Blank Solution Tabs Satyam All*.xls"
Set fso = Nothing

I am sure the error is coming from the use of the wildcard(*).

What is a work around? I do not know what the complete filenames will be because they will always have dates in them.

omgang

My thoughts

Dim fso As FileSystemObject
Dim fsoFolder As Folder
Dim fsoFile As File

Set fso = CreateObject("Scripting.FileSystemObject")
Set fsoFolder = fso.GetFolder("C:\Documents and Settings\pardoe2\My Documents\Blank Solution Tabs\")
For Each fsoFile In fsoFolder.Files
If fsoFile.Name Like "Blank Solution Tabs Satyam All*" & ".xls" Then
'move file command here
Next

I haven't tested this and have never actually done it but I think this is what you need to do to accomplish your task.
1) navigate to the folder
2) check the name of each file in the folder
3) if match is found (using the Like operator) than move that file

play around with it and see if it works.

OM Gang

ASKER CERTIFIED SOLUTION

DiegoAndresJAY

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rey Obrero (Capricorn1)

the fso sometimes gives unexpected results

i normally use this

dim xlFile, filePath
filePath="C:\Documents and Settings\pardoe2\My Documents\Blank Solution Tabs\"

xlFile=dir(filepath & "*.xls")
while xlFile<>""
if Inst(xlFile,"Blank Solution Tabs Satyam All")>0 then
filecopy filepath & xlFile, filePath & "\Archive\" & xlFile
'kill filecopy filepath & xlFile 'to delete the file
end if
xlFile=Dir
wend

eddiepardon

ASKER

Thanks!

omgang

Not for nothin here but the solution you accepted moves every file in the folder. You specifically asked for a method to move only xls files that were similar to a particular string value.

OM Gang