Board index » Visual Studio » Shell Question

Visual Studio369
Hi,



I am using the shell command to print an excel spread sheet however I am

having a problem when the file name has a space in it. Here is my code:



Shell Excel.exe /p C:\issue log.xls



The error I receive is C:\Issue.xls could not be found. Is there a way to

deal with filenames with spaces?



Thanks


-

"Kyle" <Kyle@email.com>wrote in message

You need double quotes:

Here is Rick Rothstein's post from seven posting back...



The following is a compilation of several posts I've given in the past

regarding the Shell command. Your question is addressed in there; the

remainder is for your consideration.



Rick



You can use the Shell command. To execute internal DOS command (Dir,

Copy, etc. as well as redirection of screen output), the command

processor must be specified (using the Environ$ function and "comspec"

as its argument returns the correct command processor path on NT and

non-NT systems) . Specifying the command processor is safe & generic and

will work with non-internal commands also. That syntax, using an XCopy

command as an example is:



Shell Environ$("comspec") & " /c xcopy """ & _

Source & """ """ & Destination & """ " & Option, vbHide



You set the Source and Destination (string variables) to the appropriate

paths and the Option (string variable), if any, which can be found by

opening an MSDOS Prompt window and typing xcopy /?. (Note: You can type

/? after any DOS command at a DOS prompt to list the available options

for that command.) One more example would be to list all the files in a

directory including subdirectories and subdirectories of subdirectories

and all of their files.



CommandLine = "dir """ & FileSpec & _

""" /s/b>""" & RedirectTo & """"

Shell Environ$("comspec") & " /c " & CommandLine, vbHide



Here, the output of a Dir command is redirected to a file-path you

specify in the RedirectTo (string variable). The /s/b are options to the

Dir command that tell it to recurse throught its subdirectories and not

to include header or summary information.



I used a variable for the file name so that I could more easily explain

the benefit of encasing it in quotemarks. If you redirect to a file that

has spaces in its name, or if there are spaces in the path specification

itself, then the filename *must* be quoted to protect the spaces from

DOS's desire to use them as delimiters. (That's what all those

quotemarks in the Shell statement are for.) If the filename doesn't have

spaces in it, the quotes aren't necessary BUT they don't hurt either.

Hence, the above will work with either.



As for your PING question, something like the following should work:



strIP = "4.17.23.1"

Shell Environ$("comspec") & " /c ping " & _

strIP & ">""" & RedirectFile & """", vbHide



Although you didn't specify it in your original post, I assume you want

to use vbHide for the optional 2nd parameter to Shell. This hides the

DOS window so that your user doesn't see it. If you want the DOS window

to remain visible, you would use the vbNormalFocus BUT you must use a /k

instead of a /c for the command processor argument. Basically, the /c

tells the command processor "here comes a command and, when its finished

executing, close the DOS shell it is running in" whereas the /k also

tells the command processor that a command follows, but it instructs it

to leave the DOS session running.



The above assumes you do NOT have to wait for this file to be completely

written before your code continues executing. If you have to work with

this file right after it is created, consider one of these (which makes

your program wait until the DOS process is finished):



MICROSOFT 'S OFFICIAL WAY

========================

See this link



support.microsoft.com/support/kb/articles/Q129/7/96.asp">support.microsoft.com/support/kb/articles/Q129/7/96.asp



Note: This method doesn't use Shell -- it uses CreateProcessA.





FAST AND DIRTY METHOD (WORKS ALMOST ALL THE TIME)

=================================================

Paste these lines in the (General)(Declarations) section of the form

where the Shell is being called (or remove the Private keywords and put

them in a BAS module if more than one form will use them):



Private Declare Function OpenProcess _

Lib "kernel32" _

(ByVal dwDesiredAccess As Long, _

ByVal bInheritHandle As Long, _

ByVal dwProcessId As Long) As Long

Private Declare Function CloseHandle _

Lib "kernel32" _

(ByVal hObject As Long) As Long

Private Declare Function WaitForSingleObject _

Lib "kernel32" _

(ByVal hHandle As Long, _

ByVal dwMilliseconds As Long) As Long



Call your Shell command in this form with the appropriate Shell

arguments placed in the parentheses:



PID = Shell( <<Put Shell Arguments Here>>)



And finally, paste the following IMMEDIATELY after the PID=Shell

statement above (making sure to handle the possible error where

indicated; i.e. stop the code from falling through to your other

commands if the Shell failed):



If PID = 0 Then

'

'Handle Error, Shell Didn't Work

'

Else

hProcess = OpenProcess(&H100000, True, PID)

WaitForSingleObject hProcess, -1

CloseHandle hProcess

End If







-

Enclose the file name in quotes.



--

Chris Hanscom - Microsoft MVP (VB)

Veign's Resource Center

www.veign.com/vrc_main.asp">www.veign.com/vrc_main.asp

--





"Kyle" <Kyle@email.com>wrote in message



-

Thanks for the post. I am going to use shellexecute api instead of the

shell command which brings up a question. I have a mssql db table that

holds a line commands for a bunch of different file extensions. I was using

shell for all of the file extensions but now I am using shell execute for

some of them. Is there a way I can store the command string for

shellexecute (example: 1, "Print", strLocalFileName, "", "", 1)?



Thanks



"Veign" <NOSPAMinveign@veign.com>wrote in message



-

"Kyle" <Kyle@email.com>wrote in message



Personally, I would use Excel Automation rather than shell to Excel.



Dim oXLApp As Object

Dim oXLWorkbook As Object

Dim sFileName As String



sFileName = "H:\My Documents\Office Documents\Excel Files\My Budget.xls"

Set oXLApp = CreateObject("Excel.Application")

Set oXLWorkbook = oXLApp.Workbooks.Open(sFileName)

oXLWorkbook.PrintOut

oXLApp.DisplayAlerts = False

oXLApp.Quit



One reason is that you can control all aspects of printing, if you need to.

For example, you could specify certain pages to print, or only print one

worksheet of a workbook, etc. Also, in order to ensure Shell will work, you

really should get the path where the excel.exe file is located. You don't

need to worry about this if you use Automation.



--

Mike

Microsoft MVP Visual Basic







-

Instead of storing the complete command string just store the parts like

Operation, File, WindowState.



--

Chris Hanscom - Microsoft MVP (VB)

Veign's Resource Center

www.veign.com/vrc_main.asp">www.veign.com/vrc_main.asp

--





"Kyle" <Kyle@email.com>wrote in message



-

On Tue, 6 Sep 2005 15:19:19 -0600, "Kyle" <Kyle@email.com>wrote:



Ahem



I think the smart money is on using FindExecutable

- and then using Shell to launch it



The more stages you control yourself, the more chances you have of

easily trapping when something has gone wrong.





-