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.

--
Rick (MVP - Excel)


"ExcelMonkey" wrote in message
...
Now I get Error 53 File not found.

Thanks

EM

"Rick Rothstein" wrote:

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