| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Query retrieving data from the web - excel 2003
|
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Query retrieving data from the web - excel 2003
Thanks, any help is appreciated.
"Don Guillett" wrote: I won't have time to look at this until tomorrow but you would like my free files in the yahoo xltraders group under the author, donaldb36. Just goto the groupjoinand download http://tech.groups.yahoo.com:80/group/xltraders/ -- Don Guillett SalesAid Software "Bryan" wrote in message ... ===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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| importing/linking data from an Access 2003 Query to an Excel 2003 | Excel Discussion (Misc queries) | |||
| Retrieving Data in excel | Excel Worksheet Functions | |||
| Retrieving the SQL query of the external data range | Excel Programming | |||
| Problem Code: Retrieving Stored Access 03 Query | Excel Discussion (Misc queries) | |||
| Retrieving FoxPro Data using Query | Excel Programming | |||
Linear Mode
