[Answered] Open xls and keep ODBC query

[StroppyMoppy]

Hi all

I am a newbie to OOo and I am currently evaluating it. The problem I have is that we currently have a large number of excel spreadsheets that pull information from an external data source (set up suing Microsoft query). When I open these up in Calc the data is all there but it won't refresh - I have set up the ODBC connector and I am looking at a way to use the existing queries in the xls. Hope this makes sense.

regards

Pete

[Villeroy]

OOo can not reuse Excel's connectors since OOo runs on different operating systems whereas Excel can assume always the same environment. You need to create a Base document as connector. Then recreate or copy&paste the queries to the Base document. Finally you can drag&drop any table or query from the datasource window into Writer or Calc.
File>New>Database...
[X] Connect to existing database, type: ODBC
[X] Register database (expose it as datasource in other components)
Store the database document (*.odb) and add queries.

Hit F4 in Calc, drag and drop from the left pane onto a cell, notice the linked database range (Menu:Data>Define...) and it's additional properties.
You can also create data pilots (pivot tables) from datasources.

[StroppyMoppy]

That is what I thought - I just assumed that because I was a noob I was missing something. Unfortunately it mean we will have to continue with excel (at least for the moment) as there a few hundred different spreadsheets with slightly different queries and I don't have the time to start redoing all of them.

Thanks for the answer.

Pete