ExcelBanter - Query retrieving data from the web

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Query retrieving data from the web - excel 2003 (https://www.excelbanter.com/excel-programming/375632-query-retrieving-data-web-excel-2003-a.html)

Query retrieving data from the web - excel 2003

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.

Query retrieving data from the web - excel 2003

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

--
Tim Williams
Palo Alto, CA

"Bryan" wrote in message ...
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.

Query retrieving data from the web - excel 2003

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.

Bryan wrote:
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.

Query retrieving data from the web - excel 2003

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

"Don Guillett" wrote:

Post your code or send me a workbook to look at

--
Don Guillett
SalesAid Software

"Bryan" wrote in message
...
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.

Query retrieving data from the web - excel 2003