I am downloading live data from bloomberg and am attempting to write a macro that loops through several different stock symbols and saves the corresponding data for each symbol to its own invididual workbook. My problem is that I need to add a delay to my code in order for the live data to update before the rest of the code is implemented. Application.Wait will not work for this, nor will Sleep because these functions pause the execuation of the code.

I am using On.Time to to delay the processing of the rest of the code by 3 seconds. This works great when only processing one stock, but once I put the code into a loop, it flies through all of the stocks quickly and seemingly ignores my request to delay to code using On.time. I have been wrestling with this and tried just about every thing I can think of to pause the code. My code is below if anyone would like to look through it. There are two Modules (Symbolloop and SaveCopy). The ontime delay in symbolloop calls Savecopy.

Thank you.


Sub symbolloop()
' Loop through symbols

Dim PauseTime, Start
Dim intRow As Integer
Dim intMaxRows As Integer

intMaxRows = Cells(70, 1)
intRow = 1

Do While intRow <= intMaxRows

'MsgBox intRow

ActiveCell.FormulaR1C1 = "=[CurrentlyOwnedStocksDB.xls]Sheet1!R" & intRow & "C1"

'Application.OnTime Now() + TimeValue("00:00:03"), "SaveCopy"

intRow = intRow + 1


End Sub

Sub SaveCopy()

Dim NewName As String
Dim nm As Name
Dim ws As Worksheet

'run regression analysis
Application.Run _
"'Master 20YR Dividend Regression actual - updated.xls'!Aregression20"
Application.Run "RefireBLP"

' save copy
With Application
.ScreenUpdating = False

' Copy specific sheets
' Array("Sheet Name", "Another sheet name", "And Another"))
' Sheet names go inside quotes, seperated by commas
On Error GoTo 0

' Paste sheets as values
' Remove External Links, Hperlinks and hard-code formulas
' Make sure A1 is selected on all sheets
For Each ws In ActiveWorkbook.Worksheets
ws.[A1].PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
Cells(1, 1).Select
Next ws
Cells(1, 1).Select

' Remove named ranges
For Each nm In ActiveWorkbook.Names
Next nm

' Change Color Scheme to copied workbook so that colors/charts match
ActiveWorkbook.Colors = Workbooks( _
"Master 20YR Dividend Regression actual - updated.xls").Colors

' Save it with the NewName and in the same directory as original
ActiveWorkbook.SaveCopyAs "F:\Test\" & "20YR Div Reg - " & Range("c11") & " - " & Format(Date, "mmddyyyy") & ".xls"
ActiveWorkbook.Close SaveChanges:=False
End With
End Sub

have you tried using DoEvents then sleep.. try it.. i hope it helps..

Where do I insert DoEvents? Also, sleep doesn't seem to work because it pauses the entire execution of code.

Thank you

have you tried using DoEvents then sleep.. try it.. i hope it helps..

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.