Web Query and table?

[Donal McWeeney]

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

 

[Wei Lu [MSFT]]

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.

 

[Donal McWeeney]

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

 

[Wei Lu [MSFT]]

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.

 

[Donal McWeeney]

I've mailed you the details you requested.

Thanks

Donal

 

[Wei Lu [MSFT]]

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.

 

[Donal McWeeney]

Hi Wei,

Thanks for the update. I will try what you suggested.

Thanks

Donal

 

[Wei Lu [MSFT]]

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.

 

[Donal McWeeney]

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