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
ADVERTISEMENT
Feb 4, 2010
I'm using a pre-made spreadsheet from my stock broker (Interactive Brokers) that retrieves and displays real-time quotes, and allows one to retrieve historical stock data, among other things. I've created a little macro within it to try and automate some common tasks I do everyday - basically I want to retrieve a year of daily stock quotes for "stock 1" from IB's servers, then have the macro wait for the retrieval to be done and written to the spreadsheet (takes anywhere between 10 and 30 secs). After that's all done, then I want to do the same for "stock 2". Then when that's done, the macro proceeds to go ahead and do some calcs on stock1 and stock2. The problem is after my stock1 data request, the macro just keep on trucking through to the next commands while stock1's data retrival is still going on.
so things are getting all balled up. How can I get my macro to wait until stock1's data retrieval is all done?
View 14 Replies
View Related
Oct 13, 2011
Is it possible to insert a wait command in milliseconds- minimum I have seen is a second?
View 1 Replies
View Related
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
View Related
Dec 10, 2012
see attached file. Need to find latest non blank value - in attached file it is highlighted in yellow. From there, want to summarise 26 weeks back so, in the attached file:
Row 2 would be finding 750 and summarised back 26 weeks from 30 sep 2012
Row 3 would be finding 2250 and summarised back 26 weeks from 2 dec 2012
Row 4 would be finding 5000 and summarised back 26 weeks from 4 nov 2012
View 7 Replies
View Related
Jan 24, 2014
How do I pause my macro until the user selects a chart in the worksheet? Or Keep prompting a message to the user till he selects the chart and when a chart is selected run the macro.
Step 1: Check if a chart is Selected
Step 2: If yes, run the macro
Step 3: If no, prompt the user with a msgbox of Retry/Cancel.
Step 4: If the user selects Retry, wait till he makes a selection.
Step 5: If the selection is a chart, run the macro. If the selection is not a chart prompt the same msgbox again and keep looping it till he selects a chart.
Sub test5()
Dim chtSelected As Chart
Dim UserResponse As Integer
On Error Resume Next
Set chtSelected = ActiveChart
[Code] .....
View 2 Replies
View Related
Jun 23, 2009
I have a very long code with multiple functions and operations i.e. it calculate many fields. The normal running time varies from 2 minutes to 30 minutes depending on the data size.
Can any one tell me a way so that I can put a kind of status bar to show the progress or estimated time left? Basically, during operation it looks like excel is hanged and not responding but infact its not.
View 9 Replies
View Related
Jan 11, 2009
What is the simplest way to display a "Please Wait.." message during the execution of a macro.
Tried the WshShell.Popup but did not work.
View 10 Replies
View Related
Jan 6, 2014
how do I code in vba for it to wait until the user has checked the file that has been made make a few amendments and then when done one would click OK or resume for the program to carry on with the rest of the code.
View 2 Replies
View Related
Jan 29, 2014
If I run this in the editor sometimes it works and sometimes it does not.
If I run this in editor with F8, stepping through the code, it does work.
I can only guess that enough time is passing during the F8 presses that the page can load. But is that not the purpose of adding the wait code?
[Code] ....
View 9 Replies
View Related
Apr 7, 2008
I've a userform style progress bar which stops updating if i use application.wait within my code like this:
UserForm1.FrameProgress.Caption = "0%"
UserForm1.Label1.Caption = "Starting Process.."
UserForm1.LabelProgress.Width = 0
UserForm1.OkButton.Visible = False
DoEvents
Application.Wait (Now() + TimeValue("0:00:01"))
View 9 Replies
View Related
Apr 17, 2008
been checking out 1/2 a second wait instead of a 1 sec wait
and would like to know it you can do this
timer thing
Dim x As Single
x = Timer
While Timer - x < 0.5
Wend
more than once
it works the first time and then it sort of skips it
or is there ANOTHER way of having a delay of less than a second?
View 6 Replies
View Related
Jun 2, 2007
My question is if it's possible to change the code and make it work faster. When I change the ComboBox I wait about 1-3 seconds for about 40 items in the Worksheet. I still didn't test it with more items. Here's the
Private Sub ComboBox1P2_Change()
If ComboBox1P2.ListIndex = 0 Then
ComboBox2P2.Style = fmStyleDropDownCombo
ComboBox2P2.Value = sign
ComboBox2P2.Enabled = False
ComboBox3P2.Clear
ComboBox3P2.Enabled = False
ComboBox1P2.SetFocus
ElseIf ComboBox1P2.ListIndex 0 And ComboBox1P2.ListIndex -1 Then
ComboBox2P2.Style = fmStyleDropDownList
ComboBox2P2.Enabled = True...........
View 9 Replies
View Related
Jan 26, 2009
I have a workbook with alot of results that employees will need to wait for results to show.
While it Calculates.. can we get a cell to show "Please Wait - Searching" so they know its actually doing something for them?
View 9 Replies
View Related
Aug 21, 2006
When clicking the button on my userform, it goes through a quite complex process of changing the views in several pivot tables that are linked to an "report" sheet, which has all the figures the user needs in a neat format.
Changing these pivot tables takes up to a minute, so I wanted a userform, called "frmwait", to pop when clicking the command button. It would say "Generating Your Report - Please Wait". At the same time, the initial userform, which is called "frmroutedashboard", would be hidden.
This does not happen - the "frmwait" userform shows, however the "frmroutedashboard" does not hide.
Is this because the code I composed uses the values on the "frmroutedashboard" to generate the view, and it cannot hide until the report is completed? If so, there must be a way around this. Anyone know how?
The initial code so far looks like this:
frmroutedashboard.hide
Application.Cursor = xlWait
Application. ScreenUpdating = False
Application.DisplayStatusBar = True
Application.StatusBar = "Updating Route Dashboard..."
I thought if it looked like this, it would be sufficient. Unfortunately, the file is too large to attach, so I hope the code suffices.
frmroutedashboard.hide
frmwait.show
Application.Cursor = xlWait
Application.ScreenUpdating = False
Application.DisplayStatusBar = True
Application.StatusBar = "Updating Route Dashboard..."
View 8 Replies
View Related
Mar 18, 2007
Situation:
At two places in the middle of my code I need to specify a particular variable; usually this is done by some autodection-method, but whenever this fails a userform gets loaded and the user needs to do some clicks which finally specifies that variable.
Problem:
So far my code does NOT wait for the userform (and the user-input), but continues running until it fails, cause that particular variable (see above) had not been initialised with the correct value for this run.
My Question:
How can I force the code to wait until the userform has been closed? Or what other way do you usually solve that problem when wanting to use a userform in mid-code? Below you find the place where I load the User form 'UsrFormSpecifyFormat' ....
View 9 Replies
View Related
Nov 18, 2008
when I run a macro, it takes me to a different sheet, clicks on a cell, and then goes to data--> form to automatically generate a form so I can make a new entry. I want the macro to wait until I have completed the form, and on completion to do something else. I'd prefer it if I could indicate completion of the firm by just a keystroke, but a mouse click will do as well.
View 2 Replies
View Related
Feb 27, 2010
How can I put a VBA script to wait for 30 seconds, then close the Excel file (w/o saving it) which its name is stored in CSV_file_name variable, then re-open it?
View 9 Replies
View Related
Feb 4, 2013
I am using Excel 2007.
I have some pivot tables that have the data source in another workbook. I didn't create any external data connections. When I created the first pivot table, I just selected the range from that other workbook. Most of the other pivot tables were copied and modified accordingly, so they should have the same pivot cache.
I have created a macro that basically refreshes all data, using the Workbook.RefreshAll command, then copy/pastes values and then deletes the pivots so that I can send it via e-mail without problems. The macro seems to work fine.
The QUESTION relates to the fact that I am afraid VBA won't wait for the refresh to complete before starting to copy/paste values.
The PivotCache.BackgroundQuery = False solution doesn't work. I get 10004 error. Also, there are no data connections visible in the Data > Connections. I guess that, because of this, "Properties" is greyed out in the Data tab and also "Connection Properties" under Pivot Table Tools > Options > Change Source Data is greyed out. This way, I cannot uncheck the "Enable background refresh" box.
I could just tick the "Refresh when opening the file" in Pivot Table options, but if I open the file without first opening the workbook with the raw data, refresh takes very long.
I have tried to measure time difference between the RefreshAll command and a simple Range("C3").Select command. There is a time difference, but I'm still not 100% sure that VBA waits for refresh to complete before continuing.
VB:
Do Until Application.CalculationState = xlDone
VB:
[COLOR=#333333][COLOR=#333333]DoEvents[/COLOR][/COLOR]
[COLOR=#333333][COLOR=#333333]Loop[/COLOR][/COLOR][COLOR=#333333][COLOR=#333333]
[/COLOR][/COLOR]
View 9 Replies
View Related
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
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
Jun 20, 2013
Excel 2007
I have this simple formula: =IF(SEARCH("ABC",BQ239,1)>0,"Found", "Not Found")
Instead of saying Not Found when the value is not in the cell BQ239 text string I get #VALUE! returned
how do I get it to say "Not Found"? I searching for a substring that can be anywhere in the string.
View 2 Replies
View Related
Jan 15, 2014
I have a macro where I manually select a cell then the macro kicks in to copy and paste the contents into the Find function. From there it goes to another work sheet, clicks on a cell in column one and searches for matching cell contents. Then If false it manually goes back to the original worksheet/cell and then I input a N in the left adjacent cell. If true I enter a Y. Right now I can only run this for the specific cell the macro was recorded for. I would like to expand this.
What I would like to do create a loop in the macro that waits for my cell choice input then continues with the with the Find function. If false the macro should just loop back to the original worksheet/cell and wait for input, ie the next cell selected. However, if true a worksheet/cell is selected, a Y is input and it loops back to the original cell and waits for input.
View 9 Replies
View Related
Jul 5, 2007
I want to allow users to place files (.jpg, .tif, .pdf, .pps etc) into a network folder and then have my program open each file and display it for 30 seconds then close the file and the application before opening the next file.
1. Set up a loop to get a directory listing of the folder and write the listing to a text file.
2. Open up the text file and get the next filename in it
3. Open the file in the associated application
4. Wait for 30 seconds (or some period of time) and then close the file
5. Repeat steps 2-4
DoIt = 1
While DoIt = 1
Open "C:TempList.txt" for output as #1
Print #1, Files In Folder
Close #1
Open "C:TempList.txt" for input as #2
while not eof(2)
Line input #2, MyFile
Display MyFile on screen
Wait for 30 seconds
Close MyFile and MyApplication that opened it
wend
Close #2
I can get the directory listing just fine with no problems
I can open the files in the associated application just fine with no problems.
with closing the application after 30 seconds or some period of time.
I need some code that will allow me to easily send it a filename and it will know how to close the file and the application that opened it.
View 9 Replies
View Related
Jul 15, 2009
I have an issue new client is asking for me to present my reports with a percent complete. We do survey work and all our date is in Ft. The way my file is set up right now i have H5-H100 as incomplete footage, Column I5-I100 has my completed footage and to keep it simple Row 13 A-k show total amount of footage. He wants to see everyday when i submit the % complete based of footage incomplete and footage completed.
Anyone able to help me out with a formula for this? I tried messing with it a little, wasn't really able to get it down and my Excel skills definitely aren't past beginner level.
View 6 Replies
View Related
Nov 26, 2008
Excel cannot complete this task with available resources. Choose less data or close other applications. My file is only about 3mb in size, made up of 17 worksheets. These sheets are calculated by referencing to another file that contains all of the background data. The data file is also about 3mb, made up of 13 worksheets.
There are probably about 2 - 3 thousand formulas in the file in total, ranging including vlookups, sumifs, sumproducts, etc. When the data was contained within the file there was no problem. I moved each data sheet into a new workbook to trim the size of my file and also stop the incessant calculation and this is when the problem started. Now, when I open the file and am prompted to update, it will update to about half way and then throw up the error message!
View 4 Replies
View Related
Dec 23, 2008
I have desinged an excel invoice, but I think I might want it to do the impossible.
I have my companies database as a .csv file, is there any function where I can enter in the company account number and it will automatically fill in the company name, address, contact etc like a mail merge type thing and for it to work vice versa enter the company name and it fills in the account number and address all automatically taken from the information on the .csv file?
View 9 Replies
View Related
Mar 12, 2013
I want to enter a name in a cell from a list of 70 names I have on another work sheet. By just typing the first few letters of the name into the cell I need it to complete the entry. I do not want to use a drop down box it takes too long to find the name. I need a formula to do this for my spread sheet.
View 6 Replies
View Related
Jun 23, 2014
I have a file that i need to use for analysis but it is currently a text file, how can i use VBA to open it with excel and then complete text to columns, using a delimiter of a semi colon.
I have attached a sample of before & After.
View 14 Replies
View Related
Feb 5, 2014
I have a form I'm building that I want to put on the server to allow different people to complete as needed.
As they complete this form I want to take all the info they entered and have it go into a data base once there I want to clear the form for the next person.
View 3 Replies
View Related
