Re: [ExcelVBA] ActiveSheet.QueryTables.Add

 

Hi David

QueryTables.Add inserts a column every time you run it. This seems to be by design.

The way to handle this is to run the query once only after building the symbol string in a loop that looks down column A until it hits an empty cell.

Your  myurl string should look like "http://download.finance.yahoo.com/d/quotes.csv?s=GE+MSFT+HNZ+IBM+ETC&f=l1wde"

I think this is what you were trying to end up with anyway ?

Regards

Derek +++

>________________________________
> From: David <who1012000@yahoo.com>
>To: ExcelVBA@yahoogroups.com
>Sent: Wednesday, 2 May 2012, 18:57
>Subject: [ExcelVBA] ActiveSheet.QueryTables.Add
>
>
> 
>Hello Group,
>
>Hope everyone is having a good day. I am running a query on a list of symbols. Code is as follows:
>Sym = Range("A" & ActiveCell.Row).Value
>myurl = "http://download.finance.yahoo.com/d/quotes.csv?s=" & Sym & "&f=l1wde"
>Set qt = ActiveSheet.QueryTables.Add( _
>Connection:="URL;" & myurl, _
>Destination:=Range("K" & ActiveCell.Row))
>With qt
>.Refresh BackgroundQuery:=False
>End With
>qt.Delete
>Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, Comma:=True, FieldInfo _
>:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), TrailingMinusNumbers:=True
>ActiveCell.Offset(1, 0).Select
>
>Everything is running fine, but the query is inserting a column each time it runs, thus it displaces everything over one column above it. I have tried to figure out what parameter to add, so that it will not do this, but am failing to figure it out.
>
>Any ideas would be appreciated.
>
>Thanks,
>David
>
>
>
>
>

[Non-text portions of this message have been removed]

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (3)

Recent Activity:

• New Members 4

Visit Your Group

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___