Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Query and table?
Hi,
I've been trying to get excel 2007 to do an web query using an iqy file and this piece works. However if I then select the data and try and format as a table it gives the following warning: Your selection overlaps one or more external data ranges. Do you want to convert the selection to a table and remove all external connections? If I select yes it breaks the external connections and the data is not refreshable. Is it possible to have an external query and to get its data into a table? Doing so makes it much easier to manipulate the data into a pivot table etc I'm not sure how they do it but the SharePoing list action "Export to Spreadsheet" does something similar to what I want to do. Thanks Donal |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Query and table?
Hello Donal,
When you export the Sharepoint List, it did generate a .IQF file. So what did you do to format the table? Please let me know in what scenario you will face the issue. Thank you! Sincerely, Wei Lu Microsoft Online Community Support ================================================== When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ================================================== This posting is provided "AS IS" with no warranties, and confers no rights. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Query and table?
Hi Wei,
Thanks for the reply. What I want to do is have a link on a web page that will download a dynamically .IQY file. The .IQY will contain a link back to another aspx page that will download the xml in rowset format. In playing with this I stuck an xml file in a WSS document library and have a .IQY file on my local hard drive. The format of the .IQY is: WEB 1 http://servername/sites/site/subsite/DocLib/data.xml When I open the iqy in excel it loads the data from the data.xml fine - and I can refresh the data too. The problem is when I try and make the data into a table it gives that error. To format as a table I selected the data, clicked "Format as Table" and selected the table style. The format as table dialog comes up and the "my table has headers" checkbox is checked (as it should). Click ok and you get the prompt to break the connection which is not what I want. Any ideas greatly appreciated. Thanks Donal |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Query and table?
Hello Donal,
Could you please send me a screenshot for the error and the steps you do in the Excel? Sincerely, Wei Lu Microsoft Online Community Support ================================================== When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ================================================== This posting is provided "AS IS" with no warranties, and confers no rights. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Query and table?
I've mailed you the details you requested.
Thanks Donal |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Query and table?
Hello Donal,
Thank you for your email. I did reproduce your issue. Unfortunately, this issue is by design. Since Format as a table will create a source and overlap the existing source, you could not format the web query. Another option is use the XML in the Developer Tab which you could import the xml from Sharepoint directly as a XML source. The XML source will create a table and you don't need to format it again. Please have a try and let me know the result. Sincerely, Wei Lu Microsoft Online Community Support ================================================== When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ================================================== This posting is provided "AS IS" with no warranties, and confers no rights. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Query and table?
Hi Wei,
Thanks for the update. I will try what you suggested. Thanks Donal "Wei Lu [MSFT]" wrote in message ... Hello Donal, Thank you for your email. I did reproduce your issue. Unfortunately, this issue is by design. Since Format as a table will create a source and overlap the existing source, you could not format the web query. Another option is use the XML in the Developer Tab which you could import the xml from Sharepoint directly as a XML source. The XML source will create a table and you don't need to format it again. Please have a try and let me know the result. Sincerely, Wei Lu Microsoft Online Community Support ================================================== When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ================================================== This posting is provided "AS IS" with no warranties, and confers no rights. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Query and table?
Hello Donal,
Did you get any progress? Anything I can do, please feel free to let me know. Sincerely, Wei Lu Microsoft Online Community Support ================================================== When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ================================================== This posting is provided "AS IS" with no warranties, and confers no rights. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Query and table?
Hi Wei,
Not really any progress - just more bewilderment!!! I started a new thread titled "Xml data import weirdness" if you want to read my confusion there! Thanks Donal |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple Table Query | Excel Worksheet Functions | |||
Pivot Table Query | Excel Programming | |||
VB way to remove the query from a Query Table? | Excel Programming | |||
How tp query a spreadsheet table? | Excel Programming | |||
Linking a table in Access to a table in Excel using MS Query | Excel Programming |