WebQuery through an Excel Macro 1

[pkrmott]

This topic has been covered extensively in other posts, but I have a peculiar problem that I cannot solve. On a spreadsheet, I create a webquery using a macro.

Everytime I use the:
"Set QT = ActiveSheet.QueryTables.Add(Connection:=totalline, Destination:=Range("A1"))"
command, it takes 15-20 seconds to perform that one line. (NOTE: the "totalline" variable is a website, for ex: "URL;

http://finance.yahoo.com/q/ks?s=AA",

and QT is defined as a QueryTable type). Stepping through the code, this seems odd because it is not actually querying the website at this point, it is only setting up the querytable command.

The call to the webquery is as follows:
"
With QT
.Name = "ks?s=AA"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = """yfncsubtit"",17,22,25,28,31,34,37,45,48,51"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
"

I am completely mystified about why this would take so long. I have used different internet connections and different computers, and the result is the same... a 20 second wait on the QT = activesheet.querytable.add command. I would greatly appreciate any thoughts you have.

-PM

 

[xlbo]

why are you ADDING a querytable each time - why not just use the one there and manipulate it ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

and...

depending on how many times you have executed the QueryTables.Add command, you ought to DELETE all but ONE...

dim i as integer
for i = 1 to activesheet.querytables.count - 1
  activesheet.querytables(i).delete
next

Skip,

_[red][/red]

 

[pkrmott]

First, let me say that I really appreciate the quick responses.

I think this was one of those scenarios where I couldn't see the forest for the trees. About a month ago I had tried the method of using 1 webquery and simply changing the "Connection" property, but for some reason I got it into my head that I couldn't change that property. In retrospect... EASY.

The line of code that allows you to change the connection property (for the scenario I outlined above) is:
QT.Connection = totalline

I'm embarrassed that I didn't come to this on my own, but greatly appreciate the kick in the pants.

Thanks again,
-PM

 

[xlbo]

do not be embarrassed - it is far easier for others to see what you can't - especially if it is something you have been concentrating on a lot - I have done it many times myself - can't see the wood for the trees !!

It is always better to ask and find out than not to ask and forever be wondering

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question