Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivottables from external sources
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 3. Any advice would be appreciated Thanks and I look forward to your response Dan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivottables from external sources
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 wrote in message ... 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 a 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. 3. Any advice would be appreciated. Thanks and I look forward to your response. Dan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivottables from external sources
Dan,
Glad it worked for you. Cheers. Troy wrote in message ... Troy, Thanks for your thorough answer. This has solved my issue and I appreciate it. Thanks, Dan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Updating of External Sources | Excel Discussion (Misc queries) | |||
Pivottables - Can i consolidate 2 Data sources in Excel 2007? | Excel Discussion (Misc queries) | |||
Sending pivottables to external users | Excel Discussion (Misc queries) | |||
PivotTables and OLAP sources | Excel Discussion (Misc queries) | |||
Extracting and using Text from external sources | Excel Worksheet Functions |