Free Microsoft Excel 2013 Quick Reference

VBA Shell command

Hi Guys,

I had a problem using the shell command in VBA in Excel 2007.

I was trying to run the shell command in VBA (excel2007) on a .BAT(batch) file. While running the macro, the DOS command prompt appears and goes away in a flash but the function of the .bat is not performed which makes me think that the shell command isnt functioning properly. I had this proper earlier also with a few Exe's.

the line of the macro that calls is (c is the path for the batch file) :

Call Shell(c, vbNormalFocus)

Could any of you help me with some suggestions regarding this problem.

Just to confirm, the batch file on itself works absolutely fine and it looks like it doesnt take more than 2 seconds to run it. It also does not wait for any input from the macro. it is an individual batch just included in the macro.

Thank you.


The VBA shell command is as follows:

programPath = "C:Program FilesInternet Exploreriexplore.exe" ' works
'programPath = "iexplore.exe" ' does not work
Shell programPath + " " + fileToLaunch, vbNormalFocus

but the drawback is that the invoked program (iexplore.exe, at least in my case) needs to have the FULL PATH to where the program exists = the "C:Program FilesInternet Explorer" which may or may not work on someone elses computer. This hardcoding will not work and is not transportable.

Is there a trick to find where the executing program lives? or launching it without the path?

Thanks,

Hi Everyone

I want to kill windows xp process tree for a particular
process using VBA Shell Command.

Detail Description: I am using code to produce PDF's and
due to come reason my memory is blocking and the printing
stops saying that "server threw an exception", with some
R&D I figured that Acrobat Process in Windows XP Task
Manager if exceeds 100,000K the acrobat is not able to
print Pdf's.

Can anyone used/seen any code to kill the process tree
for a particular process.(I know that i need to use the
Shell commands but never used one)

Please help me in writing the code .... thanks inadvance

HI there,
First post (I defected from Excel-L!) - hello all.

I'm opening another application (Tableau) from within Excel, connecting Tableau directly to the current file. I am building the following string to use in the Shell command:
When I run this from the command line, Tableau opens maximized. However, when I run the same code through a VBA Shell
command, Tableau opens in a minimised state:
    ' construct the command line and execute it
    strCmdLine = Replace(strTableau, "%1", ActiveWorkbook.FullName)
    Shell (strCmdLine & " /max")
Any ideas how I can force the other app to be maximised?

Thanks

Hi,

I'm trying to run a perl script from a macro using the shell command and get "Runtime error 68: Device unavailable". This seems to be true when trying to run any unix command (ls, for example), not just the perl interpreter. Seems the only apps that do not give this error are ones installed in the "Applications" folder with proper .app packaging. I have no problems when running the macro on windows.

thanks for any clues,
raphael

Excel VBA Shell command issue: I have an .exe (non-Microsoft, report generator) that I need to open from Excel. When I open this .exe normally, the first window to open is a login window. Like any login, I input the username and password, gain access and go to work. However, when opening this .exe with the Shell command, I have a huge issue. When the Shell command fires, the login window opens as it should. BUT - the UN & PW fail. Everytime. SO, open this .exe normally and I can login. Open it with Shell, the same Un & PW fail. Something about the Shell is preventing the login window from working. I've done a fair bit of reading and can find no one that has had this issue. Any thoughts from any one?

Hello all,

I have been trying to use the "Call Shell" command to run a .bat file. I am not using the script to creat the .bat file, because I have already created it and a corresponding text file the .bat file uses.

When I use this, the command window opens, runs some text very quickly and disappears without doing what I would like.

I know the .bat file works because I can double click on it from Windows Explorer and it opens a command window and completes successfully. I am trying to figure out how to either 1) keep the command window open to see what happens on the screen and/or 2) log everything in the screen to a file. Is this possible?

The code I am currently using:


	VB:
	
 CommandButton4_Click() 
     
    On Error Goto FTPErr 
     
    Call Shell ("F:Testftp.bat", vbNormalFocus) 
     
FTPErr: 
    If Err.Number  0 Then 
        MsgBox Err.Number & " " & Err.Description 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I don't know much code at all, and what you see above is just sort of thrown together. The error logging I tried did not work, probably because there is something missing.

A follow-up question, if I have a text file that the .bat file refers to, do I have to include that file in the VBA code somewhere for it to work?

Thanks in advance for the help.

Evening all,

I am attempting to call an application from excel that is run from a 'DOS prompt'. I can run the app fine from the dos shell but i am not sure how to use the shell command in VBA so the excel app will run it.

Can someone please give me the format of the shell command in VBA?

Thanks in advance,
Di

I use the shell command to launch an access project file.
I need to detect when this process(Access) has terminated (a macro
within the access project file terminates itself once it has finished
what it is doing).

The reason for ths is that I want the Access Project to run and do its
thing;
Excel code waits until the process has finished;
Excel (using VBA) refreshes pivot table.

Can anyone help me on this or is it beyond the scope of VBA ?

------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

This is how I would execute the following in a .BAT file:

"N:AppsOpera ReportsOfficeMSACCESS.EXE" K:ExpCurMonth.mdb /x
"Autorun"

However, if I try to use
Shell("", 0)

in VBA it moans about the ".

For now I just call the .Bat file, but the zero hides the command
prompt window and not the Access window.

I want to run the above command line and also hide the Access window
using zero in the shell command.

Any takers?

------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

I am using a shell command to open a converted excel file with .exe extension:
file_nm = Range("file_name")   'this refers to a file name with .exe extension in a workbook cell

For Each wb In Workbooks
If wb.Name = file_nm Then
MsgBox ("The " & wb.Name & " is already open.")
Exit Sub
End If
Next

open_str = ActiveWorkbook.Path & "" & file_nm
Dim RetVal

RetVal = Shell(open_str, 1)

[COLOR=#333333][b][noparse]
[/noparse[/b][/COLOR]

The shell command opens the workbook in another instance of excel. Therefore searching for an open workbook of the same name to avoid opening the same workbook twice (as in the for Each loop above) doesn't work because the previously opened workbook is in another instance of excel.

I need either to have the shell command open the file in the same instance of excel (preferably), or else know how to look in another instance of excel to see if the workbook is already open.

Please help with example code.

Thanks,

Jerry Erwin

Hi All, I have run into a problem using the shell command with VBA on a userform.

I need to open a explorer window and navigate to our document management software.

This does not work.  It will open the explorer window but only show the "my documents" folder.  

the problem is "documentumCustom Queryxyz_prod_doc" is not a physical address.
I can use the shell command to open any physical address (aka, C:temp) with no problems.

When explorer is open, we typically paste "documentumCustom Queryxyz_prod_doc" into the address bar and then hit enter. My goal is to replicate this with VBA, and if i can get that far, I'd like to pre-populate a sql query from the VBA as well to automate some document searching. (So I am also interested in passing other information to the explorer window)

Essentially, i need to replicate opening explorer, pasting a address, (as i don't seem to be able to pass it with shell), and hitting enter, to start.

Thanks for any suggestions or ideas.

I have a batch file I would like to execute from an Excel macro, and
have the output be piped to a file. For this test I created a file
"dir.bat" in the same folder as "Book1.xls", the file contains one
line, "dir /b /o"

>From the command prompt, if I run it like so

dir.bat > dir.txt

it successfully saves the directory listing to "dir.txt".

However it doesn't seem to be working from the Excel macro:

Sub ShellTest()
Shell ThisWorkbook.Path & "dir.bat > dir.txt"
End Sub

I get no error messages, and dir.txt gets created, but it is empty.

Can anyone see anything wrong?

Much appreciated...

Hi all

I wrote an Excel VBA program to run (Shells to) a DOS program (not mine)
which deals bridge hands called "Big Deal"
It takes certain parameters such as the number of hands required, the name
of the output file and the type (e.g. CSV)

This all worked perfectly under Excel 2000 (Windows 98)
I have now converted/upgraded to Windows XP Home and Excel 2003 and have now
found that the parameters (NoOfHands and Hands, etc) are ignored and have
to be (re)typed at run time

Why is this? Has there been a change in the Shell command under Windows XP
or is the problem with Excel 2003?

The lines I use (which worked under the old setup) are list below:

Dim NoOfHands As Integer
(Note: the input control routine for NoOfHands has, for brevity, been
omitted)
Program = "C:Simultaneous TournamentUnlimitedBigdealbigdeal -n " &
NoOfHands & " -p Hands -f csv"
TaskID = Shell(Program, vbNormalFocus)

Peter Bircher
South Africa

VBA has a Shell command that can open certain programs like this:

RetVal = Shell("C:WINDOWSCALC.EXE", 1) ' Run Calculator

Is it possible to open Bloomberg using this command?

Good day all... hoping for a little help with the syntax on this one. An associate of mine created a phenomenal mini application in Python, which we can run from a command prompt manually. I'm trying to integrate his application into a spreadsheet our group has been using for quite some time now.

Other than opening a dos prompt and doing a "pushd" to map a network drive, I got nothing. And nothing I'm doing is working. And nothing I've read is helping.

I don't think this should be terribly difficult, but... well, for me it is.

I'm trying to do the following:
From VBA, open the Command window, which seems to work with
- Once the DOS window is open, I need to change directories to, something like 
"Corp.Bloomberg.comPn-dfsq_search"

- I need to then execute this line
"q_search.exe small_set.txt"

- Wait until the dataset is loaded (small_set.txt)

From here the script allows some user input, which it then processes (n-gram string searches done via Python), then outputs the data to a file, which I will import back into my spreadsheet.

Hope that makes sense - and thank you for any help!

JP

I'm writing a macro in Excel which finds other files (not Excel files)
with a given name and then opens them. I'm using the Windows registry
to find the programs associated with the files and then using the Shell
command to open the files with the given program. My problem is that
for some programs the Shell command opens a new instance of the program
for each additional file, i.e. each AutoCAD file opens into its own
instance of AutoCAD even if AutoCAD is already running.

So my question is, is there a switch for the Shell command that tells
it to check to see if an instance of a program is already running
before starting a new one, and, if the program is already running,
opens the file with the existing instance? Or is there another way of
doing this without getting into the API of each program in question?

Here's a snippet of my code:

strCmd = appWord.System.PrivateProfileString("", _
"HKEY_CLASSES_ROOT" & regType & "shellOpencommand", _
"")

If Len(strCmd) > 0 Then
strCmd = Replace(strCmd, "%1", fileWithPath) 'for pdf & dwg files
strCmd = Replace(strCmd, "/dde", "/one " & """" & fileWithPath &
"""") 'for solidworks files
Shell strCmd, vbNormalFocus
Else
MsgBox prompt:="Could not find an application" & vbCr & _
"registered to display file.", _
Buttons:=vbCritical + vbOKOnly, _
Title:="Not Registered"
End If

Thanks!
Eric

Hi,
I succesfully use a VBA-program for starting up other applications,. i.e. other spreadsheets.
Example:

Sub ShellProg ()
Dim ResStart As Variant
Dim strPath, strFile, strShell, strName As String

strPath = Worksheets("Standards").Cells(2, 2).Value
strFile = Worksheets("Standards").Cells(9, 2).Value
strShell = strPath + " " + strFile
strName = "UserSheet1.xls"

ResStart = Shell(strShell, 1)

strPath = Worksheets("Standards").Cells(2, 2).Value
strFile = Worksheets("Standards").Cells(10, 2).Value
strShell = strPath + " " + strFile
strName = "UserSheet2.xls"

ResStart = Shell(strShell, 1)

etc. This works beautifully.
Now, I want to check whether a certain user file (UserSheet2.xls) has already been openend and I include before the Shell command:
strName = "UserSheet2"
If bFileOpen((strName)) Then
Etc.

Function bFileOpen(wbName As String) As Boolean
Dim wb As Workbook
'check each open workbook's name, in lower case, and set the function
'to true if a match is found. If not match the function defaults to False
For Each wb In Workbooks
If LCase(wb.Name) = LCase(wbName) Then
bFileOpen = True
Exit Function
End If
Next
End Function This check is unsuccessfull, even if UserSheet2.xls is definitively open. However, should I have started up manually my second worksheet UserSheet2.xls, not via ShellProg, then the same test is successful.

So, there seems to be a difference between spreadsheets having been started up through a shell and the same spreadsheets having been started up manually by double clicking on the file name.
Also, after my spreadsheets have been started up through the ProgShell program, I could delete ProgShell itself, but the same difference exists: the spreadsheets having been started by ProgShell act differently from the same spreadsheets having been started by hand, even though the starter program ProgShell has already gone.

Can somebody explain this, please?

Thanks in advance,

Roel

Hi All,

I have a dos command that lists the location of a config file associated
with a program.

This works fine from a dos prompt but can I use the Shell command in such a
way that the location of the config file can be returned into VBA?

Something like Var = Shell("Getconfig") (I am aware that doing this merely
would set Var = process thread associated with shell)

Thanks

Andi

Hi,

I want to run a Shell command to open lots of files.

I can get the basic command working, but I want to open a lot of different files and am seeing if there is a way to save time by having a variable input, rather than having 1000 different shell lines with IF statements.

So...

Instead of

Shell "cmd
/c start C:documentsTesterexample1.txt"
Shell "cmd /c start C:documentsTesterexample2.txt"
.
.
.
If possible I want to generate 3 variables based on cells A1, A2 and A3, and have one shell command.

So, let
A1 = documents
A2 = tester
A3 = example1.txt

The command line would now be:

and would open files from different directories based on inputs.

Is there any way to do this, or, as I imagine, the code must be explicit as it just dumps whatever you write into dos.

If thats the case, I thought a work around might be to enter

Into, say, B1, and then try and execute the formula in  B1.

Can this be done?

Im still pretty new to VBA, so Im sorry if this is an obvious problem, or obviously impossible to solve.

Any help appreciated.

Thanks,

This shell command does not work. No error just exits.

	VB:
	
 CrisnetValue 
CrisnetValue = Shell("C:Program FilesCRIS NETCRIS NET 6main.exe", 1) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This Shell command does work fine. Both in same macro. I copied the path right from the properties of the main.exe to avoid typos and I can execute main.exe through Explore.

	VB:
	
CrisnetValue = Shell("C:crisnetcrisnet.EXE", 1) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Logic will execute one or the other so both are never executed.

Hello

I am trying to open a binary file in excel using a hex editor called frhed.exe which is in my C drive. I am selecting the file to be open ( with .i2C extension)using getopenfile command and using the returned string in the shell command as shwon below, It is giving error. The i2c files are in G drive of the computer.With the name of the file to be opend placed in the shell command as follws works fine Shell("c:hexfrhed.exe " c:hextest.i2c, vbNormalFocus)

	VB:
	
 gh() 
    Dim retvalue, datafiles As String 
    ActiveSheet.Cells.ClearContents 
    datafiles = Application.GetOpenFilename(FileFilter:="I2CFiles,*.I2C", FilterIndex:=1, Title:="Select Files",
MultiSelect:=False) 
    retvalue = Shell("c:hexfrhed.exe " & datafiles, vbNormalFocus) ' open a txt document
    AppActivate retvalue 
    SendKeys "%fe{ENTER}", True 
    SendKeys "%{F4}", True 
    ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range("A1") 
    Range("A1").Select 
    Range(Selection, Selection.End(xlDown)).Select 
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ 
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _ 
    Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _ 
    :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _ 
    Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _ 
    ), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array _ 
    (20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1), _ 
    Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1)) 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I have written a Shell command. It is very simple and designed to open another software. Here it is:

Dim X As String
X = Shell("C:Program FilesMicrosoft OfficeOFFICE11xlview.exe", vbNormal)

Is there a command that will close the open program? Something along the lines of
.....Ofice11xlview.CloseProgram", ...

But more importantly, is there a way to enter a second shell command after the first one that will have XLViewer to open a particular workbook after the viewer is open?

I am new to shell commands and not familiar with all its asopects at this point. Perhaps a website for shell beginners?

Thank you,

is anyone familiar with the shell command? i don't know anything about it or how to use it...but i've heard it will solve my problem.

i need to have excel open up internet explorer and go to a specific web address, that is a a text file online that has comma separated values, and copy that text and paste it into excel for me. anyone know how to do that?

some have mentioned to use the file > open option, however, i'm on a local intranet and can't do that with the old version of excel that i'm on...

your suggestions are greatly appreciated.

thanks
jacob

Howdy,

I'm in the process of building a user form that required the user to brows for a file, the path of the file that they select is then displayed in the textbox next to the "browse" button in my form. I also have another button next to this which when selected will open the file that is displayed in the textbox previously mentioned.

I’ve been trying the shell command but with no success, I can get the user form to open adobe reader but whenever I try to include that file path for which to open the file I keep getting runtime errors, can anyone help.

Below are a couple of examples that I’ve tried (you'll prob be able to guess from the code that I’m very new to VB in excel).

For additional info the value in the textbox would be something like, "H:My DocumentsPDF Filesmyfile.pdf"

Dim GetFile As String
Shell "C:Program FilesAdobeReader 8.0ReaderAcroRd32.exe", vbNormalFocus
'this opens adobe reader but I'm trying to open a specific file

Shell "C:Program FilesAdobeReader 8.0ReaderAcroRd32.exe" & textbox9.value, vbNormalFocus
This produces the runtime error again

Dim GetFile As String
GetFile = TextBox9.Value
Shell "C:Program FilesAdobeReader 8.0ReaderAcroRd32.exe" & GetFile, vbNormalFocus
'this produced a runtime error - to be honest this was an attempt born out of frustration.

Any help would be greatly appreciated.

Thanks in advance

G