Want external data to update into a Table?

[ddub25]

I have created an external data connection to a webpage that imports informtaion from the webpage into Excel every 60 mins. I tried to create a table encompassing the information, but Excel warned me that if I did, the external data link would be lost.

Is it not possible to turn a webquery (that continuosly updates from a website) into a table without disrupting the connection?

Dan

 

[chuckchuckit]

How are you trying to "create a table encompassing the information" that says the connection will be lost?

Also are you doing the query with 60 minute refresh?

 

[ddub25]

I highlight all the information that's imported by the web query including the header row. On the 'Insert Ribbon' (Excel 2007) I then click 'Table'. A message pops up saying "Your selection overlaps one or more data ranges. Do you want to convert the selection to a table a remove all external connections?"

I click 'NO' because I want the data connection to remain, but doing this I do not get a table.

I have the web query set to refresh every 5 mins.

Dan

 

[chuckchuckit]

Dan,

I am not experienced with using the Insert, Table. I know that if you try to modify the query area in any way you will cause problems.

I would suggest just copying the data from the query area first. Copy just the values to a different area that is not part of any range, or especially any part of the query area that is being imported. Then I would imagine that your next step of selecting this new copied area and then using "Insert, Table" would likely work.

I do a lot of querying, and if I remember right copying that area might be trouble too when query is active (not sure about that). So what I do to avoid any problems is I just have a whole other section that is a copy of the query area using formulas in cells. Then when query updates, those cells will automatically change their values too.

Such as if cell A1 is part of a query area, you could have cell A10 have the formula "=A1". Then you could work with cell A10 instead of A1.

Chuck


Chuck

 

[ddub25]

Thanks Chuck, that is what I thought I'd have to do. Just thought I'd see if it could be done with re-referencing. Thanks again.

Dan