Error 5 on Shell Command

[ExcelMonkey]

I am getting an "Error 5 Invalid procedure call or argument" on the following:

lStr_Dir = ThisWorkbook.Path
strDirectoryList = lStr_Dir & "\Directory"
Shell (strDirectoryList & ".bat"), vbHide '', vbMinimizedNoFocus

?strDirectoryList
\\CGAS114\Username\My Documents\The Folder\ExcelVBA\Directory

Is this due to the spaces in the path?

Thanks

EM

 

[joel]

the shell doesn't understand command line instructions or bat files. you
have to use cnd.exe to execute the bat files.

Shell ("c:\windows\cmd.exe " & strDirectoryList & ".bat"), vbHide '',
vbMinimizedNoFocus

 

[Rick Rothstein]

It might be better to let VB select the user's command processor rather than
hard coding it (not all past versions of Windows used cmd.exe)...

Shell Environ("COMSPEC") & " " & strDirectoryList & ".bat", vbHide

 

[joel]

Rick: I believe windows 3.1 used "command.com". Starting in Win95 there was
cmd.exe

 

[Rick Rothstein]

You might be right... I don't really remember. However, I'm not sure whether
the Windows operating system always has to be installed to a directory named
Windows or whether the user can modify the install directory. If the user
can modify it, your hard coded command processor path would also fail. Also,
will future Windows operating systems always use cmd.exe as the command
processor? Or, will 64-bit Windows operating systems use System32 to store
the command processor? I still think it is best to let VB obtain the command
processor and its path rather than hard coding it in.

 

[joel]

I knew the diffference was betwen 16bit and 32 bit but couldn't remember
which operating system was which

16 bit - DOS, Win 3.1, Win95, Win98
32 bit - Win 2000, NT, ME, XP
64 bit - Vista

 

[ExcelMonkey]

The code I am using is as follows from the website:
http://www.bygsoftware.com/Excel/VBA/ftp.htm

When I put your "c:\windows\cmd.exe" line in the Shell command I get an
Error 76 Pat not found. Will need to test on an FTP site. You will have to
change the variables below: ftpsiteaddres, username, password. It would be
helpful if we could use a public FTP site for this example.

Sub PublishFile()
Dim strDirectoryList As String
Dim lStr_Dir As String
Dim lInt_FreeFile01 As Integer
Dim lInt_FreeFile02 As Integer

On Error GoTo Err_Handler
lStr_Dir = ThisWorkbook.Path
lInt_FreeFile01 = FreeFile
lInt_FreeFile02 = FreeFile

'' ANW 07-Feb-2003 :
strDirectoryList = lStr_Dir & "\Directory"

'' Delete completion file
If Dir(strDirectoryList & ".out") <> "" Then Kill (strDirectoryList &
".out")

'' Create text file with FTP commands
Open strDirectoryList & ".txt" For Output As #lInt_FreeFile01
Print #lInt_FreeFile01, "ftpsiteaddress"
Print #lInt_FreeFile01, "username"
Print #lInt_FreeFile01, "password"
Print #lInt_FreeFile01, "cd source/uploads"
Print #lInt_FreeFile01, "binary"
'Print #lInt_FreeFile01, "send " & ThisWorkbook.Path & "\FTP Test
File.xlsm targetdir/FTP Test File.xlsm"

'' To receive a file, replace the above line with this one
Print #lInt_FreeFile01, "recv \ImageCombo.xls " & ThisWorkbook.Path &
"\ImageCombo.xls"

Print #lInt_FreeFile01, "bye"
Close #lInt_FreeFile01

'' Create Batch program
Open strDirectoryList & ".bat" For Output As #lInt_FreeFile02
Print #lInt_FreeFile02, "ftp -s:" & strDirectoryList & ".txt"

Print #lInt_FreeFile02, "Echo ""Complete"" > " & strDirectoryList & ".out"
Close #lInt_FreeFile02

'' Invoke Directory List generator
Shell ("c:\windows\cmd.exe" & strDirectoryList & ".bat"), vbHide '',
vbMinimizedNoFocus
'Wait for completion
Do While Dir(strDirectoryList & ".out") = ""
DoEvents
Loop

Application.Wait (Now + TimeValue("0:00:03"))

'' Clean up files
If Dir(strDirectoryList & ".bat") <> "" Then Kill (strDirectoryList &
".bat")
If Dir(strDirectoryList & ".out") <> "" Then Kill (strDirectoryList &
".out")
If Dir(strDirectoryList & ".txt") <> "" Then Kill (strDirectoryList &
".txt")

bye:

Exit Sub

Err_Handler:
MsgBox "Error : " & Err.Number & vbCrLf & "Description : " &
Err.Description, vbCritical
Resume bye

End Sub

 

[Rick Rothstein]

You need a space between the "c:\windows\cmd.exe" part and the
strDirectoryList that you concatenated to it. Just put a space after the
"exe".

 

[ExcelMonkey]

Now I get Error 53 File not found.

Thanks

EM

You need a space between the "c:\windows\cmd.exe" part and the
strDirectoryList that you concatenated to it. Just put a space after the
"exe".

 

[Rick Rothstein]

That probably means there is something wrong with the path and/or file name,
as constructed, that is stored in the strDirectoryList variable. Either the
entire path is not complete or some backslashes are missing from the
constructed path or the bat file doesn't really exist at the path you are
looking at. Try MessageBox'ing the strDirectoryList variable's content just
before you attempt to use it in the Shell command and see if that path
and/or file name is properly constructed.

 

[ExcelMonkey]

To be clear the path I am creating in is:
\\CGAS114\username\My Documents\Personal Folder\ExcelVBA\

I can confirm that the strDirectoryList is:
\\CGAS114\username\My Documents\Personal Folder\ExcelVBA\Directory

I can also confirm that there are two files in the directory:
Directory.txt and Directory.bat

Thanks

EM

 

[Rick Rothstein]

The only other thing I can think of, and I don't know for sure, is that
perhaps the Shell function doesn't support direct network addresses (I can't
check as I'm retired without access to a network). Do you have your network
address mapped to a drive letter? If so, strictly as a test, try putting in
a break point right before the Shell function call and replace the network
address in strDirectoryList with the mapped drive letter (then Run the
remainder of the code) and see if that works. If it does work, then that
would mean Shell doesn't support network addresses, which would mean you
will have to add code to substitute the drive letter reference for the
network address.

 

[ExcelMonkey]

The path that I am using is clearly a network path. "My documents" is not
actually on my computer (i.e not on my C: drive).

So I replaced all references to ThisWorkbook.Path to a my desk top path
which is on my C: Drive.

I still get the Error 53 message.

Regards

RK