Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 5 on Shell Command
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 5 on Shell Command
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 5 on Shell Command
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 -- Rick (MVP - Excel) "joel" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 5 on Shell Command
Rick: I believe windows 3.1 used "command.com". Starting in Win95 there was
cmd.exe "Rick Rothstein" wrote: 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 -- Rick (MVP - Excel) "joel" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 5 on Shell Command
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. -- Rick (MVP - Excel) "joel" wrote in message ... Rick: I believe windows 3.1 used "command.com". Starting in Win95 there was cmd.exe "Rick Rothstein" wrote: 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 -- Rick (MVP - Excel) "joel" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 5 on Shell Command
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 "Rick Rothstein" wrote: 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. -- Rick (MVP - Excel) "joel" wrote in message ... Rick: I believe windows 3.1 used "command.com". Starting in Win95 there was cmd.exe "Rick Rothstein" wrote: 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 -- Rick (MVP - Excel) "joel" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 5 on Shell Command
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 |
#8
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 5 on Shell Command
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 5 on Shell Command
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 5 on Shell Command
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" wrote: 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 5 on Shell Command
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. -- Rick (MVP - Excel) "ExcelMonkey" wrote in message ... 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" wrote: 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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 5 on Shell Command
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 "Rick Rothstein" wrote: 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. -- Rick (MVP - Excel) "ExcelMonkey" wrote in message ... 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 Shell Command Runtime Error HELP | Excel Programming | |||
Shell Command | Excel Programming | |||
Shell command | Excel Programming | |||
xp shell command using vba | Excel Programming | |||
SHELL command | Excel Programming |