Excel Pivot update from Access

[tbieri]

Howdy,

I have a workbook with one pivot table created from an
Access DB. I would like to share the workbook, but find
that other users have to navigate to the database in order
to refresh data. I thought the issue was around the query
file, and have written VBA code to look for the query file
in the "C:\Documents and Settings\username\Application
Data\Microsoft\Queries\Query_name.dqy" and put a copy there
is one does not exist; but that does not seem to address
the issue. In fact, if I refresh the data on another users
computer and navigate to the database (which maps the
drive), the query file is not apparantly needed.

I would rather point to the \\server\path versus having
manually point to the file and mapping the drive. What
would the VBA code look like to accomplish this?

What is the purpose of the query file?

TIA
regards,
Tim

 

[keepITcool]

there's no need to save the query
probably better to just keep it in the excel file.

editing the connection string of the querytable
may be a bit of a hassle..
but via VBA it IS accessable.
(a normal albeit delimited string.

you dont mention excel version:

for xl97 you can make a DSNless connect string
to avoid have to use the USERS ODBC files.

in newer version you can just enter a normal ado connect string

I suggest visiting www.able-consulting.com/ADO_Conn.htm
to get all the syntax ok.



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


tbieri wrote :