shell command with VBA

I'm creating a new macro. So far, I have some code and it works. Then I have a shell command to run a macro in an Access database. Once the macro is completed, I want to run some more code to extract and format the results of the Access macro.

Everything is working upto the and including the Shell command. However, I can't get the code to execute after the shell command.

Can I use a shell command in the middle of vba code? This is the first time I have tried this.

Any help would be greatly appreciated. Thanks for the help.....

-------------Problems Reply------------

Have a look at this side for the function ShellX:
http://vb-tec.de/xshell.htm

The code is 90% identical to Ricks, in addition, it provides the exit code of the called program.

Andreas.

You can use the shell command. In order to get your VBA code to wait for the shell command to complete, try something like the suggestion offered in the link below.

HTH,

Eric

http://stackoverflow.com/questions/5968356/how-to-programmatically-wait-for-shell-command-to-finish-running

Here is some material I have posted in the past regarding having VB code wait for a Shell command to finish...

MICROSOFT'S OFFICIAL WAY
========================
See this link...

http://support.microsoft.com/kb/129796

Note: The above method does not use Shell -- it uses CreateProcessA.

FAST AND DIRTY METHOD
======================
Paste these lines into a Standard Module:

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 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

It occurs to me that it is probably unnecessary to use Shell. Why not set a reference to Access in Excel VBA and run the Access macro directly from Excel?

Have a look at this side for the function ShellX:
http://vb-tec.de/xshell.htm

The code is 90% identical to Ricks, in addition, it provides the exit code of the called program.

Andreas.

Category:Office Views:0 Time:2013-09-10

Related post

  • How can I execute a shell command using VBA? 2010-02-18

    I want to execute a shell command like the following using Visual Basic for Applications. C:\Temp\gc.exe 1 How can I do it? --------------Solutions------------- Example: retVal = Shell("C:\Temp\gc.exe 1", vbNormalFocus) Link: Shell Invoked from VBA T

  • How do you execute a shell command in vba that uses stdio and stdout? 2011-06-15

    In visual studio I want a macro that takes the selected text, pipes it to a dos command, captures the resulting stream and replaces the selected text with it. Something like this... Dim objSel As TextSelection = DTE.ActiveDocument.Selection objSel.Te

  • Capture output value from a shell command in VBA? 2010-05-06

    Found this function http://www.cpearson.com/excel/ShellAndWait.aspx But would need also to capture output from the shell output. Any code suggestion ? --------------Solutions------------- You can CreateProcess the application redirecting its StdOut t

  • Using AppActivate and Sendkeys in VBA shell command 2011-01-12

    I want to switch back and forth between application with the shell command in VBA. I am using SendKeys to do stuff in process A then move to process B, then to process A then process B. It works fine for the first iteration. When I use AppActivate to

  • Running a batch file using the Shell Command Access 2010 vba 2014-08-17

    Hi. I have a simple batch file that deletes the current backup copy of the be of the database and then makes a new backup copy of the back end file of the database. The batch file runs perfectly when run from windows explorer. The front end, back end

  • How to call TextEdit using the shell command in a VBA project 2015-02-13

    Original Title: "Text Editor" I need to call Mac's Text editor ("TextEdit") using the shell command in a VBA project. I was hoping it would be as simple as: Shell("TextEdit (mytextfile)") but there is apparently more to it in Apple. --------------Sol

  • Inability to use "POSIX path" when passing a Applescript command via VBA's "MacScript" function 2010-12-15

    I'm porting some VBA from Excel 2003 (Windows XP) to Excel 2004 (OS X). Excel 2004 contains a VBA interpreter that allows you to fob off some commands onto Applescript using the MacScript() function. This is all well and good for what I want to do (w

  • Executing shell command from MySQL 2008-11-14

    I know what I'm looking for is probably a security hole, but since I managed to do it in Oracle and SQL Server, I'll give it a shot: I'm looking for a way to execute a shell command from a SQL script on MySQL. It is possible to create and use a new s

  • How do I call a Windows shell command using VB6? 2008-11-14

    How exactly using VB6 can I can call any Windows shell command as you would from the command-line? For example, something as trivial as: echo foo --------------Solutions------------- Here's how you do it : Shell "cmd echo foo", vbNormalFocus Shell an

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.074 (s). 11 q(s)