Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Redefining pivot table source
Good day all.
I have a pivot table within an Excel file that picks up its data from an Access database located in the same directory as the Excel file. I am attempting, unsuccessfully, to change the source that the Excel file goes to when it picks up the data. (Essentially, I am moving a folder from the C drive to the D drive. The Access file is called "Figs2.mdb", the table within the Access file is called "figstb" and the columns I am trying to retrieve from the table are "Name", Location", "Date" and "Amount". Code snip....... ---- Workbooks.Open ThisWorkbook.Path & "\NewPV.xls" Set wb = ActiveWorkbook x = ThisWorkbook.Path y = x & "\Figs2.mdb" wb.Sheets(1).PivotTableWizard SourceType:=xlExternal, SourceData:=Array("SELECT figstb.Name, figstb.Location, figstb.Date, figstb.Amount" & Chr(13) & "" & Chr(10) & "FROM `" & x & "\Figs2`.figstb figstb"), _ Connection:=Array("ODBC;DSN=MS Access Database;DBQ=" & y & " ;FIL=MS Access;") ---- Any help gratefully received. Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 pivot table source table | Excel Discussion (Misc queries) | |||
entry removed from source table remains in pivot table pull down | Excel Worksheet Functions | |||
Transforming pivot table back to the source table | Excel Worksheet Functions | |||
OfficeXP "Cannot open pivot table source file" when refreshing pivot table | Excel Programming | |||
Help required with setting up a pivot table with the source on sheet1 to have the pivot table created on sheet called "report" | Excel Programming |