Excel Pivot through Access VBA

[Fable79]

I'm using excel/access 2007.
I've an Access file based on which I'd like to create a brand new Excel file with a pivot table.
So far I was able to create the file and the pivot table, but I'm unable to modify correctly the value of the pivot. It defaults to "Count of" while I'd like to have "Sum of".
I finally understood that the correct property to set is ".calculation", but, as it does for .orientation, access vba accepts only numbers.

Can anyone help me?
Thanks a lot

this is the code:
Set appexcel = CreateObject("Excel.Application")
Set wbk = appexcel.Workbooks.Add
wbk.ActiveSheet.Name = "PivotRes"
Set wks_pivot = wbk.worksheets("PivotRes")
wbk.sheets("Sheet2").Activate
wbk.ActiveSheet.Name = "PivotSrc"
Set wks_source = wbk.worksheets("PivotSrc")
Set rs = CurrentDb.OpenRecordset("SELECT * FROM PivotDemand", 4)
With wks_source
.QueryTables.Add Connection:=rs, Destination:=.Range("A1")
.QueryTables(1).Refresh
End With
wbk.PivotCaches.Create(SourceType:=1, Sourcedata:="PivotSrc!R1C1:R5000C8", Version:=3).CreatePivotTable _
tabledestination:="PivotRes!R1C1", tableName:="PivotT", DefaultVersion:=3

wks_pivot.Activate

With wks_pivot.pivottables("PivotT").pivotfields("Customer_Name")
.Orientation = 1
End With
With wks_pivot.pivottables("PivotT").pivotfields("WaferQty")
.Orientation = 4
.calculation = 2 'not a sum
End With

 

[james_lankford]

do the entire thing in Excel
do it using Excel's menus (no VBA)
but before you start click on "Record macro" from excel's menu

once you're pivot table is finished click "stop recording"

now open up the vba editor in excel

the code excel creates is pretty bad and will need major editing
they also use excel constants like
xlSum
instead of 4 or whatever the value is

but at least you actually see the code
so you can do a google search on
xlsum constant
or whatever constant you're looking for

you could also step through the code and debug.print the values of the constants

you could also temporarily change this line
Set appexcel = CreateObject("Excel.Application")
and set a reference to excel

then you can use the actual excel constants in access vba
and step through them and see their values

 

[Fable79]

Thank you James for your answer.
I already tried the "record macro" trick but something is "lost in the translation" between excel and access vba
In fact, for instance, I cannot use .position for the pivot fields in access, while it is legal to use it in excel.
However, I tried to follow your suggestion to use the constant for xlsum (which is -4157) but didn't work either.

Can you please explain me better what you mean by "you could also temporarily change this line
Set appexcel = CreateObject("Excel.Application")
and set a reference to excel"?

Thank you very much

 

[Norie]

If you want to find the value of an Excel VBA constant look in the Object Browser(F2).

 

[Fable79]

Hi Norie,
thank you for your reply, but I'm using Access VBA and in there the object browser doesn't help with the "CreateObject("Excel.Application")" stuff.
However, I found the xlsum constant as suggested by James, but, unfortunately, this doesn't work with access vba.
Really stuck with it!
I'm in the path of giving up

 

[tiedied61]

I was having a similar problem a couple months ago. I have created multiple pivot reports and automated them through Access. Rather than trying to create the Pivot tables "on-the-fly", I created Excel templates and simply overwrite the data page, and loop through other pages, refreshing the Pivots. I have the first sheet as the raw data source, and many sheets have with different pivots, and a final report page that reads from the pivots.

Here is the shortened version ... no variables/constants Dimmed here, but it should give you the idea
In my case xlSheetIn is the user pointing to another workbook containing the source data to be used for the reports
You can just do as you have already done, using "normal" SELECT query, rather than a pivot query to populate the Raw Data page.
This is easier to code, and much more reliable.... It also has the advantage of working on ANY Excel template you decide to create, and any data source that has the necessary columns available to write to the Excel data page.

NOTE: the seemingly "extra" .Activate and .Select are necessary because Excel automation is funny about being in the correct workbook and worksheet before any copy/paste operations

  ' Open the template, and get the Raw Data sheet
   Set xlSheetOut = xlFileOut.Sheets(SHT_SWCR_DATASOURCE)
   
   ' be sure there are no filters on in the template
   xlSheetOut.AutoFilterMode = False
   
   ' Clear the dummy data from the template
   xlSheetOut.Activate
   xlSheetOut.Range(CellFirst, CellLast).ClearContents
   
   ' Copy/Paste the new data to the template
   CellPaste = "A2"
   xlFileIn.Activate
   xlSheetIn.Select
   xlSheetIn.Range(SrcFirst, SrcLast).Copy
   
   xlFileOut.Activate
   xlSheetOut.Select
   xlSheetOut.Range(CellPaste).Select
   xlApp.Selection.PasteSpecial xlPasteValues
   
   ' Data range pre-set in the template to 1 million rows
   ' Refresh all the pivot tables using new data
   For Each tmpSheet In xlFileOut.Worksheets
      For Each xlPivotTable In tmpSheet.PivotTables
         xlPivotTable.RefreshTable
      Next xlPivotTable
   Next tmpSheet
   
   ' Save and close the report output file
   xlApp.DisplayAlerts = False
   xlSheetOut.SaveAs FileNameOut
   Excel_FileClose xlFileOut, xlApp, True
   xlApp.DisplayAlerts = True

As I said, this code snippet will required a lot of modification to match you situation, but it gives you the logic flow. Be sure to include several hundred rows of dummy data in the template, so that the pivot initially has something, else it will not work. Also, DO NOT overwrite the data page column headers, only the data rows ... again this will cause the pivots to fall apart

Art

 

[Fable79]

Ladies and Sirs,
thank you very much for your time.
I don't know how, but after days and days of testing, finally ACCESS gave up and now the ".function = xlsum" works!!
Thank you again
Talk to you next time
ciao
Fabiana

 

[Norie]

Fabiana

Glad you got it working.

By the way, I wasn't really suggesting you look for the Excel VBA constant in the Access Object Browser, though it would be there if you had set a reference to the MS Excel Object library.