Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Connection Proliferation
I cobbled together a set of macros (below) to periodically query Yahoo
finance and download quotes. This spreadsheet would ideally run 24/7. I noticed after a few weeks that it grew larger and slower. I discovered, under Data Connections, about 7000 identical connections that had been created (and saved). Just deleting them will be a massive chore, but avoiding them entirely would be desirable. Obviously I'm doing something wrong here. Any help appreciated. Code begins: Public RunWhen As Double Public PauseStuff As Boolean Public Const cRunIntervalSeconds = 120 ' two minutes Public Const cRunWhat = "GetData" ' the name of the procedure to run Sub StartTimer() RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds) Application.OnTime EarliestTime:=RunWhen, Procedu=cRunWhat, Schedule:=True 'PauseStuff = True 'if true, getdata End Sub Sub GetData() Dim QuerySheet As Worksheet Dim DataSheet As Worksheet Dim qurl As String Dim i As Integer On Error Resume Next Application.ScreenUpdating = False Application.DisplayAlerts = False Application.Calculation = xlCalculationManual If Not PauseStuff Then GoTo OnPause Set DataSheet = Worksheets("Sheet2") DataSheet.Activate Range("A30").CurrentRegion.ClearContents Range("b2").Select i = 1 yahoourl = "http://quote.yahoo.com/d/quotes.csv?s=^GSPC + " + ActiveCell.Value ActiveCell.Offset(1, 0).Select While ActiveCell < "" yahoourl = yahoourl + "+" + ActiveCell.Value ActiveCell.Offset(1, 0).Select Wend yahoourl = yahoourl + "&f=nl1c" QueryQuote: With ActiveSheet.QueryTables.Add(Connection:="URL;" & yahoourl, Destination:=Range("A30")) .BackgroundQuery = True .TablesOnlyFromHTML = False .Refresh BackgroundQuery:=False .SaveData = True End With Range("a30").CurrentRegion.TextToColumns Destination:=Range("a30"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _ Semicolon:=False, Comma:=True, Space:=False, other:=False Application.Calculation = xlCalculationAutomatic Application.DisplayAlerts = True Columns("A:A").ColumnWidth = 28# 'Cells(2, 3).Select OnPause: StartTimer End Sub Sub StopTimer() On Error Resume Next Application.OnTime EarliestTime:=RunWhen, Procedu=cRunWhat, Schedule:=False End Sub Sub Pausequery() PauseStuff = Not PauseStuff Worksheets("Yahoo").Activate If Range("A1") = "" Then Range("A1") = "WEB QUERY PAUSED" Else Range("a1") = "" End Sub |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
When I save a workbook(2007) w/ a data connection to htm no data? | Excel Discussion (Misc queries) | |||
Data Connection | Excel Discussion (Misc queries) | |||
Importing:Data Connection Wizard Doesn't see Source Data - No Impo | Excel Discussion (Misc queries) | |||
always recheck data connection library for latest connection strin | Excel Discussion (Misc queries) | |||
always recheck data connection library in MOSS for latest data sou | Excel Discussion (Misc queries) |