Web Query results VBA

[fmcampos]

Hi, I am running the following query just fine. The problem is that i want the resulting information to be placed on a single cell and excel seems to always include at least two rows in the resulting range (which therefore erases values on the row below the query). Anyone one knows how to limit these results to ocupy a single cell regardless of it's contents?

Also everytime I try reacreating this Query, instead of updating the named ranges created by the query, I keep getting more ranges such as "Query_1", "Query_2" and so on.. I tried delteing them automatically before recreating the query, but with no success..

Can anybody help please?

For Linha = 5 To UltimaLinha

    
    Ticker = Range("C" & Linha).Value
    Pais = Range("B" & Linha).Value
    Destino = ("R" & Linha)

    QueryTables(Linha - (Linha - 1)).ResultRange.Delete
    QueryTables(Linha - (Linha - 1)).Delete
    
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://quote.yahoo.com/d/quotes.csv?s=" & Ticker & Pais & "&d=t&f=sl1d1t1c1ohgvj1pp2wern""", _
        Destination:=Range(Destino))
        .Name = ("Cotacao-" & Ticker)
        .FieldNames = False
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = True
        .BackgroundQuery = True
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 1
        .WebSelectionType = xlAllTables
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = False
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = True
        .Refresh BackgroundQuery:=False
    End With

Next Linha

[fmcampos]

Nobody??

[ChrisM]

can't help you on the extra row being added

As far as the named ranges being added, that's happening because you keep creating new web queries every time with the .Add method:

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://quote.yahoo.com/d/quotes.csv?s=" & Ticker & Pais & "&d=t&f=sl1d1t1c1ohgvj1pp2wern""", _

You don't need to make new queries every time. Make them once, then just update the connection and refresh each one:

ActiveSheet.QueryTables(1).Connection:= _
"URL;http://quote.yahoo.com/d/quotes.csv?s=" & Ticker & Pais & "&d=t&f=sl1d1t1c1ohgvj1pp2wern""", _

ActiveSheet.QueryTables(1).refresh

Then you can delete your old code lines:

QueryTables(Linha - (Linha - 1)).ResultRange.Delete
QueryTables(Linha - (Linha - 1)).Delete

The only thing left is to determine if you need a fixed amount of queries or if the query count is dynamic. If your query count is dynamic, then you WILL need to add new queries as needed.

[fmcampos]

Thank you for your reply.

Refreshing the query would work for me. I could even set the refreshperiod property instead of manually refreshing the query.

The problem is that after refreshing the query i need another macro to run to edit the string obtained with this refresh. I tried using the AfterRefresh method, but nothing happens once the query refreshes, not even if I click on refresh...

I'm using

Private Sub QueryTable_AfterRefresh()

Macro Code here

end sub

[ChrisM]

Call your macro code right after you refresh the query (as in...inside the same procedure)

set your querytable to backgroundquery=false that way the next line of code won't run until the new data is in.

[fmcampos]

Thanks!

Not exactely what I did, but your suggestion gave me the insight to achieve what I wanted!


Anyone else know the answer to my second question? How to get all query results into a single cell instead of splitting it between rows?

[fmcampos]

Still looking for this answer should anyone know it...

How to get all query's result in a single cell rather then in multiple rows..

[ChrisM]

Can you clarify what you want to happen?

Do you want all rows and columns of data put into one cell, or just one column for each row, or what?

What does your query output look like? How do you want it to look?

[fmcampos]

Yes thanks for replying.

I'm doing a webquery to download online stock quotes to excel. As many other people I'm using yahoo as my source. Below is my code:

    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://quote.yahoo.com/d/quotes.csv?s=" & Ticker & Pais & "&d=t&f=sl1d1t1c1ohgvj1pp2wern" _
        , Destination:=Range(Destino))
        .Name = ("Cotacao" & Ticker)
        .QueryType = xlTextImport
        .TextFileStartRow = 1
        .FieldNames = False
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = True
        .BackgroundQuery = True
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = False
        .EnableRefresh = True
        .RefreshPeriod = 1
        .WebSelectionType = xlSpecifiedTables
        .WebTables = "2"
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = False
        .WebConsecutiveDelimitersAsOne = False
        .WebSingleBlockTextImport = True
        .WebDisableDateRecognition = False
        .WebDisableRedirections = True
        .Refresh BackgroundQuery:=True
        .WebSingleBlockTextImport = True
    End With

If you visit this link in your browser you'll be able to see that the entire results from Yahoo are returned into a single line, however, when i use the webquery to get this string, the query uses two rows to place the data: one with the actual data and a second blank row.

What I wanted to do is to get just a single row of information with the query, so I can stack more queries like this one below one another.

I tried adding a delete row command after running the query, but since I'll be using the query auto refresh option, whenever the query refreshes I end up overwriting anything on the row directly below it....

[ChrisM]

what is Pais variable?

could you post the complete URL without variables?