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?
 
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
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
xyz = "ren  """ & sPath & "\*.csv"" """ & sPath & "\*.old"""
debug.print xyz   '<--- What does this display in your Immediate Window?
0
 
jitgangulyAuthor Commented:
Ren "\\rabofspr\global\funds xfer\Chase\*.csv" "\\rabofspr\global\funds xfer\Chase\*.old"
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
0
 
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
0
 
jitgangulyAuthor Commented:
Boy,
Can I have a simple code that would rename two files. I would prefer using wildcards. Both are .csv extension
0
 
Leo EikelmanDirector, IT and Business DevelopmentCommented:
you can use wildcards.

Look at my above comment.

This code isn't that complicated

Leo
0
 
jitgangulyAuthor Commented:
Why can't I use
1. Shell
2. FSO
0
 
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 :-)
0
 
Leo EikelmanDirector, IT and Business DevelopmentCommented:
yeah I overlooked changing the parameters.  

Glad it's working

Cheers,

Leo
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.