File not found with Shell Command

This is the code

xyz = "ren  """ & sPath & "\*.csv"" """ & sPath & "\*.old"""
myval = Shell(xyz)

Gettign file not found. When I run it from DOS, it works fine
Whats wrong ?

LVL 20
jitgangulyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
xyz = "ren  """ & sPath & "\*.csv"" """ & sPath & "\*.old"""
debug.print xyz   '<--- What does this display in your Immediate Window?
jitgangulyAuthor Commented:
Ren "\\rabofspr\global\funds xfer\Chase\*.csv" "\\rabofspr\global\funds xfer\Chase\*.old"
Leo EikelmanDirector, IT and Business DevelopmentCommented:
instead of the Shell function use ShellWait

Here is a code Example

------------------------------------------------------------------------------------------


Const STILL_ACTIVE = &H103
Const PROCESS_QUERY_INFORMATION = &H400

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Private Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long
Private Declare Function GetExitCodeProcess Lib "kernel32" (ByVal hProcess As Long, lpExitCode As Long) As Long

Private Function ShellWait(PathName, Optional WindowStyle As VbAppWinStyle = vbHide) As Double
Dim H As Long, R As Long
    H = OpenProcess(PROCESS_QUERY_INFORMATION, False, Shell(PathName, WindowStyle))
    Do
        GetExitCodeProcess H, R
        DoEvents: Sleep 100
    Loop While R = STILL_ACTIVE
End Function

Private Sub Form_Load()
Dim q As String
q = "dsquery user -name > c:\query.txt"
ShellWait "cmd /c " & q, vbHide

'open file for input etc...here
End Sub

---------------------------------------------------------------------------------


Cheers,

Leo
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Leo EikelmanDirector, IT and Business DevelopmentCommented:
above replace

q = "dsquery user -name > c:\query.txt"

with your cmd you are executing

q = "ren  """ & sPath & "\*.csv"" """ & sPath & "\*.old"""

Cheers,

Leo
jitgangulyAuthor Commented:
Boy,
Can I have a simple code that would rename two files. I would prefer using wildcards. Both are .csv extension
Leo EikelmanDirector, IT and Business DevelopmentCommented:
you can use wildcards.

Look at my above comment.

This code isn't that complicated

Leo
jitgangulyAuthor Commented:
Why can't I use
1. Shell
2. FSO
Leo EikelmanDirector, IT and Business DevelopmentCommented:
What about instead of this

xyz = "ren  """ & sPath & "\*.csv"" """ & sPath & "\*.old"""
myval = Shell(xyz)

try this

myval = Shell "cmd /c ren  """ & sPath & "\*.csv"" """ & sPath & "\*.old"""

cheers,

Leo

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jitgangulyAuthor Commented:
You are the man

Of course I had to do little modification, as the 2nd parameter in ren does not require full path

xyz = "cmd /c ren  """ & sPath & "\*.csv"" *.old"

I always give 'A' (since I am expert too :-)
Leo EikelmanDirector, IT and Business DevelopmentCommented:
yeah I overlooked changing the parameters.  

Glad it's working

Cheers,

Leo
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.