Update of web queries (macro)

[NelsonX]

Hi all,

I have a macro which runs various web queries (as opposed to updating individually using the toolbar); and is also based on cell input. In addition to this I want to automate other procedures like copying formats to retain a specific format to my web queries. For example, here is an extract of the code:

----
Sub Test()
Sheet10.QueryTables.Item("ReutersCashFlowStatementI").Connection = _
"URL;http://www.reuters.com/finance/stocks/incomeStatement?stmtType=CAS&perType=INT&symbol=" _
& Sheet10.Cells(5, 3) & Sheet1.Cells(34, 3)
Sheet10.QueryTables.Item("ReutersCashFlowStatementI").Refresh

'Copy Formats After Data Download
Sheets("HiddenSheet(2)").Range("B5:R295").Copy
Sheets("Financial Statements (Reuters)").Range("B9").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("C5").Select
----

The problem is that the 'copy' part of the code doesn't work. It appears that this part runs before the data is downloaded from the web. Is there anyway to pause or delay the code so that this works? I hope I've explained this properly.

Nelson

 

[John_w]

Try changing:

Sheet10.QueryTables.Item("ReutersCashFlowStatementI").Refresh

To:

Sheet10.QueryTables.Item("ReutersCashFlowStatementI").Refresh BackgroundQuery:=False

You want the Refresh to be synchronous so that it waits for the data to be retrieved before continuing with the copy part.

 

[NelsonX]

Hi John, thanks for your help - the macro now works as expected