G
Guest
Hello -
This is probably an SQL question. I would like to make the code below (from
J-Walch book) more useful for my needs. The code creates a pivot table by
querying an access database. The change I would like to implement is that I
do not want the whole content of the table brought into excel, but just
selected data belonging to a specific field identified by a variable. This
can be accomplished by changing the * in the code below to a variable (for
instance the value of sheet(2).Range("A1")) How can I do that?
Below is the code. Where you find the * is where I would like to enter the
variable.
Thank you!
Sub CreatePivotTableFromDB()
Dim PTCache As PivotCache
Dim PT As PivotTable
On Error Resume Next
Application.DisplayAlerts = False
Sheets("PivotSheet").Delete
On Error GoTo 0
Set PTCache = ActiveWorkbook.PivotCaches.Add _
(SourceType:=xlExternal)
DBFile = ThisWorkbook.Path & "\Mydata.mdb"
ConString = "ODBC;DSN=MS Access Database;DBQ=" & DBFile
QueryString = "SELECT * FROM `" & ThisWorkbook.Path & _
"\MYDATA`.MyTable MyTable"
With PTCache
.Connection = ConString
.CommandText = QueryString
End With
Worksheets.Add
ActiveSheet.Name = "PivotSheet"
Set PT = PTCache.CreatePivotTable( _
TableDestination:=Sheets("PivotSheet").Range("A1"), _
TableName:="Payroll for Kreg Project")
With PT
.PivotFields("Dst Acct Unit").Orientation = xlRowField
.PivotFields("Distribution Amount").Orientation = xlDataField
End With
End Sub
This is probably an SQL question. I would like to make the code below (from
J-Walch book) more useful for my needs. The code creates a pivot table by
querying an access database. The change I would like to implement is that I
do not want the whole content of the table brought into excel, but just
selected data belonging to a specific field identified by a variable. This
can be accomplished by changing the * in the code below to a variable (for
instance the value of sheet(2).Range("A1")) How can I do that?
Below is the code. Where you find the * is where I would like to enter the
variable.
Thank you!
Sub CreatePivotTableFromDB()
Dim PTCache As PivotCache
Dim PT As PivotTable
On Error Resume Next
Application.DisplayAlerts = False
Sheets("PivotSheet").Delete
On Error GoTo 0
Set PTCache = ActiveWorkbook.PivotCaches.Add _
(SourceType:=xlExternal)
DBFile = ThisWorkbook.Path & "\Mydata.mdb"
ConString = "ODBC;DSN=MS Access Database;DBQ=" & DBFile
QueryString = "SELECT * FROM `" & ThisWorkbook.Path & _
"\MYDATA`.MyTable MyTable"
With PTCache
.Connection = ConString
.CommandText = QueryString
End With
Worksheets.Add
ActiveSheet.Name = "PivotSheet"
Set PT = PTCache.CreatePivotTable( _
TableDestination:=Sheets("PivotSheet").Range("A1"), _
TableName:="Payroll for Kreg Project")
With PT
.PivotFields("Dst Acct Unit").Orientation = xlRowField
.PivotFields("Distribution Amount").Orientation = xlDataField
End With
End Sub