Wait time Problem

[billheath]

I am having trouble accessing internet data and copying it to my worksheet. My Code is:

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

Range("E12:E83").Select
Selection.Copy

Range("B12").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("C4").Select
Application.CutCopyMode = False
Selection.Copy
Range("C6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


Range("B75").Select
ActiveCell.Value = "=C74 * C75"
Range("B78").Select


The "RefreshAll" goes to the internet and pulls in the data okay. Howevwer, I can't stop the copy and paste until the refresh is done. I put in the "Wait Code" above. However, the RefreshAll command waits for 30 seconds before proceding even though it is inserted before the wait command. Any ideas would be appreciated!!
Thanks,

 

[SkipVought]

Hi,

I'd loop thru each sheet and on each sheet loop thru each QueryTable doing a Refresh with the parameter of FALSE, to wait for the refresh to complete before the code can continue.

Your QueryTables might be part of a sheet or a ListObject

Dim ws As Worksheet, lo As ListObject, qt As QueryTable

For Each ws In Worksheets
    For Each lo In ws.ListObjects
        lo.QueryTable.Refresh False
    Next
    For Each qt in ws.QueryTables
        qt.Refresh False        
    Next
Next

As an asside relative to waiting 30 seconds for some asynchronous process to complete (which could actually take less than 30 seconds or more than 30 seconds) would you decide, before entering your car to drive to work, that you'll wait 30 seconds at each intersection before proceeding through any intersection?

 

[billheath]

Good ideas. However, what I want the code to do is to stop until the fist statement "ActiveWorkbook.RefreshAll" has completed before continuing with the next line of code "Range("E12:E83").Select".

 

[SkipVought]

Substitute my code for that one line.

 

[SkipVought]

Just out of curiosity, why would you want B75 to contain the TEXT, "=C74 * C75"

I'm just guessing that you really want this..

Range(B75).Formula = "=C74 * C75

 

[billheath]

Thank you, Skip. Everything worked as you said until this morning. When I opened the file this morning, I received the message,
"Unable to open

http://moneycentral/tinvestor/external/excel/quotes.asp?SYM

Cannot download the information you requested"

This worked all last week. I'm not sure what changed, if anything. And yes, I am connected to the internet and am able to open moneycentral.msn.com

The code stops on
" qt.Refresh False "

Bill

 

[billheath]

Sorry, I did that wrong. The message reads, "Unable to open

http://moneycentral.msn.com/investor/external/excel/quotes.asp...

 

[SkipVought]

This seems to be an entirely different issue, yes?

It is the first time you discussed THAT you opened a web location and certainly we do not know HOW you effected this in your code. So how can one cogently reply without the appropriate supporting information?