Stumped on establishing web querries on MS Excel

[Chris]

I created an excel spreadsheet that captures prices from the web and what I
want to do is take these prices from the web queries I created and move them
into a workable list. The problem is when the queries are first created and I
hyperlink them into a list, it works great!!!! The problem occurs when a
couple of days later when I refresh, the data becomes skewed and some of the
lines shift. What should be a price is another piece of data from the web
query appears on the link. I have tried developing the queries both dynamic
and non dynamic but both ways I have problems with the data.

Ultimately, what I would like to do is have the queries build once,
hyperlinked into another sheet and then build a pivot table from the results.
If you have any insight or feedback on this, I would be most grateful. Like I
mentioned, if you do not know then I guess this cannot be done.

Best Regards,

Chris

 

[Joel]

There are 3 options in a query you can edit after you create the query. Not
sure if these will work but they appear to be releated to your problem. the
1st is the default setting

If the number of rows in the data range changes upon refresh

Insert cells for new data, delete unused cells
Insert entire rows for new data, clear unused cells
Overwrite existing cells with new data, clear unused cells


To get to these options in excel 2003 (not sure where they are in 2007)

1) Click any cell in the returned data from the query
2) Go to menu Data - Import External Data - Data Range Properties

the Data Range Properties will only be enabled when you click a cells in the
range of the query. The range of the query can be found by looking in the
Named Ranges by going to menu Insert - Names - Define. Then click on the
name of the query.

 

[Wes]

I am having the same problem - in both Excel 2003 and 2007. A revised an old
web query and now it will not overwrite the existing cells even though that
option has been selected.

The query used to import web data and overwrite the previous data. The
spreadsheet then uses the web data in formulas adjacent cells.

Now, a refresh results in blank cells being inserted to the right of the
imported data. Makes a mess of the spreadsheet.

I have tried with and without the "Overwrite existing cells with new data,
clear unused cells" but it does not seem to make any difference.

Any suggestions?

Thanks
Wes

 

[Joel]

did yo uupgrade your Internet explorer to version 8.0. It could be the
webbrowser or the webpage may have gotten updated. I 'e been having problems
with queries not returning all the data and had to resort to using VBA code
to extract data from webpages rather than using a query.

 

[Wes]

Explorer v8 could be part of the problem ... but I am not sure how Explorer
would impact the way Excel inserts the data into the spreadsheet.

 

[Joel]

I don't knoweither, but I wouldn't be surprized. When you setup the query
from the worksheet menu a IE application opens up. So there is some sort of
relationship between the two.

 

[Chris]

I want to thank you both for your responses but I have the latest version of
IE and it still does the same. Could this be some sort of glitch? I feel I am
close but just
can't get it. I tried the 3 ideas thatJoel suggested but a no go. I am
stumped!!!

 

[Joel]

You may need an earlier version of IE. The lastestt may be the cause of the
problem.

 

[Wes]

I am tending to agree with Joel. I am now experiencing the problem with one
computer running Office 2003, and another machine running Office 2007. Both
machines are running Explorer v8. The query ran fine on both platforms (even
with Explorer v8) until I made a slight change in the data I was importing.
Now when the data is imported, it adds new cells rather than overwriting -
regardless of which option I select for the data range.

Re-installing an earlier version of Internet Explorer does not sound like a
'preferred' fix but may be an effective work-around.

 

[Chris]

You had mentioned you have used VB code> In your experinece was that
sucessfull for you? I am almost at the point of scratching the approach of
doing the traditional querry and just hard code it. My other question is it
difficult to use VB code to extract the data needed from the web. Thanks guys
for both of your feedback. I am dertermined to figure this out one way or the
other.

Chris