Getting External Data with Bullying/Boundary problems

[TATrader]

Dear Esteemed Sirs and Sirettes,

I have this issue with a bully in the playground of my Excel workbook.
I tried to ignore and confront the bully, but both resulted in an
astounding failure comparable to or exceeding that of team USA in the
last soccer world cup.

The issue stems from using a VBA macro to get data from a webpage and
insert it into my ESPP calculation spreadsheet. The setup is as
follows:

- Windows XP
- Excel 2000
- Monitor, Eyes, etc.etc.

The workbook has not been locked, nor the cell in question. The VBA
macro I got and which used to work wonderfully well was from:
http://msdn.microsoft.com/library/d...y/en-us/odc_xl2003_ta/html/odc_xlWebquery.asp
<I think google groups will wrap that URL like a fajita>

The gory details (sparing you the bloody nose):
Added module and code snippet as given above and shown below with my
tweak:
Sub URL_Static_Query()

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/d/quotes.csv?s=MSFT&f=l1&e=.txt", _
Destination:=Range("d3"))

.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With
End Sub


Mind you, I changed quote data source. I also wished it to go into the
cell of D3. The surrounding cells calculate boring stuff such as ESPP
tax implications and TODAY() to figure out holding time.


The problem I have with the execution of this macro is that it doesn't
find D3 as a welcome home and decides to insert a whole column to the
left of the D column. This leaves all my other wonderful goodness
unchanged, since 'E' is the new 'D' and the cell functions are updated
accordingly.

Call me old-fashioned, but I wish column 'D' to remain where it is, BUT
with the updated information downloaded courtesy Yahoo! Inc.

Anyone know how to tweak this situation to enable me to update D3 with
the oh so desirable current data? I thank you in advance for your time
and apologize that I can't offer too much assistance within this forum.
I'd be happy to pre-offer assistance in exchange in the following
areas: C, C++, MFC, Windows Programming (within my capability), and
some C-based cell phone programming.

Best,
Me!~

 

[Don Guillett]

Try adding a line.
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True

You also are building a LONG list of external names with each query. You
should incorporate a name delete into the macro to prevent this. I also
suspect you want more than one symbol so you need a list loop. You may want
to goto xtraders group>join>download some of mine and other FREE files to do
this for you.

To Post a message, send it to: (e-mail address removed)
To Unsubscribe, send a blank message to:
(e-mail address removed)
To change address, first unsubscribe, then resubscribe to:
(e-mail address removed)

 

[millerj]

Don,

I very much appreciate the help and reference to more useful tools!

Best Regards,
Me!~

 

[TATrader]

Don,

Very much appreciate the help and reference to the useful tools!

Best Regards,
Me!~

 

[Don Guillett]

glad to help. You will like the xltraders group