Connecting a Pivot Table to .Tab Data file ** Minimise the connexion setup hassle

[Guest]

Hi,
In Excel 2007 :
I have a Pivot Table that aggregates data from a .tab data file using the
Microdsoft ODBC Text Driver.
I am looking for the simplest most direct method for establishing the
connexion.
All the connections are on our LAN.

I have a number of such data files. They will all be located on the same
server share, locally named as X:.
I have a number of such Pivot Tables. They will all be located on the same
server share locally named as V:.
All user have access to these server shares via a logon script.

The data file has no column headers, so I have added colum headers via the
ODBC tool.
That's unavoidable.
I can now see the header data in the schema.ini file in X:.

***** What I would like to do is minimise the connexion setup hassle.
******
I have set up a connection from the Pivot table to the data.
The Pivot Table is fucnctionning correctly, i.e. reading and updating.

I was used to doing this in Excel 2003 with MSQuery and when I finally
mastered that, I upgraded to Excel 2007 and things became more comlicated.
Apparently.

When I delve into the connexion properties I can see the SQL request and a
reference to an odc file.
Do I have to use an .odc file?
I though that the Excel file could store a referece to the drive X: as if it
were a database file and then referece the text files in X: as if they were
tables.

If it is possible to skip the odc file? If so, could someone please explain
how this is done?
Or show me the simplest and most direct method for setting up these
connections.

TIA

Rosalie

 

[Roger Govier]

Hi Rosalie

The same functionality does exist in 2007.
On the Data tab>group Get External Data>From other Sources>the last item is
MS Query which will take you through the identical wizard as in 2003

 

[Guest]

Thanks Roger,
Fantastic.
I'll try and set it up and let you know.

Rosalie

 

[Guest]

OK.
That's it.
Am I right in thinking that setting up the connexion this way avoids the
necessity of having an .odc file?
I mean, is Excel not creating an .odc (or some other kind of file) behind my
back ?
Or am I OK with
1. my data file .tab
2. my schema.ini
3. my excel sheet containing my PT.

Is that the minimum yet sufficient stuff I need for my PT to work ?

Rosalie.