1 second delay

[tlindeman]

Could you please supply me witht the vba code that creates a one second delay between running the next section of my code? I have created code that connects to google.com/finance and downloads the financials based on the tickers in A. However, I keep getting errors that the web query is not valid. However, when I refresh the connection it works. I belive that it is moving to fast. Here is a snippet of my code:

If Range("A232").Value = " " Then Exit Sub
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.google.com/finance?q=" & Range("A232").Value & "&fstype=ii", Destination:=Range("B232"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery = False

 

[Zakkaroo]

This is borrowed from an old project that I did. I hope it works and that i've not missed a bit of the code out somewhere lol

You need to create a module and insert the following at the top:

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Then, in the middle of your code:

Sleep 1000

This would cause it to pause for 1 second (1000 miliseconds)

 

[Datsmart]

Add this line of code where you want the delay:

Application.Wait (Now + TimeSerial(0, 0, 1))

If you want more delay, change the "1" to the number of seconds you want the delay.

 

[Zakkaroo]

I had an alternate version that i've found in case you had any problems with the first one.

newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 1
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

Edit - lol beaten to it, but with a much more efficient version

 

[tlindeman]

Thank you all for your help

 

[Jonmo1]

You might also change

.BackgroundQuery = True
to
.BackgroundQuery = False


This should force VBA to wait for the query to complete before resuming it's next command.