Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to run a shell command via VBA?
I want to run shell commands via VBA?
particularly this one: Which adds a printer default CSCRIPT %windir%\system32\prnmngr.vbs -ac -p "\\rds1\Printer088" |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to run a shell command via VBA?
Use the SHELL command. In the VBE, type in the word SHELL, move the cursor
onto the word, then hit the F1 key to get the Help documentation for it. For your situation (and a nod to Steve Yandl for the idea) I think this may work: .... Shell "CSCRIPT %windir%\system32\prnmngr.vbs -ac -p" & Chr(34) & "\\rds1\Printer088" & Chr(34) .... The Chr(34) is for placing the quotes around the printer location. -- Toby Erkson Excel 2003, WinXP "Benjamin" wrote in message ... I want to run shell commands via VBA? particularly this one: Which adds a printer default CSCRIPT %windir%\system32\prnmngr.vbs -ac -p "\\rds1\Printer088" |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to run a shell command via VBA?
As a follow up... the following is a compilation of several posts I've given
in the past regarding the Shell command (over in the compiled VB newsgroups, but applicable to the VBA world) which may prove useful. -- Rick (MVP - Excel) 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 http://support.microsoft.com/support.../Q129/7/96.asp Note: This method doesn't use Shell -- it uses CreateProcessA. FAST AND DIRTY METHOD ====================== 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 "Just Another Yahoo!" wrote in message ... Use the SHELL command. In the VBE, type in the word SHELL, move the cursor onto the word, then hit the F1 key to get the Help documentation for it. For your situation (and a nod to Steve Yandl for the idea) I think this may work: ... Shell "CSCRIPT %windir%\system32\prnmngr.vbs -ac -p" & Chr(34) & "\\rds1\Printer088" & Chr(34) ... The Chr(34) is for placing the quotes around the printer location. -- Toby Erkson Excel 2003, WinXP "Benjamin" wrote in message ... I want to run shell commands via VBA? particularly this one: Which adds a printer default CSCRIPT %windir%\system32\prnmngr.vbs -ac -p "\\rds1\Printer088" |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to run a shell command via VBA?
Untested. (I don't have that printer or that script.)
dim myFileName as string dim myParms as string myfilename = "Cscript" myparms = "%windir%\system32\prnmngr.vbs -ac -p ""\\rds1\Printer088"" Shell Environ("comspec") _ & " /k " & Chr(34) & myFileName & Chr(34) & " " & myparms, vbNormalFocus Use /c says to close that (hidden!) window when it's done. The /k to see the command window (/k = keep open). And when you're done testing, you may want vbHide so the window isn't noticeable. (See VBA's help for Shell to see the options.) If this doesn't work (or some variation), you may want to include the version of windows that you're running. I'm not sure all still support Shell. Benjamin wrote: I want to run shell commands via VBA? particularly this one: Which adds a printer default CSCRIPT %windir%\system32\prnmngr.vbs -ac -p "\\rds1\Printer088" -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to run a shell command via VBA?
Dang Rick, nice! I'm saving this to a .txt file for future reference.
-- Toby Erkson Excel 2003, WinXP "Rick Rothstein" wrote in message ... As a follow up... the following is a compilation of several posts I've given in the past regarding the Shell command (over in the compiled VB newsgroups, but applicable to the VBA world) which may prove useful. -- Rick (MVP - Excel) 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.) ... |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to run a shell command via VBA?
I am new to the forum and I agree , this is a great post. I do have a related question. I use vba to open an SSH tunnel using plink.exe the code I use is: Shell Environ$("COMSPEC") & " /k c:\plink -ssh -l 'username' -pw 'password' -4 -L 3306:localhost:'IP address' """ I run it using the 'on open' function for the switchboard form in Access. i can then access mysql tables from access. THE QUESTION (finally !!) Is there a way I can close the shell window using VBA at some later point (when I close access)? It would be great if it would type "EXIT" first , but I suspect that is not essential. Thanks!! Rick Rothstein;503270 Wrote: As a follow up... the following is a compilation of several posts I've given in the past regarding the Shell command (over in the compiled VB newsgroups, but applicable to the VBA world) which may prove useful. -- juanpedro ------------------------------------------------------------------------ juanpedro's Profile: http://www.thecodecage.com/forumz/member.php?u=2333 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=138407 http://www.thecodecage.com/forumz --- news://freenews.netfront.net/ - complaints: --- |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to run a shell command via VBA?
On 8/17/2010 6:13 PM, juanpedro wrote:
I am new to the forum and I agree , this is a great post. I do have a related question. I use vba to open an SSH tunnel using plink.exe the code I use is: Shell Environ$("COMSPEC")& " /k c:\plink -ssh -l 'username' -pw 'password' -4 -L 3306:localhost:'IP address' """ I run it using the 'on open' function for the switchboard form in Access. i can then access mysql tables from access. THE QUESTION (finally !!) Is there a way I can close the shell window using VBA at some later point (when I close access)? It would be great if it would type "EXIT" first , but I suspect that is not essential. Thanks!! Rick Rothstein;503270 Wrote: As a follow up... the following is a compilation of several posts I've given in the past regarding the Shell command (over in the compiled VB newsgroups, but applicable to the VBA world) which may prove useful. If VBA works similarly enough to VB6 you might be able to use a combination of these two posts to get the handle of the window when you first create it, then send it a message to close it: http://www.vb-helper.com/howto_shell_get_hwnd.html Dim task_id As Variant, task_hWnd As Long task_id = Shell(Shell Environ$("COMSPEC")& " /k c:\plink -ssh -l 'username' -pw 'password' -4 -L 3306:localhost:'IP address' """) task_hWnd = InstanceToWnd(task_id) Private Function InstanceToWnd(ByVal target_pid As Long) As Long ' Return window handle for an instance handle Dim test_hwnd As Long, test_pid As Long, test_thread_id As Long ' Get the first window handle test_hwnd = FindWindow() ' Loop until we find the target or we run out of windows. Do While test_hwnd < 0 'See if window has a parent. If not it's a top-level window If GetParent(test_hwnd) = 0 Then 'This is a top-level window. 'See if it has the target instance handle. test_thread_id = _ GetWindowThreadProcessId(test_hwnd, test_pid) If test_pid = target_pid Then 'This is the target InstanceToWnd = test_hwnd Exit Do End If End If ' Examine the next window test_hwnd = GetWindow(test_hwnd, GW_HWNDNEXT) Loop End Function ''-------------------------------------------------------- http://www.tek-tips.com/faqs.cfm?fid=6073 Private Const WM_CLOSE = &H10 Private Declare Function PostMessage Lib "user32" _ Alias "PostMessageA" _ (ByVal hwnd As Long, _ ByVal wMsg As Long, _ ByVal wParam As Long, _ lParam As Any) As Long 'Post a message to the window to close itself 'using the handle found with above code section PostMessage task_hWnd, WM_CLOSE, 0&, 0& ''-------------------------------------------------------- Will that work? There is a note about Access VBA but nothing about Excel: "Note that in VBA with Access 2002, Shell() no longer returns a task handle as described in examples and the Access 2002 documentation, but rather returns True or False. Go figure." Mike |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to run a shell command via VBA?
juanpedro;740349 Wrote: I am new to the forum and I agree , this is a great post. I do have a related question. I use vba to open an SSH tunnel using plink.exe the code I use is: Shell Environ$("COMSPEC") & " /k c:\plink -ssh -l 'username' -pw 'password' -4 -L 3306:localhost:'IP address' """ I run it using the 'on open' function for the switchboard form in Access. i can then access mysql tables from access. THE QUESTION (finally !!) Is there a way I can close the shell window using VBA at some later point (when I close access)? It would be great if it would type "EXIT" first , but I suspect that is not essential. Thanks!! Rick Rothstein;503270 Wrote: As a follow up... the following is a compilation of several posts I've given in the past regarding the Shell command (over in the compiled VB newsgroups, but applicable to the VBA world) which may prove useful. Take a look at this code snippet in our upload/download section 'Code Cage Downloads - The Code Cage Forums' (http://www.thecodecage.com/forumz/lo...id=2&linkid=23) -- Simon Lloyd Regards, Simon Lloyd 'Microsoft Office Help' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?u=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=138407 http://www.thecodecage.com/forumz --- news://freenews.netfront.net/ - complaints: --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Shell Command | Excel Programming | |||
Shell Command | Excel Programming | |||
Shell command | Excel Programming | |||
xp shell command using vba | Excel Programming | |||
SHELL command | Excel Programming |