Hi,

I need to generate some IQY files that will get XML data (I'm generating this XML so I can modify the complete schema and hierarchy of this XML file). But when opening this IQY file, I wanted the user to have a formatted table (in a dataTable) with still the connection to the distant Data (the XML file).

Currently, I can generate a simple IQY that import the data without formatting the table:

WEB

1

http://localhost:27312/Poc/Export

Selection=EntirePage

Formatting=None

PreFormattedTextToColumns=True

ConsecutiveDelimitersAsOne=True

SingleBlockTextImport=False

DisableDateRecognition=False

DisableRedirections=False

But I need to generate a formatted table (a datatable), like in the SharePoint queries that we used to have in another application on SharePoint which was like:

WEB

1

https://site.com/SP_API/owssvr.dll?XMLDATA=1&List={CE127285-B596-4EB4-B5DA-C442E387E92D}&View={C7686794-C676-4053-876F-A6BADD3553A0}&RowLimit=0&RootFolder=%2fARP%2fLists%2fBusinessNeed

Selection={CE127285-B596-4EB4-B5DA-C442E387E92D}-{C7686794-C676-4053-876F-A6BADD3553A0}

EditWebPage=

Formatting=None

PreFormattedTextToColumns=True

ConsecutiveDelimitersAsOne=True

SingleBlockTextImport=False

DisableDateRecognition=False

DisableRedirections=False

SharePointApplication=https://site.com/SP_API

SharePointListView={C7686794-C676-4053-876F-A6BADD3553A0}

SharePointListName={CE127285-B596-4EB4-B5DA-C442E387E92D}

RootFolder=/API/Lists/BusinessNeed

Is there a way to improve my IQY file to have such a result, or is it possible to generate web services that acts as SharePoint to fulfil these lines:

SharePointApplication=https://site.com/SP_API

SharePointListView={C7686794-C676-4053-876F-A6BADD3553A0}

SharePointListName={CE127285-B596-4EB4-B5DA-C442E387E92D}

What is also strange is that when I do a manual importation of the XML data (with the "Data>Get Data From Web" tool), the table is perfectly formated as needed (in a datatable), but when I save the IQY query with that tool, the query doesn't formats the Table...  :s

Thanks for your help  ! =)

Xavv

Hi,
From your description, I find the definition of a Web Query File.
It is a text file that contains from one to four lines of text where each line of text is separated by a carriage return. Web query files can be created in any text editor, such as Notepad, and they are saved with the .iqy extension.
With the document, I recommend you to Create Web Query (.iqy) Files again with the following link:
http://support.microsoft.com/kb/157482
For more detail information, please refer to the following link:
Getting Started with Sample Excel Web Queries
http://www.vertex42.com/News/excel-web-query.html
How to Create Excel Dynamic Web Query
http://excelwebquery.blogspot.in/2011/09/how-to-create-excel-dynamic-web-query.html

Regards,
George.zhao

Hi George.zhao,

Thanks for your answer ! But I've already done all of that and it works on the basic way, not on the way I need it. 

Maybe some picts would be better to explain my needs :

With a simple IQY file, this is what I get :

If I try to format this table, I will lose all the data-connections and therefore I won't be able to refresh the content of the table.

In fact, the result I need is the same as if I'm using the Excel function "Data > From Web" that give a refreshable and formated datatable:

Now I really think that using IQY files makes impossible to get such a result without having a Sharepoint data source.

Another way would be to generate the Excel file with OpenXML libraries... But I need to find how to create a dataconnected table... Any advises? =)

Thanks,

Xavv