Create pivot table in Excel from MS Access using VBA

[Red Fox]

Hello!



Please can you help to improve the VBA code. It's necessary to create pivot table in Excel from MS Access using VBA.

The VBA code stops running on this line:

Set PTCache = .ActiveWorkbook.PivotCaches.Add _      
       (SourceType:=xlDatabase, _      
       SourceData:=.ActiveWorkbook.Sheets("Raw_Data").Range(SourcePivot))

Access file is attached.

Thanks in advance.

[June7]

Weird message when opening ReportForm: There is no object in this control. I hit OK (twice) and form finally opens.

Instead of code to set label caption, could use a textbox with expression in ControlSource. Set as Enabled No. I removed the Activate procedure and don't get error.

However, get that same error on the DatePicker code when I run the ReportButton event. I comment those 2 lines.

Now get error message: Object variable or With Block variable not set. - triggered by Set MyQueryDef line. Why modify QueryDef?

What does p variable represent?

Why open query object?

[Red Fox]

Thanks for the reply! Sorry, the attached file wasn't correct. I resolved the problem, but got a new one. Now the VBA recoder stops to work on the following lines:

                 With .ActiveSheet.PivotTables("PivotTable")
                        .PivotFields("Group").Orientation = xlRowField
                        .PivotFields("Group").Position = 1

New file is attached. Thanks for the reply.

[June7]

Open db and get error "There is no object in this control." - twice and when I try to run the button event because of date picker code. Commented code.

I get error "Unable to set the Position Property of the PivotField class".

The only example I can find that seems relevant declares and sets PivotItem and PivotField object variables http://stackoverflow.com/questions/1...in-pivot-table