Any help you can lend on this would be greatly appreciated!! My question is how do we update the formula for every minute and retrieve the answer in a sequence of cells along with the timestamp till we click the stop button. It can take a few seconds for the macro to actually end because it still needs to complete one more refresh and wait cycle. so for those two hours the web query refreshed but macro did not run! On Time runtime, "Extractor" End Sub Sub Extractor() ' ' Retrives required information and copies to Table Range("A6: G6"). On Time Time To Run, "datafeed" End Sub Sub datafeed() application. Save As Filename:= _ "C:\Users\Carella Home\Desktop\DSA Data Feed.txt", File Format:=xl Text, _ Create Backup:=False Call autorun End Sub Sub auto_close() application.For this reason Cell A1 is set to equal 2 until the macro actually exits the loop. On Time Time To Run, "datafeed", , False End Sub Sub eodsave() application. Save As Filename:= _ "C:\Users\Carella Home\Desktop\DSA Data Feed.xlsm", File Format:=52, Create Backup:=False End Sub ------------------------------------------- It works fine and saves the file that I need it to save as a text file every 15 seconds.This tip (9151) applies to Microsoft Excel 2007, 2010, and 2013.You can find a version of this tip for the older menu interface of Excel here: Turning Off Screen Updating.
I stumbled across your VBA Tips #9 talking about a running a code every hour, minute or second and thought you would be the guy I should be talking to. The above code works good and it updates the result in same cell. Its also a nice touch to add some text in one of the spreadsheet cells to indicate if the auto refresh macro is running, aborting or stopped. The IF statements cause the loop to end if A1 is not equal to 1. So for eg I had once, it worked till and then two hours it did nothing and started again around something. Regards My Macro is as follows: Dim runtime Sub Start() '' Start Recording vessel positions 'Call Extractor End Sub Sub Duration() 'Define Time interval required runtime = Now Time Value("") Application. Here is my code below; ------------------------------------------------- Dim Time To Run Sub auto_open() Call autorun End Sub Sub autorun() Time To Run = Now Time Value("") application.This does not help the macro to stop, it just puts some signaling ont o the spreadsheet so that you can tell the user if the macro has actually stopped yet. The problem is that when I close the file, I get the following message; Run-time error '1004': Method'On Time' of object '_Application' failed Can anyone tell me what I am doing wrong here?This is important because closing a spreadsheet while a Macro is running is not pretty. Screen Updating = True I am unable to successfully include the following macro to get it to run every 1 min, appreciate your guidance on how to include my code. I essentially need my excel file to spit out a text document save of itself every 15 seconds.This means that the Excel screen can look like it has "gone crazy" while the macro is running.One thing you may want to do with your macro to make it run faster and to prevent distracting flashes on the screen is to turn off screen updating while the macro is running.