Pausing Within For Loops Until Shell Command Is Complete

Nov 3, 2009

I have written a For loop that uses a shell command to call a batch file that replaces text in some text file, Mytrail.txt. I then use that altered text file and use it to run a 3D modeling program Pro/Engineer by calling it through another shell command. I would like to add pause instructions to my routine after the shell commands. Below is a snippet of the code I am working with:

For i = 1 To N Step 1
Shell ("Reblacer.bat Mytrail.txt string_A string_B)

'Here I would like to add code that will pause the routine until the batch file 'operation is complete.

Shell "C:Program FilesproeWildfire 3.0inproe.exe Mytrail.txt", vbNormalFocus

'Here I would like to add another pause instruction until the above shell 'command is complete. Note that this shell command creates a new file in 'some directory whose name is actually string_A.prt. Could this be used to 'tell visual basic that it may recontinue executing the routine.

Next i
End Sub

How to add pauses to the routine above that prompt visual basic to continue executing the code only after the shell commands have completed their tasks?

View 2 Replies


ADVERTISEMENT

Shell - Adding Another Command To Forfiles Command?

Apr 19, 2014

I am playing around with the Forfiles command (being called from Excel via Shell)..

I can't for the life of me get it apply a second command (such as getting the file size of each file)..

For example:

VB:
Private Sub CommandButton1_Click()
Dim Z
Z = Split(CreateObject("wscript.shell").exec("cmd /c forfiles /P C:UsersapoDesktopTextFiles /S /M *.* /d

[Code]....

The end result being the filename and the filesize shown..

View 4 Replies View Related

Shell Command In Vba

Jun 29, 2007

I have to run command prompt from with my VBA code , and run an application passing it 2 filenames and redirecting the output of the application to a third file. Also my VBA script should wait for the application to complete running before getting back to the next line of execution. HEre is what i have coded so far :

Sub Macro1()
Dim dosCmd As String
dosCmd = "purge.exe input1.txt input2.txt >> opFile.txt"
Call Shell("cmd.exe " & dosCmd, vbNormalFocus)

Now this code ends up just opening the command prompt and nothing else. It does not run purge.exe.

View 2 Replies View Related

Way To Enter A Second Shell Command After The First One

Aug 11, 2008

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

Dim X As StringX = 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?

View 9 Replies View Related

VBA Shell Command On A .BAT (batch) File

Jul 13, 2009

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.

View 15 Replies View Related

Shell Command Not Working With Windows 7

Nov 17, 2009

I'm very much in a bit of a pickle. I have updated my operating system from vista to windows 7 and a macro no longer works. The old code is

View 4 Replies View Related

Using Variable String In Shell Command

Nov 1, 2013

I'm trying to use a variable that hold a string in Shell string command. This shell is the command to take a snap picture over a given region and store the picture to a pre determined file name

***The code do compile***

The string variable is : StringAddress = "g: estsnapasnap1.jpg"

The following shell command will work

RetVal = Shell(("C:Program Files (x86)HyperSnap 7HprSnap7.exe " & " -hidden
-snap:x3602:y155:w1432:h830 -save:jpg g: estsnapasnap1.jpg"))

This one, with the string address variable, wont work

RetVal = Shell("C:Program Files (x86)HyperSnap 7HprSnap7.exe " & " -hidden
-snap:x3602:y155:w1432:h830 -save:jpg " & StringAddress)

View 1 Replies View Related

VB Shell Command To Browse Files

Oct 15, 2008

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,

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.

View 9 Replies View Related

Using Macro To Enter Variables To Shell Command

Oct 12, 2009

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.

View 8 Replies View Related

Possible To Reuse Opened Shell Command From Excel VBA

Jul 27, 2014

I am using a shell command to communicate with a device under test and would like to open this shell once, change directory to a specific one and then send different text strings to it at various times during my test program. The code below works perfectly fine but opens and closed the shell each time it is used. I think it woud be quicker if I could reuse it until done and then exit out of it.

Here are 2 examples of a couple different strings I would like to pass into the cmdShell function at various times. I would like to send the first string, perform some measurements and then repeat with the 2nd string.

i.e. shellStr1 = ("cd .. & cd c:ezspi & testdm900 --widn 0x1020 --widv 0x1") then once the shell is open and the directory has been set send shellStr = ("testdm900 --widn 0x1020 --widv 0x5")

Is this possible using WShell Script or do I need another method?

Public Sub cmdShell(shellStr As String)
Dim Test As Double
Dim response As VbMsgBoxResult
Dim cmdObject As Object
Dim ReferenceTime As Double, ElapsedTime As Double

Set objShell = CreateObject("WScript.Shell")

[Code] .........

View 4 Replies View Related

VBA Shell Command To A Launch Applications On All Computers (no File Paths)

Apr 14, 2006

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?

View 9 Replies View Related

Have VBA / CMD Wait Until Previous Command Is Complete

Jul 31, 2013

Below is the code I currently have implemented. The first line of code creates a .csv file. The third line of code opens that file. I couldn't put the third line right after the first because it would try to open the file before it was finished being created. Hence, the second line which waits for 5 seconds. Is there a way, either in the CMD or VBA, that I can have it wait until the first line of code has completed?

VB:
Shell "cmd.exe /c M:" & "&& cd DesktopExcel Project" & Command
Application.Wait Time + TimeSerial(0, 0, 5)
Workbooks.Open("M:DesktopExcel ProjectInfo.csv")

View 1 Replies View Related

Pausing And Referencing In VBA

Oct 7, 2008

I have made 2 macros that run one after the other to perform a certan task. My issue is that I want to run Variants that I have defined in Macro1 In Macro2 but I dont know how to reference them macro 2, or if there in the same module do they recognize the varients anyway??.

I'll list the variants defined below; could someone provide me with the proper code to reference the variants form macro1 in macro2. I am also wondering if it would be easier to combine both macros under one macro and simply put a pause in the macro between both section and have a certain command to resume once the user decides to( so it would have to be a user command such as a button). If you think this would be easier could you please provide the code to pause the macro until A button is hit. both these methods woud be useful to me elsewhere as well so if it's not to much to ask could you provide both methods.

View 2 Replies View Related

Pausing Before Macro

Jul 7, 2009

1) open at a specific time each day,
2) grabs some information from Bloomberg (updating takes about 15-20 seconds after opening the workbook)
3) and then run a macro after the updating is finished.

Right now I've:

1) created a batch file to open the file
2) set Windows Scheduled Tasks to run that batch file at 4:15pm
3) Created a workbook open event that calls my macro if the workbook is opened between 4:14pm and 4:17pm

What I'm looking for is the best way to pause before running my macro until the workbook has been open for say 30 seconds. I have read through a lot of threads asking this question, but the threads have not been marked solved, and it seems that a lot of the proposed solutions have the problem of pausing all activity in the sheet, so the information from Bloomberg doesn't update while the macro is paused.

View 5 Replies View Related

Excel VBA Pausing For Bloomberg?

Apr 30, 2014

Trying to create a macro where it will begin by inserting today's date into a cell, then pause for roughly 35 seconds while the massive correlation matrix pulls all the correlations of the portfolio in, then takes an output value that is generated in the workbook from the filled correlation matrix (correlation coefficient) and copy's and pastes it in a different cell. Then it repeats this process again using "Today's date - 1" (yesterday's date), waiting 35 seconds again and taking the new output value and copy and pasting it below the previous one.

I know I am not close but here is what I have for code thus far (which does not work properly).

[Code].....

View 4 Replies View Related

Folder Structure By Shell

Nov 18, 2009

I want to find the folder structure, but walking down. I have try this code

View 2 Replies View Related

Shell To Open Word

Jun 2, 2009

not sure why this is not working, it is from Access I know this is the wrong forum but figured I haven't got any answers from anywhere else and it seems like a simple fix

this command fails... i am guessing its to do with the file path having spaces but don't know how to get it to work, what to insert so it will work.

Call Shell("V:Program Files (x86)Microsoft OfficeOFFICE11WINWORD.EXE / L:Best Practice ManualFull Labour Hire Best Practice Manual.doc", 1)

View 9 Replies View Related

Shell Function To Make This Run From A VB Macro

Jul 3, 2006

I'm trying to run an application using a VB macro. The command line provided to me is "C:Program FilesWeb Scraper Plus+PkgExec.exe" -p "5A147DC7-0420-4321-9DEC-02B8D8111A78". This command line works fine from my Run menu. Now how do I write a Shell function to make this run from a VB macro?

View 4 Replies View Related

Excel 2010 :: Error In Shell During Opening PDF

Feb 25, 2014

I'm using excel 2010 and pdf creator to make a pdf (and open it automatically). It works with the following code:

[Code] .....

path and filename are previously defined objects.

I'm using pdfcreator because the code also has to be used with excel2003 on another pc. The problem is that I get an error (probably for trying to open the newly creating pdf-file).

Error 2417024894 (80070002) during execution. Method run of Object IWshSell 3 failed.

View 2 Replies View Related

Using Shell To Call Batch File Or CMD Prompt

Dec 9, 2011

I'm working on my first VBA project that requires 12 fields to be manually input of which one field is a file path. I need this path for multiple reasons, but the one reason I'm having a problem with is using the location of the path in conjunction with calling a bat file or executing a cmd prompt that is at that path.

Path Location, minus file name:

Code:
sheets("b. Fill Out Required Info").Select
Range("B18").Select
Actual Path found in B18:
C:UsersNickDesktopSubmission ToolTest Files

File attempting to execute:

CopyFileNames.bat

I can manually put all of this together and get what I want by using:

Code:
Shell "C:UsersNickDesktopSubmission ToolTest FilesCopyFileNames.bat", 1

The problem is, every time this will be used, a different path will be used, but the same batch file name will be used. So, rather than having to manually input the file path every time, is there a way to replace "C:UsersNickDesktopSubmission ToolTest Files" with the cell reference of the path listed above? I've tried different methods of this but I cannot seem to get it.

It would be even better if I could just use the cmd prompt found within the batch and run it directly from vba; the prompt I'm running is as follows:

Code:
cd C:UsersNickDesktopSubmission ToolTest Files dir /b/o |find ".xml">ListOfFileNames.txt

How can I put that cmd into VBA? I tried different versions of this with no success:

Code:
Shell "cmd.exe /c cd C:UsersNickDesktopSubmission ToolTest Files dir /b/o |find ".xml">ListOfFileNames.txt"

Notice that the path is the same as the path shown in B18 above. It would be great if that path would be dependent on the cell referenced above.

View 9 Replies View Related

Use The Shell Method To Open Files And Folder

May 11, 2007

I know this is simple, but im having problems finding it in search. Im using the Shell method to open files, but how to open folders?

View 7 Replies View Related

Shell Function Compile Error - Expected Procedure Not Variable

Aug 8, 2014

I have a VBA shell script that worked just fine on another computer, but now it won't run.

The code that won't execute is:

[Code] ........

View 12 Replies View Related

Press Command But And Execute The Code Assigned To Another Command Button

Apr 14, 2009

Is it possible that when you press a command button, that the first thing it does is to execute the code assigned to another command button (IE in another sub).

View 9 Replies View Related

Loops In Vba

Apr 13, 2007

how can i make a loop throught the G colum and to check if the cell before the currect is bigger/ lower or equal to the currect cell. and if its higher then an arrow will be shown '^' if its lower an upside down arrow will be show 'v'
equal will show -> arrow. example:

G2 = 200
G3 = ^ 300
G4 = -> 300
G5 = v 209

so how can i change the G3 to G4 G5 etc.. in a loop in the: Range("G3").Select command? and i need to change the

.Value = "=$G$2"
to
Value = "=$G$3"
Value = "=$G$4"
etc...
in 2 places................

View 4 Replies View Related

Macro That Loops

Oct 18, 2008

i have this code that upon opening of workboot it generates the next number thats available on sheet 2 i then enter the data that i need to and click the button it then transfers to sheet2 and prints 2 copies and then clears the cells that i have input data to

what i really need it to do is exactly as it does right now but to then change the next number available to what it is ....

View 14 Replies View Related

Nested Loops..?

Aug 11, 2009

I seem to be having trouble creating a nested loop. It seemed simple enough in my brain holder, however, in real life...Well I am here aren't I. Here is my

View 2 Replies View Related

Using For Loops When Referencing

Aug 20, 2009

First of all, I'm completely new to both this forum and VBA. I have just done a programming course in java before. I hope you will forgive me if this have already been posted. No to my problem.

I am tryring to use different projected values to project other subparts of those units, which is done in the sheet mean needed weekly. Tje values are taken from projections. The answer that I get from mean needed weekly should be copied to a third sheet.

This is the code that I am using right know, but I cant get the for loop to work (or more exactly to be able to place the "i" in my text without making compilation errors).

View 9 Replies View Related

Eliminate Loops

Oct 9, 2009

One of the multitude of areas where I still need drastic improvement is with efficiency. Specifically, avoiding loops.

Are any of you aware of any good, basic level sites, blogs, posts, etc., about better methods or processes? I'm not particularly experienced with programming (I've been a software TESTER for 13 years and just started some programming in recent months), so the more basic the better.

View 2 Replies View Related

Loops With Formulas

Oct 2, 2008

I have recorded a macro that cleans up my data that I receive from an external source and inserts two columns.

I recorded a seperate macro to execute an "if formula" that evaluates Column A and B and if A is blank return the value in B, else return the value in A. I wrote the formula =if(a1=" ",b1,a1).

I referred to the text I got in the class and found that I can use a "Next Loop". I added to the formating macro the Next Loop critera and instead of inserting text I inserted the above formula.

It ran and returned data, however it was not in the column I intended, it cascaded off to the side of my data in a step down (kinda like stairs).

View 9 Replies View Related

VBA Colourindex & Do Loops

Nov 3, 2009

I need to maintain a KPI in excel2000 which details weekly progress, I normally do this using some condition formatting and hiding cells, I have to be honest its not pretty.

What I would like to do is to use the colorindex function to show the wk breakdown status and a Do loop on my target range, I think!...... I've a basic example below,

If I could show the benefits to my boss he might even let me take a VBA course.....

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved