Posted to microsoft.public.excel.programming
|
|
Error 5 on Shell Command
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 (MVP - Excel)
"ExcelMonkey" wrote in message
...
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
"joel" wrote:
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
"ExcelMonkey" wrote:
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
|