Hi All,
Long time reader of this forum but first time posting.
I don't normally ask for help as i prefare to read as much as i can to understand coding better.... but for the first time in 8 years of this job i'm close to giving up on trying to work this issue out.
What i'm trying to do, i feel should be pretty simple but i just can not get the code right to create a pivot table in Excel from a module ran in Access.
I have 3 queries which are exported to a new workbook on 3 tabs, the 3 tabs are then merged in to one tab/sheet, with the remaining 2 tabs/sheets deleted. Some formatting is done and color coding depending on criteria being met.
Now, all i want to do is use the data held in the one sheet to create a Pivot Table of that data. I have done this manually and it does what i want it to do. However, trying to create from a module via access vba is causing me some headaches.
Has anyone done this before and could point me in the right direction? Perhaps a guide on what parameters should be etc?
I have tried 2 bits of code that when i read them, look ok to me but i must be missing something, hopefully something completely obvious that i am overlooking.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim lngLastRow As Long
Dim shtL As Worksheet
Dim aSheet As Worksheet
Set shtL = WB.Sheets(1)
Set aSheet = WB.Sheets.Add
lngLastRow = shtL.Range("A2").End(xlDown).Row
WB.PivotCaches.Create(SourceType:=xlDatabase, _
'SourceData:="shtL!A1:AA & lngLastRow",
'Version:=xlPivotTableVersion12). _
'CreatePivotTable TableDestination:=aSheet.Name&"!A1", _
'TableName:="LeaverPivot", _
'DefaultVersion:=xlPivotTableVersion12
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
This gives me an error of;
Unable to get PivotTables property of the worksheet class
Any help at all would be greatly appreciated.
I am using Access & Excel 2010
Excel Library added in the References
Please let me know if there's any more info i can give that would be of use.
Regards,
Ric.
Long time reader of this forum but first time posting.
I don't normally ask for help as i prefare to read as much as i can to understand coding better.... but for the first time in 8 years of this job i'm close to giving up on trying to work this issue out.
What i'm trying to do, i feel should be pretty simple but i just can not get the code right to create a pivot table in Excel from a module ran in Access.
I have 3 queries which are exported to a new workbook on 3 tabs, the 3 tabs are then merged in to one tab/sheet, with the remaining 2 tabs/sheets deleted. Some formatting is done and color coding depending on criteria being met.
Now, all i want to do is use the data held in the one sheet to create a Pivot Table of that data. I have done this manually and it does what i want it to do. However, trying to create from a module via access vba is causing me some headaches.
Has anyone done this before and could point me in the right direction? Perhaps a guide on what parameters should be etc?
I have tried 2 bits of code that when i read them, look ok to me but i must be missing something, hopefully something completely obvious that i am overlooking.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim lngLastRow As Long
Dim shtL As Worksheet
Dim aSheet As Worksheet
Set shtL = WB.Sheets(1)
Set aSheet = WB.Sheets.Add
lngLastRow = shtL.Range("A2").End(xlDown).Row
WB.PivotCaches.Create(SourceType:=xlDatabase, _
'SourceData:="shtL!A1:AA & lngLastRow",
'Version:=xlPivotTableVersion12). _
'CreatePivotTable TableDestination:=aSheet.Name&"!A1", _
'TableName:="LeaverPivot", _
'DefaultVersion:=xlPivotTableVersion12
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
This gives me an error of;
Unable to get PivotTables property of the worksheet class
Any help at all would be greatly appreciated.
I am using Access & Excel 2010
Excel Library added in the References
Please let me know if there's any more info i can give that would be of use.
Regards,
Ric.
