Easiest way to import sharpoint list data into excel programatically

[Jay A. Ritchie]

I understand that I can use VBA code to call SharePoint web services and
get that data into Excel. I was wondering if there's a more direct way
using Office/Excel objects to do the same thing. Seems like depending on
how you go about things, Excel uses a couple of different mechanisms to
get data from SharePoint.

So far I've identified the following:

1. Use Excel VBA code to call SharePoint's Data Retrieval Web Services.
2. Export a list from SharePoint to Excel which uses a iqy file.
3. Use Excel's Data Import feature which uses a data retrievel service
definition (UXDC file) and Excel's ActiveWorkbook.XmlImport method. (I
haven't found documentation for this methods explicit support for UXDC
files.)

What I want to do is aggregate data from multiple SharePoint lists
inside Excel and really just want to know which way is the best way to
do this.

Thanks,
Jay

[Nock]

Jay, how's it going. I've been looking for the same solution to this for
sometime now and all I've come up with are your posts across the web! Seems
as thou there's isn't a definitive answer as yet.

Please update the forum or contact me directly if you find any new info. I
will do likewise!

[email protected]

Many thanks,

Nock

"Jay A. Ritchie" wrote:

> I understand that I can use VBA code to call SharePoint web services and
> get that data into Excel. I was wondering if there's a more direct way
> using Office/Excel objects to do the same thing. Seems like depending on
> how you go about things, Excel uses a couple of different mechanisms to
> get data from SharePoint.
>
> So far I've identified the following:
>
> 1. Use Excel VBA code to call SharePoint's Data Retrieval Web Services.
> 2. Export a list from SharePoint to Excel which uses a iqy file.
> 3. Use Excel's Data Import feature which uses a data retrievel service
> definition (UXDC file) and Excel's ActiveWorkbook.XmlImport method. (I
> haven't found documentation for this methods explicit support for UXDC
> files.)
>
> What I want to do is aggregate data from multiple SharePoint lists
> inside Excel and really just want to know which way is the best way to
> do this.
>
> Thanks,
> Jay
>