|
Posted to microsoft.public.excel.programming
|
|
|
How do I update a ODBC query in Excel using pivotTables in VBA
"Dick Kusleika" wrote:
FCS wrote:
I have a worksheet with a Pivot Table whose source data is an Access
Database encrypted using a Workgroup File (.mdw). I created a User
DSN within the OBDC Object Administrator and I specified the .mdw
file. I created the PivotTable by pointing to my DSN data source and
I created the new Pivot Table succesfully.
I moved the files (.mdb .mdw and .xls) to another computer with the
same versions of Windows and Office and recreated the DSN entry but
now I can not refresh the data. I get an error message saying "OBDC
Microsoft Access Driver Login Failed. Cannot start your application.
The workgroup information file is missing or opened exclusively by
another user". What is wrong? How can I check the workgroup file
used in the original pivottable in VBA and how can I change it?
Sadly, the external data query doesn't just point to the DSN, it actually
stores the database location. Go to the Immediate Window (Alt-F11, Cntl+G)
and type
?Sheet1.PivotTables(1).PivotCache.Connection
and
?Sheet1.PivotTables(1).PivotCache.CommandText
and see if those string don't contain the path to the old mdw. You can read
this page
http://www.dicks-clicks.com/excel/Ex...htm#ChangeConn
It doesn't discuss PivotTables specifically, but it will give you an idea of
what the Connection and CommandText strings look like. Every time you see
something like
Sheet1.QueryTables(1)
just replace
Sheet1.PivotTables(1).PivotCache
and it should work similarly. I've never based a PT on and mdw, so there
maybe something more going on than I can see, but this should give you a
start.
--
Dick Kusleika
MVP - Excel
Daily Dose of Excel
http://www.dicks-blog.com
|