Shell command in Excel 2003 VBA question 2

[mscallisto]

I suspect the following shell commands are not working.

When the message box pops up I click in the desktop and refresh the screen with F5 and still see the files I thought I deleted.

How can I monitor whats going on with the shell results e.g. "file not found" etc.?

    Shell "command.com /c cd C:\Documents and Settings\smills\Desktop\"
    Shell "command.com /c del Pipey.txt"
    Shell "command.com /c del Log.xls"
    MsgBox ("Deleted")

 

[jrbarnett]

Rather than debug the command.com calls I'd suggest replacing them with the VBA equivalent calls which should be faster:

If Dir ("C:\Documents And Settings\smills\Desktop\Pipey.txt") = "Pipey.txt" Then
Kill "C:\Documents And Settings\Smills\desktop\Pipey.txt"
If Dir ("C:\Documents And Settings\smills\Desktop\Log.xls") = "Log.xls" Then
Kill "C:\Documents And Settings\Smills\desktop\Log.xls"
MsgBox "Deleted"
End If

With your method, each individual shell command will have its own environment variable, current folder etc, so these will reset with each call.

John

 

[mscallisto]

Thanks John

I wasn't aware that I could do that.
I found MacID and RmDir in the VBA Help and wondered if other DOS commands like CD change directory were available as VBA equivalent calls.

BTW it works great with an additional End If

sam

 

[PHV]

if other DOS commands like CD change directory were available as VBA
Have a look at ChDir

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jrbarnett]

Name - to rename a file
CurDir - Return current directory
Dir - to retrieve lists of files/folders
MkDir - to create a folder
RmDir to delete a folder
Kill - Delete a file

You may also like to search for information on Scripting.FileSystemObject for examples of the more modern capabilities.

John

 

[mscallisto]

Thanks Guys everything works fine with your help but I must read up on the shell function.

I thought that Shell "command.com /c command" would act like I was in a DOS window and perform the command, like

dir > file.txt etc.

 

[FaneDuru]

In fact Shell acts like in DOS window (for command.com)...
The problem looks to be the Path: DOS does not know about names with spaces so "Documents and Settings" is not a valid name... If you try an other valid path I think it will work.

Fane Duru

 

[mscallisto]

Oh Yes I remember many years back (just after "long Names" came into existance) I had to say cd \docume~1 to adhere to the 8 character format but I thought that was no longer needed???

I tried:
Shell "command.com /c cd C:\Docume~1\sam\Deskto~1\"
and it didn't work

I'll stick with jrbarnett's method for now