Dan,
Here is one approach to your problem.
The object that you want to look at is the PivotCache (e.g.
PivotTables(1).PivotCache).
There are two properties:
1) PivotCache.Connection
2) PivotCache.Commandtext
1) The CommandText property holds the SQL query string. Note that MS Query
typically includes a path to the database file in the SQL string. Depending
upon your type of query you may be able to remove this filepath information
from the query string (or you can construct the string with the proper
path). See the examples below:
Original CommandText:
"SELECT Table1.fYear, Table1.fMonth, Table1.fType, Table1.fData
FROM `C:\My Documents\Excel\Junk1\Access\myDB`.Table1 Table1"
New CommandText:
"SELECT * FROM Table1"
-or-
"SELECT * FROM `C:\MyPath\myDB`.Table1 Table1"
2) The Connection property holds the connection string to the database file.
It appears that you can remove the path to the file and the default
directory from this string. Excel will **by default** use the current file
directory automatically as the path to the database file. This may or may
not point to your database file when your Excel file opens and the user has
the ability to change the default directory by using the File | Open menu
command. I wouldn't recommend going this route. The proper path to the
database file is too easily broken.
The more preferred route would be to define the path to the database file
with a static known directory. Or it appears your intent is that the
database file will be in the same directory as the Excel file. If this is
always the case then you could read the path property of the Excel file and
use it to define the path to the database file in the connection string.
You shouldn't need to rebuild your PivotTable. Just set the Connection
string and CommandText when the workbook is opened in the Workbook.Open
event. I do this routinely with QueryTables without any problems. Below is
some example code that should get you started. Cheers.
Troy
Sub PivotTable_ExtDB()
Dim twb As Workbook
Dim ws1 As Worksheet
Dim pt1 As PivotTable
Dim sCn As String
Dim sSQL As String
Dim sPath As String
Dim sDBname As String
Dim sPTname As String
'***Use the name of your PivotTable.
sPTname = "PivotTable1"
'***Use the name of your database file.
sDBname = "Testdb1.mdb"
'***This assumes the PivotTable is on Sheet1.
Set twb = ThisWorkbook
Set ws1 = twb.Worksheets(1)
Set pt1 = ws1.PivotTables(sPTname)
'Assume the database file is in the
'same directory as this file.
sPath = ThisWorkbook.Path
'Redefine the connection string
'to a MS Access database.
sCn = ""
sCn = sCn & "ODBC;DSN=MS Access Database;"
sCn = sCn & "DBQ=" & sPath & "\" & sDBname & ";"
sCn = sCn & "DefaultDir=" & sPath & ";"
sCn = sCn & "DriverId=25;FIL=MS Access;"
sCn = sCn & "FIL=MS Access;"
sCn = sCn & "MaxBufferSize=2048;"
sCn = sCn & "PageTimeout=5;"
pt1.PivotCache.Connection = sCn
'***Use your query string.
sSQL = "SELECT * FROM Table1"
pt1.PivotCache.CommandText = sSQL
'Refresh the PivotTable.
pt1.PivotCache.Refresh
'MsgBox pt1.PivotCache.Connection
'MsgBox pt1.PivotCache.CommandText
'Hide the PivotTable toolbar.
Application.CommandBars("PivotTable").Visible = False
Set pt1 = Nothing
Set ws1 = Nothing
Set twb = Nothing
End Sub
Hello,
I have created a pivot table from an external source. The external source
is a Access database that is in the same directory as the excel workbook.
The issue is that Microsoft Query automatically modifies my SQL statement
to include a hardcoded path to the database.
For example, "Select * FROM MyTable" changes to
"SELECT * FROM `C:\Program Files\MyFolder\db`.MyTable"
If I send the db and excel file to someone, and they do not put in the
exact folder, the pivottable does not know where to get data from.
My questions are:
1. Can I prevent the hardcoded path?
2. Is there a way to dynamically through code on sheet startup, to modify
the SQL statement to have a different path? The "layout" of the pivottable
would need to be persisted and/or recoded.
