Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Stumped on establishing web querries on MS Excel
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Stumped on establishing web querries on MS Excel
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. "Chris" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Stumped on establishing web querries on MS Excel
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" wrote: 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. "Chris" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Stumped on establishing web querries on MS Excel
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. "Joel" wrote: 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. "Chris" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Stumped on establishing web querries on MS Excel
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" wrote: 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. "Joel" wrote: 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. "Chris" wrote: 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Stumped on establishing web querries on MS Excel
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. "Wes" wrote: 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" wrote: 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. "Joel" wrote: 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. "Chris" wrote: 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Stumped on establishing web querries on MS Excel
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!!! 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. "Wes" wrote: 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" wrote: 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. "Joel" wrote: 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. "Chris" wrote: 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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Stumped on establishing web querries on MS Excel
You may need an earlier version of IE. The lastestt may be the cause of the
problem. "Chris" wrote: 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!!! 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. "Wes" wrote: 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" wrote: 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. "Joel" wrote: 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. "Chris" wrote: 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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Stumped on establishing web querries on MS Excel
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. "Joel" wrote: You may need an earlier version of IE. The lastestt may be the cause of the problem. "Chris" wrote: 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!!! |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Stumped on establishing web querries on MS Excel
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 "Chris" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Establishing Excel default toolbars | Excel Discussion (Misc queries) | |||
URGENT HELP: Establishing a Training Matrix | Excel Discussion (Misc queries) | |||
Stumped with Excel | Excel Worksheet Functions | |||
Re-establishing Links | Excel Discussion (Misc queries) | |||
Re-establishing Links | Links and Linking in Excel |