Creating an Excel PivotChart in Access (1 Viewer)

C

crowegreg

Registered User.
Local time
Today, 15:14
Joined
Feb 28, 2011
Messages
108
I've written this code in Access to create a pivot chart in Excel. All the code listed is good except for the last line. The last line creates another instance of Excel. I can't figure out what the correct syntax is. I've tried, charts, worksheets, everything I can think of, and I can't figure it out.

Thanks in advance for your help!!


Set xlApp = New Excel.Application
xlApp.Visible = True
Set wb1 = xlApp.Workbooks.Open("c:\chi\testpivot.xlsx")

wb1.Sheets.Add
wb1.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Qry1!R1C1:R2539C5", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="Sheet1!R3C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion14

wb1.Sheets(1).Select
wb1.Sheets(1).Cells(1, 1).Select
wb1.Sheets(1).Shapes.AddChart.Select

wb1.ActiveChart.ChartType = xlLineMarkers
Click to expand...
 
Rx_

Rx_

Nothing In Moderation
Local time
Today, 15:14
Joined
Oct 22, 2009
Messages
2,803
Without the excel pivot or query, this has some shortcuts.
Try this model and see if that helps:

Code: 
Public Sub TestExcelPivot()
Dim xlApp As Excel.Application
Set xlApp = New Excel.Application
xlApp.Visible = True
xlApp.Workbooks.Open ("c:\testpivot.xlsx")
xlApp.Sheets.Add
'wb1.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Qry1!R1C1:R2539C5", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="Sheet1!R3C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion14
xlApp.Sheets(1).Select
xlApp.Sheets(1).Cells(1, 1).Select
xlApp.Sheets(1).Shapes.AddChart.Select
xlApp.ActiveChart.ChartType = xlLineMarkers
        xlApp.ActiveWorkbook.SaveAs FileName:="c:\testpivot.xlsx"
        xlApp.Visible = False
        xlApp.Quit
 
End Sub
 
E

Extra Mile Data

Registered User.
Local time
Today, 16:14
Joined
Jan 22, 2014
Messages
15
crowegreg,

I tried out a nearly identical set of code (I used Sheet3 instead of Sheet1) and ended up with a pivottable on Sheet 3 and a new chart started on Sheet1. I was using Access 2013 and Excel 2013.

Here's what I used:

Code: 
Public Function TestExcelPivot()
On Error GoTo Err_Handler
    
    Dim xlApp As Excel.Application
    Dim wb1 As Excel.Workbook
    
     Set xlApp = New Excel.Application
    xlApp.Visible = True
    Set wb1 = xlApp.Workbooks.Open(CurrentProject.Path & "\testpivot.xlsx")
    
    wb1.Sheets.Add
    wb1.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "Qry1!R1C1:R2539C5", Version:=xlPivotTableVersion14).CreatePivotTable _
    TableDestination:="Sheet3!R3C1", TableName:="PivotTable1", DefaultVersion _
    :=xlPivotTableVersion14
    
    wb1.Sheets(1).Select
    wb1.Sheets(1).Cells(1, 1).Select
    wb1.Sheets(1).Shapes.AddChart.Select
    
    wb1.ActiveChart.ChartType = xlLineMarkers
 Exit_Proc:
    On Error Resume Next
    Set wb1 = Nothing
    Set xlApp = Nothing
    Exit Function
 Err_Handler:
    MsgBox Err.Number & " " & Err.Description, vbCritical, "TestExcelPivot()"
    xlApp.Quit
    Resume Exit_Proc
End Function
 
C

crowegreg

Registered User.
Local time
Today, 15:14
Joined
Feb 28, 2011
Messages
108
Thank you for the two replies.

For Extra Mile Data, the statement "wb1.Sheets(1).Shapes.AddChart.Select", does that create another instance of Excel?

For Rx, I'll try some of your code and see what happens.
 
E

Extra Mile Data

Registered User.
Local time
Today, 16:14
Joined
Jan 22, 2014
Messages
15
It did NOT create a new instance of Excel. It stayed within the one that was originally opened.
 
C

crowegreg

Registered User.
Local time
Today, 15:14
Joined
Feb 28, 2011
Messages
108
Amazing. Your code is nearly identical to mine. I'm using Access 2010 and Excel 2010. What references do you have selected within your DB?
 
E

Extra Mile Data

Registered User.
Local time
Today, 16:14
Joined
Jan 22, 2014
Messages
15
Here are the references:

  1. Visual Basic for Applications
  2. Microsoft Access 15.0 Object Library
  3. OLE Automation
  4. Microsoft Office 15.0 Access database engine Object Library
  5. Microsoft Excel 15.0 Object Library
 
C

crowegreg

Registered User.
Local time
Today, 15:14
Joined
Feb 28, 2011
Messages
108
The only differences I have are Access 14.0 Object Library. I'm sure that's the difference between Access 2010 & Access 2013.

Here is my revised code. I removed the ActiveChart statement. Although my spreadsheet is being created correctly, I still have an instance of Excel running. Any suggestions??

Dim strFileName As String
Dim xlApp As Excel.Application
Dim wb1 As Excel.Workbook

strFileName = "Test PivotChart " & DatePart("m", Date) & "_" & IIf(DatePart("d", Date) < 10, "0" & DatePart("d", Date), DatePart("d", Date))

DoCmd.OutputTo acOutputQuery, "Qry1", "xlsx", Environ$("USERPROFILE") & "\documents\" & strFileName & ".xlsx"

Set xlApp = New Excel.Application
'xlApp.Visible = True
Set wb1 = xlApp.Workbooks.Open(Environ$("USERPROFILE") & "\documents\" & strFileName & ".xlsx")

wb1.Sheets.Add
wb1.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Qry1!R1C1:R2539C5", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="Sheet1!R3C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion14

wb1.Sheets(1).Select
wb1.Sheets(1).Cells(1, 1).Select
wb1.Sheets(1).Shapes.AddChart.Select
wb1.Sheets(1).Shapes(1).Chart.ChartType = xlLineMarkers
wb1.Sheets(1).Shapes(1).Chart.SetSourceData Source:=Range("Sheet1!$A$1:$C$18")
'wb1.ActiveChart.ChartType = xl3DBarStacked100
'wb1.ActiveChart.ChartType = xlLineStacked
wb1.Sheets(1).Shapes(1).IncrementLeft 192
wb1.Sheets(1).Shapes(1).IncrementTop 14.4

wb1.Sheets(1).PivotTables("PivotTable1").AddDataField wb1.Sheets(1).PivotTables( _
"PivotTable1").PivotFields("Tran_Amount"), "Sum of Tran_Amount", xlSum
With wb1.Sheets(1).PivotTables("PivotTable1").PivotFields("Period")
.Orientation = xlRowField
.Position = 1
End With
With wb1.Sheets(1).PivotTables("PivotTable1").PivotFields("Report_Category")
.Orientation = xlColumnField
.Position = 1
End With
wb1.ShowPivotTableFieldList = False

wb1.Sheets(1).Shapes(1).Chart.Parent.Cut

wb1.Sheets.Add
wb1.Sheets(1).Paste

wb1.Sheets(1).Name = "Pivot Chart"
wb1.Sheets(2).Name = "Pivot Data"
wb1.Sheets(3).Name = "Data"

xlApp.ActiveWorkbook.Close (True)

xlApp.Quit

Set wb1 = Nothing
Set xlApp = Nothing
Click to expand...
 
C

crowegreg

Registered User.
Local time
Today, 15:14
Joined
Feb 28, 2011
Messages
108
In my troubleshooting, here's what I discovered, after the sub completes, an instance of Excel is displayed within the Task Manager. After closing the form, Excel is still within the Task Manager. When I close the database, Excel is no longer within the Task Manager. I've verified that opening the database, Excel is not within the Task Manager, after opening the form, Excel is not within the Task Manager, but after the sub completes, Excel is within the Task Manager. Any thoughts.

Here is my final code:
Dim strFileName As String
Dim xlApp As Excel.Application
Dim wb1 As Excel.Workbook

strFileName = "Test PivotChart " & DatePart("m", Date) & "_" & IIf(DatePart("d", Date) < 10, "0" & DatePart("d", Date), DatePart("d", Date))

DoCmd.OutputTo acOutputQuery, "Qry1", "xlsx", Environ$("USERPROFILE") & "\documents\" & strFileName & ".xlsx"

Set xlApp = New Excel.Application
'xlApp.Visible = True
Set wb1 = xlApp.Workbooks.Open(Environ$("USERPROFILE") & "\documents\" & strFileName & ".xlsx")

wb1.Sheets.Add
wb1.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Qry1!R1C1:R2539C5", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="Sheet1!R3C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion14

wb1.Sheets(1).Select
wb1.Sheets(1).Cells(1, 1).Select
wb1.Sheets(1).Shapes.AddChart.Select
wb1.Sheets(1).Shapes(1).Chart.ChartType = xlLineMarkers
wb1.Sheets(1).Shapes(1).Chart.SetSourceData Source:=Range("Sheet1!$A$1:$C$18")
'wb1.Sheets(1).Shapes(1).Chart.ChartType = xl3DBarStacked100
'wb1.Sheets(1).Shapes(1).Chart.ChartType = xlLineStacked
wb1.Sheets(1).Shapes(1).IncrementLeft 192
wb1.Sheets(1).Shapes(1).IncrementTop 14.4

wb1.Sheets(1).PivotTables("PivotTable1").AddDataField wb1.Sheets(1).PivotTables( _
"PivotTable1").PivotFields("Tran_Amount"), "Sum of Tran_Amount", xlSum
With wb1.Sheets(1).PivotTables("PivotTable1").PivotFields("Period")
.Orientation = xlRowField
.Position = 1
End With
With wb1.Sheets(1).PivotTables("PivotTable1").PivotFields("Report_Category")
.Orientation = xlColumnField
.Position = 1
End With
wb1.ShowPivotTableFieldList = False

wb1.Sheets(1).Shapes(1).Chart.Parent.Cut

wb1.Sheets.Add
wb1.Sheets(1).Paste

wb1.Sheets(1).Name = "Pivot Chart"
wb1.Sheets(2).Name = "Pivot Data"
wb1.Sheets(3).Name = "Data"

wb1.Close (True)

xlApp.Quit

Set wb1 = Nothing
Set xlApp = Nothing
Click to expand...
 
E

Extra Mile Data

Registered User.
Local time
Today, 16:14
Joined
Jan 22, 2014
Messages
15
If I am remembering correctly, an Excel application object that is private to a procedure will automatically close when code gets to the end of the procedure. In other words, if you want to keep the Excel app open after a form is closed, the Excel app variable has to be defined in a public module as a public object (not declared in the form itself). It sounds like you are launching your Excel object from within the form module, so I am confused about why it persists after the form closes.

The DoCmd.OutputTo has an AutoStart argument that automatically starts the Excel file after it is exported. It is supposed to default to False if you don't explicitly set it. If you haven't already, I would try to explicitly set that argument to False. Maybe the OutputTo isn't acting as expected and is starting Excel.
 
C

crowegreg

Registered User.
Local time
Today, 15:14
Joined
Feb 28, 2011
Messages
108
That makes two of us confused!! The code that I have used to create the Excel object, I've used throughout my DB, and I have not experienced this problem.


After cleaning up the code, which primarily meant getting rid of the code that used "ActiveChart," all the remaining code only uses "wb1."


When I step through the code, Excel shows up in Task Manager after the Set xlapp statement.


Some other troubleshooting I've done is if I comment out every line of code associated with the pivot chart, after execution is completed, no instance of Excel is within the Task Manager. If I un-comment those lines, after execution is completed, an instance of Excel is within the Task Manager.


Possibly a bug in Access 2010 that has been corrected in Access 2013.
 
E

Extra Mile Data

Registered User.
Local time
Today, 16:14
Joined
Jan 22, 2014
Messages
15
You might try CreateObject() or GetObject() to obtain your Excel application object rather than
New Excel.Application
I have used both of those alternatives in the past.
 
C

crowegreg

Registered User.
Local time
Today, 15:14
Joined
Feb 28, 2011
Messages
108
Thanks for the suggestion. I'll give that a try on Thursday.
 
C

crowegreg

Registered User.
Local time
Today, 15:14
Joined
Feb 28, 2011
Messages
108
This is amazing. With looking at a lot of different things, I've come up with something. I've attached the DB. Please follow my exact instructions to duplicate what I've encountered. Please read all the instructions before testing.

1. Open this DB. Three objects are contained.
2. Open your Task Manager. Select the Processes tab. Keep it visible on your screen while you're doing this testing.
3. Open the form. Their is one command button "Build Pivot Chart." Before clicking on this command button, make sure your Task Manager is visible.
4. Click on the command button, and watch the Task Manager. You will see the instance of Excel pop-up.
5. When the code is completed, the form is closed. You will see the instance of Excel is still present.
6. Close the DB. The instance of Excel will be removed from the Task Manager
7. Open the DB again.
8. Open the form in design view
9. Select to view the code behind the form
10. Scroll down to locate the "wb1.PivotCaches" line
12. Within this block of code you will see twice "xlPivotTableVersion12"
13. Change 12 to 14 in both places
14. Close the VB window
15. VERY IMPORTANT!! DO NOT SAVE FORM. SWITCH FROM DESIGN VIEW TO FORM VIEW
16. Click on the command button, and watch the Task Manager. You will see the instance of Excel pop-up
17. When the code is completed, you will be prompted to save the form as it is being closed. DO NOT SELECT YES. SELECT NO, AND WATCH THE TASK MANAGER
18. Excel is removed from the Task Manager

If you make the changes to the code listed on line 12, then save the form, the instance of Excel stays in the Task Manager

I hope you're able to duplicate what I have done!!
 

Attachments

  • Database2.accdb
    440 KB · Views: 552
E

Extra Mile Data

Registered User.
Local time
Today, 16:14
Joined
Jan 22, 2014
Messages
15
I got the same results that you did, but I fixed the problems.

You had sort of a mixed bag.
(1) You were declaring your Excel app variable using late binding, yet you had maintained the reference to the Excel object library in your References. I fixed this by removing the Reference and by explicitly declaring the two constants, xlDatabase and xlPivotTableVersion14.
(2) You were declaring your Excel app variable at the form module level rather than the procedure level. I fixed this by pulling the variable declarations out of the module level declarations and into the procedure itself.

NOTE
I commented the GetObject() code out. You only need that if you intend to use an instance of Excel that is already open.
I left Version:=xlPivotTableVersion14 and DefaultVersion:=xlPivotTableVersion14 in your PivotCaches statement, but if you truly want late binding (not using a explicit Excel object version), then I think you should simply remove those arguments. I believe it will still work without them.

Here is the code that worked for me:

Code: 
 Option Compare Database
Option Explicit
  
 Private Sub Command2_Click()
On Error GoTo Command2_Click_Err
     Dim strFileName As String
    Dim xlApp As Object
    Dim wb1 As Object
    Dim strRange As String
    Dim dbase As Database
    Dim intColumnCount As Integer
    Dim intRecordCount As Integer
    Const xlDatabase As Variant = 1
    Const xlPivotTableVersion14 As Variant = 4
     Set dbase = CurrentDb
    intColumnCount = dbase.QueryDefs("Qry1").Fields.Count
    Set dbase = Nothing
    
    intRecordCount = DCount("[Report_Category]", "Qry1") + 1
    
    strFileName = "Test PivotTable " & DatePart("m", Date) & "_" & IIf(DatePart("d", Date) < 10, "0" & DatePart("d", Date), DatePart("d", Date))
    
    DoCmd.OutputTo acOutputQuery, "Qry1", "xlsx", Environ$("USERPROFILE") & "\documents\" & strFileName & ".xlsx"
    
    'Set xlApp = New Excel.Application
    On Error Resume Next
'    Set xlApp = GetObject(, "Excel.Application")
 '    If Err.Number <> 0 Then
'        Err.Clear
        Set xlApp = CreateObject("Excel.Application")
 '    End If
    'xlApp.Visible = True
    Set wb1 = xlApp.Workbooks.Open(Environ$("USERPROFILE") & "\documents\" & strFileName & ".xlsx")
    
    'Determine total data being used, rows and columns
    strRange = "Qry1!R1C1:R" & intRecordCount & "C" & intColumnCount
    
    wb1.Sheets.Add
    wb1.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        strRange, Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:="Sheet1!R3C1", TableName:="PivotTable1", DefaultVersion _
        :=xlPivotTableVersion14
        
    wb1.Sheets(1).Select
    wb1.Sheets(1).Cells(1, 1).Select
    wb1.Sheets(1).Shapes.AddChart.Select
    wb1.Sheets(1).Shapes(1).Chart.ChartType = xlLineMarkers
    wb1.Sheets(1).Shapes(1).Chart.SetSourceData Source:=Range("Sheet1!$A$1:$C$18")
    wb1.Sheets(1).Shapes(1).IncrementLeft 192
    wb1.Sheets(1).Shapes(1).IncrementTop 14.4
    
    wb1.Sheets(1).PivotTables("PivotTable1").AddDataField wb1.Sheets(1).PivotTables( _
        "PivotTable1").PivotFields("Tran_Amount"), "Sum of Tran_Amount", xlSum
     With wb1.Sheets(1).PivotTables("PivotTable1").PivotFields("Period")
        .Orientation = xlRowField
        .Position = 1
    End With
     With wb1.Sheets(1).PivotTables("PivotTable1").PivotFields("Report_Category")
        .Orientation = xlColumnField
        .Position = 1
    End With
     wb1.ShowPivotTableFieldList = False
    
    wb1.Sheets(1).Shapes(1).Chart.Parent.Cut
    wb1.Sheets.Add
    wb1.Sheets(1).Paste
    
    wb1.Close (True)
    
    DoCmd.Close , ""
 Command2_Click_Exit:
    xlApp.Quit
    
    Set wb1 = Nothing
    Set xlApp = Nothing
     Exit Sub
 Command2_Click_Err:
    MsgBox Error$
    Resume Command2_Click_Exit
 End Sub
 
E

Extra Mile Data

Registered User.
Local time
Today, 16:14
Joined
Jan 22, 2014
Messages
15
Also, because we are using late binding, I changed wb1 to Object rather than Excel.Workbook.
 
You must log in or register to reply here.

Similar threads

J
How to generate/create Excel range subaddress in an Access hyperlink field.
Replies
4
Views
496
Gasman
Gasman
J
Solved Create Excel Pivot Table From Access VBA
2
Replies
25
Views
2,557
jo15765
J
I
Making pivots in xlsx files in Access VBA
Replies
2
Views
774
JHB
J
J
Solved Convert An Excel Macro To Run In Access VBA
Replies
12
Views
1,418
jo15765
J
A
Using Access to automate a Pivot Table in Excel
2
Replies
21
Views
3,634
Gasman
Gasman

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom