Query retrieving data from the web - excel 2003

G

Guest

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.
 
T

Tim Williams

If you post your exsiting code you'll increase the cance of getting useful suggestions...
 
R

Randy Harmelink

What you're asking about will depend a lot on the specifics of what you
are doing and what you need to do. Personally, other than simple
requests, I find web queries to be somewhat unreliable. As a result, I
wrote an add-in that does most of my processing needs using
user-defined functions.

The add-in can be used to get stock quotes from Yahoo. It can also
retrieve historical quotes, even individual data items, from the web.
It is also open source, so you can see what it is doing to get the
data. I support it at:

http://finance.groups.yahoo.com/group/smf_addin/

The add-in, documentation, and sample templates can be found in the
files area.
 
G

Guest

===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


====
I would like to have this loop through an entire list of rows so that I
don't have to manually run the macro for each row.

Thank you!
 

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

Excel Web Query 2
Retrieving Data From Excel 7
Extract data from web 1
Excel Web Query 1
Importing data from Web using Excel 3
download info from a web site to Excel 3
Importing data from the web (script page) 5
Progressbar on userform for waiting querytable retrieving data from web 4

Top