Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Query
The following web query tries to pull 7967 stock prices from the internet and
puts them in my excel sheet formated. If the ticker symbol in the excel sheet is invalid it returns nothing or at least seems to. I think the spreadsheet is continually refreshing the data because it is very slow to respond when I open it and just try to scroll down or click on different cells. I just need to import the data when I want once. Can somebody tell me if this is the problem and how to solve it? If this isn't the problem, then what is the problem and how do I solve it? Private Sub CommandButton1_Click() Dim qtsQueries As QueryTables Dim qtQuery As QueryTable Dim test As String Dim x As Long x = 1 For Each cell In Sheet1.Range("b1:b7967") test = "http://finance.yahoo.com/q?s=" & cell.Value Set qtsQueries = ActiveSheet.QueryTables Set qtQuery = qtsQueries.Add _ ("URL;" & test, _ Application.Range("c" & x)) With qtQuery .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlOverwriteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlSpecifiedTables .WebFormatting = xlWebFormattingNone .WebTables = """yfncsubtit""" .WebPreFormattedTextToColumns = False .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = True .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False If .FetchedRowOverflow Then MsgBox "Your request returned too many results. " _ & "Please refine your search.", vbInformation, "Result Error" End If End With x = x + 1 Next Sheet1.Columns(3).Delete For Each cell In Sheet1.Range("c1:c7967") cell.Value = Right(cell, Len(cell) - IIf(InStr(cell, ":") 0, InStr(cell, ":") + 1, InStr(cell, ":"))) Next For Each cell In Sheet1.Range("c1:c7967") cell.Value = Left(cell, IIf(InStr(cell, " ") 0, InStr(cell, " ") - 1, InStr(cell, " "))) Next End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Query
Things that are slowing this down
1) You are sending over 7000 individual queries to the web do you need that many every time? 2) you are not error checking column B prior to sending blanks and non-ticker symbols still generate web query 3) Combine the two loops at the bottom Additionally try Application.screenupdating = false application.calculation = xlcalculationmanual "YOUR MAIN CODE HERE" Application.Calculate "YOUR 2 formating LOOPS, combined into one HERE" application.calculation = xlcalculationautomatic Application.screenupdating = true "John" wrote: The following web query tries to pull 7967 stock prices from the internet and puts them in my excel sheet formated. If the ticker symbol in the excel sheet is invalid it returns nothing or at least seems to. I think the spreadsheet is continually refreshing the data because it is very slow to respond when I open it and just try to scroll down or click on different cells. I just need to import the data when I want once. Can somebody tell me if this is the problem and how to solve it? If this isn't the problem, then what is the problem and how do I solve it? Private Sub CommandButton1_Click() Dim qtsQueries As QueryTables Dim qtQuery As QueryTable Dim test As String Dim x As Long x = 1 For Each cell In Sheet1.Range("b1:b7967") test = "http://finance.yahoo.com/q?s=" & cell.Value Set qtsQueries = ActiveSheet.QueryTables Set qtQuery = qtsQueries.Add _ ("URL;" & test, _ Application.Range("c" & x)) With qtQuery .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlOverwriteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlSpecifiedTables .WebFormatting = xlWebFormattingNone .WebTables = """yfncsubtit""" .WebPreFormattedTextToColumns = False .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = True .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False If .FetchedRowOverflow Then MsgBox "Your request returned too many results. " _ & "Please refine your search.", vbInformation, "Result Error" End If End With x = x + 1 Next Sheet1.Columns(3).Delete For Each cell In Sheet1.Range("c1:c7967") cell.Value = Right(cell, Len(cell) - IIf(InStr(cell, ":") 0, InStr(cell, ":") + 1, InStr(cell, ":"))) Next For Each cell In Sheet1.Range("c1:c7967") cell.Value = Left(cell, IIf(InStr(cell, " ") 0, InStr(cell, " ") - 1, InStr(cell, " "))) Next End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Query
The problem for me is less with the pulling of the data initially. I realize it will be slow to pull this many items at once. However, the problem is after the items are pulled from the web. When I open the file up it seems to be doing something. My assumption is that it is refreshing the data. So far I haven't been able to tell. I will try your suggestions. Thanks "Vacation's Over" wrote: Things that are slowing this down 1) You are sending over 7000 individual queries to the web do you need that many every time? 2) you are not error checking column B prior to sending blanks and non-ticker symbols still generate web query 3) Combine the two loops at the bottom Additionally try Application.screenupdating = false application.calculation = xlcalculationmanual "YOUR MAIN CODE HERE" Application.Calculate "YOUR 2 formating LOOPS, combined into one HERE" application.calculation = xlcalculationautomatic Application.screenupdating = true "John" wrote: The following web query tries to pull 7967 stock prices from the internet and puts them in my excel sheet formated. If the ticker symbol in the excel sheet is invalid it returns nothing or at least seems to. I think the spreadsheet is continually refreshing the data because it is very slow to respond when I open it and just try to scroll down or click on different cells. I just need to import the data when I want once. Can somebody tell me if this is the problem and how to solve it? If this isn't the problem, then what is the problem and how do I solve it? Private Sub CommandButton1_Click() Dim qtsQueries As QueryTables Dim qtQuery As QueryTable Dim test As String Dim x As Long x = 1 For Each cell In Sheet1.Range("b1:b7967") test = "http://finance.yahoo.com/q?s=" & cell.Value Set qtsQueries = ActiveSheet.QueryTables Set qtQuery = qtsQueries.Add _ ("URL;" & test, _ Application.Range("c" & x)) With qtQuery .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlOverwriteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlSpecifiedTables .WebFormatting = xlWebFormattingNone .WebTables = """yfncsubtit""" .WebPreFormattedTextToColumns = False .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = True .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False If .FetchedRowOverflow Then MsgBox "Your request returned too many results. " _ & "Please refine your search.", vbInformation, "Result Error" End If End With x = x + 1 Next Sheet1.Columns(3).Delete For Each cell In Sheet1.Range("c1:c7967") cell.Value = Right(cell, Len(cell) - IIf(InStr(cell, ":") 0, InStr(cell, ":") + 1, InStr(cell, ":"))) Next For Each cell In Sheet1.Range("c1:c7967") cell.Value = Left(cell, IIf(InStr(cell, " ") 0, InStr(cell, " ") - 1, InStr(cell, " "))) Next End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert hard coded query criteria to Parameter Query | Excel Discussion (Misc queries) | |||
Excel 2007 / MS Query - editing existing query to another sheet | Excel Discussion (Misc queries) | |||
Anyone Else Use Database Query to Query Another Sheet in the Same Excel Workbook? | Excel Discussion (Misc queries) | |||
How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) | |||
Problem with .Background Query option of ODBC Query | Excel Programming |