Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Error 5 on Shell Command
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 553
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default 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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,934
Default 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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default 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


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,934
Default 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



Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default 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




Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 553
Default 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
Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,934
Default 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

Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 553
Default 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


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2007 Shell Command Runtime Error HELP KJ MAN[_2_] Excel Programming 0 September 21st 08 07:53 PM
Shell Command JOHN Excel Programming 1 November 17th 04 10:39 AM
Shell command MAx Excel Programming 2 June 4th 04 04:11 PM
xp shell command using vba Sudhendra Excel Programming 2 February 16th 04 05:56 AM
SHELL command Robin Clay[_3_] Excel Programming 3 October 17th 03 02:50 PM


All times are GMT +1. The time now is 07:29 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement