Excel Web Query

B

Bryan

I have a spreadsheet that has two macros in excel. I use the first marcro to
retrieve data from a web page (stock quotes) using . The second macro is
used to copy the data and paste selective parts of the retrieval from the
query.

My problem is that I have a large list of data to go through and I would
like to start the process and have excel go through each line and complete
the process without me having to run each macro individually. When I tried
to combine the two queries into one, the second query will not wait for the
web page data to flood before it runs. When I place a loop or wait action in
place, the data does not come over from the web.

Can someone point me in the right direction? I am not extremely proficient
in this but can follow directions and appreciate any help.
===This is the first Macro===

Sub Stock001()

Dim strStock As String
Dim DesiredRow As Long
Dim PositionOfSpace As Long

'Init Vars:
DesiredRow = Application.ActiveCell.Row

strStock =
Workbooks(Application.ActiveWorkbook.Name).Sheets("StockData").Range("A" &
DesiredRow)

If strStock <> "" Then

'Start by clearing the data area:
Columns("I:J").Select
Selection.Delete Shift:=xlToLeft

'Now use the Web Query to fetch the data:
With
ActiveSheet.QueryTables.Add(Connection:="URL;http://finance.yahoo.com/q?s=" &
strStock, Destination:=Sheets("StockData").Range("i1"))
'With
ActiveSheet.QueryTables.Add(Connection:="URL;http://finance.yahoo.com/q?s=",
Destination:=Sheets("StockData").Range("i1"))


.Name = "http://finance.yahoo.com/q?s=" & strStock
'.Name = "http://finance.yahoo.com/q?s=" & strStock
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False 'was True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "12"
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = False
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = True
.Refresh BackgroundQuery:=True


End With

Else

MsgBox "Please select the row you are trying to update before clicking
the button."

End If

Range("A" & DesiredRow).Select

End Sub

===This is the second macro which then leads back to the first===

Sub CompleteProcess()

Dim DesiredRow As Long
DesiredRow = Application.ActiveCell.Row



'Paste the values where they belong
Sheets("StockData").Select
Range("I1").Select
Selection.Copy
Range("E" & DesiredRow).Select
ActiveSheet.Paste
Range("J1").Select
Application.CutCopyMode = False
Selection.Copy
Range("F" & DesiredRow).Select
ActiveSheet.Paste
Columns("E:F").Select
Columns("E:F").EntireColumn.AutoFit
Columns("I:J").Select
Selection.Delete Shift:=xlToLeft

'Clear the garbage left behind
Columns("I:L").Select
Selection.Delete Shift:=xlToLeft
Range("A" & DesiredRow + 1).Select
Application.Run "Stocks.xls!Stock001"

End Sub
 
N

NickHK

Bryan,
Change
..Refresh BackgroundQuery:=True
to
..Refresh BackgroundQuery:=False

Than the query will run synchrously - the code will wait until the query has
finished.

NickHK
 
B

Bryan

Thanks! That worked!

You are the man!

NickHK said:
Bryan,
Change
..Refresh BackgroundQuery:=True
to
..Refresh BackgroundQuery:=False

Than the query will run synchrously - the code will wait until the query has
finished.

NickHK
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top