Results 1 to 14 of 14
  1. #1
    crowegreg is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Posts
    398

    Creating a pivot chart in Excel

    In another posting, I was able to get the pivot table working. I'm having a similar problem with creating a pivot chart. Here's my code:

    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

    When I get to this last line of code, another instance of Excel is opened. I can't figure out the correct syntax for this line. I've tried everything I can think of.

    Thanks in advance for your assistance!!

  2. #2
    crowegreg is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Posts
    398
    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").AddDataFi eld wb1.Sheets(1).PivotTables( _
    "PivotTable1").PivotFields("Tran_Amount"), "Sum of Tran_Amount", xlSum
    With wb1.Sheets(1).PivotTables("PivotTable1").PivotFiel ds("Period")
    .Orientation = xlRowField
    .Position = 1
    End With
    With wb1.Sheets(1).PivotTables("PivotTable1").PivotFiel ds("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


  3. #3
    crowegreg is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Posts
    398
    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").AddDataFi eld wb1.Sheets(1).PivotTables( _
    "PivotTable1").PivotFields("Tran_Amount"), "Sum of Tran_Amount", xlSum
    With wb1.Sheets(1).PivotTables("PivotTable1").PivotFiel ds("Period")
    .Orientation = xlRowField
    .Position = 1
    End With
    With wb1.Sheets(1).PivotTables("PivotTable1").PivotFiel ds("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

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Does it still create two objects? This is a definite memory leak. Is it happening here? wb1.PivotCaches.Create

  5. #5
    crowegreg is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Posts
    398
    While stepping through the code, it does not show more than 1 instance of Excel. With the problem that is happening, should the Task Manager display more than 1 instance?

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by crowegreg View Post
    ...should the Task Manager display more than 1 instance?
    It can. I think the important thing is not to have any (extra unaccounted for) after the procedure is complete. So, if you have none at the beginning, you should have not when complete.

    I don't do a lot with excel and instantiating excel objects but the .Create caught my eye because you are using a template. Maybe there is another method you can use (Get?). Maybe you can execute some of your code without the wb1.PivotCaches.Create line and see if the extra instance gets created. I know it is a big part of your procedure but, just to see where and when the excel instance appears in the task bar.

    Honestly, it is a little weird to me that all of the clean up code at the end of your code block does not get rid of it. I guess this simple fact illustrates that an unnecessary object is being created under our noses.

  7. #7
    crowegreg is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Posts
    398
    The contract that I'm working on presently has given me the opportunity and headache of working with the excel object. I've done an extensive amount of using the excel, and up to this point in time, once I've figured out the correct coding, it has worked like it should. This has form has been the exception. I probably have 10 forms I've created using the excel object, and they work correctly. Within this form, if I comment out everything associated with the pivot chart, their are no remaining instances of excel.

    I just tested it on my home computer. I get the same results. Their is only 1 instance of excel stepping through the code. When I close the DB, excel is unloaded. At least I know now it's not my computer at the office.

    I'll try the get here in a few minutes.

  8. #8
    crowegreg is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Posts
    398
    I added the following code to what is listed above:
    Dim xlApp As Object
    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application") If Err.Number <> 0 Then
    Err.Clear
    Set xlApp = CreateObject("Excel.Application")
    End If
    In trouble shooting, if Excel is opened and minimized, when executing the above code, another instance of excel is displayed in Task Manager. When the code is completed, the original instance of excel is closed, no longer appearing on the task bar. Their is still one instance in the Task Manager, and like before, when the DB is closed, the instance is removed from Task Manager.


    If Excel is not opened, when executing the code, it does exactly like before. The instance is removed when the DB is closed.


    Crazy stuff here!!

  9. #9
    crowegreg is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Posts
    398
    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!!
    Attached Files Attached Files

  10. #10
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    You are getting that behavior because Access is holding the instance of Excel in memory until you tell Access you are not interested in saving changes. So it is the same behavior as closing Access.

    I looked at the code and it seems that
    DoCmd.OutputTo acOutputQuery, "Qry1", "xlsx", "C:\Test\" & strFileName & ".xlsx"
    is not creating the version needed for the pivot charts. Perhaps using Transferspreadsheet along with Version14 or even xlPivotTableVersion14 will allow you to manipulate the spreadsheet using the Workbooks object.

    I tried to create the object using the query def in a separate procedure and found the file created was not the correct version. I was doing this using a separate procedure in an effort to cause the newly created file to be available to GetObject.

    I also noticed that you have .Open method for your workbook. This method seems redundant with the app object
    Maybe something like
    xlApp.Application.Visible = False
    xlApp.Parent.Windows(1).Visible = False
    and then instantiate the workbook object.

    Not sure all of this is going to bring you closer to having a handle on the correct object or not. I would use a boolean to indicate whether or not Excel was open or not and use the boolean to determine whether or not to close Excel, the application.

    Something like
    If Err.Number <> 0 Then bolIsRunning = True

    If bolIsRunning = False then
    xlApp.Application.Close
    end if

  11. #11
    crowegreg is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Posts
    398
    Thanks for the in depth analysis!!

    xlPivotTableVersion14 was originally in the code. The DB is posted online contained xlPivotTableVersion12 because I was able to duplicate that issue consistently.

    I'll switch back to xlPivotTableVersion14 and try the transfer spreadsheet.

    I'll try your other suggestions, and post later.

    Again, thank you for your assistance in this problem

  12. #12
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    If you can get GetObject to instantiate your app object there is not a need for the orther set object =

    I would try and go straight from Dim xlApp as object to
    Set xlApp = GetObject("C:\Test\" & strFileName & ".xlsx")

    Obviously adjusting the path to your correct path.

  13. #13
    crowegreg is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Posts
    398
    I finally got this to work properly. In my testing, I determined that when the following code executed, this is what caused and additional instance of Excel.
    wb1.Sheets(1).Shapes(1).Chart.SetSourceData Source:=Range("Sheet1!$A$1:$C$18")

    Here is the finished, working properly code.

    Dim strFileName As String
    Dim xlApp As Object
    Dim wb1 As Workbook
    Dim xlRng As Object
    Dim strRange As String
    Dim dbase As Database
    Dim intColumnCount As Integer
    Dim intRecordCount As Integer
    Private Sub Command2_Click()
    On Error GoTo Command2_Click_Err
    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:=xlPivotTableVersion12).CreatePivotTable _
    TableDestination:="Sheet1!R3C1", TableName:="PivotTable1", DefaultVersion _
    :=xlPivotTableVersion12

    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

    Set xlRng = wb1.Sheets(1).Range("A1:C18")

    With wb1.Sheets(1).Shapes(1).Chart
    .SetSourceData xlRng
    End With

    With wb1.Sheets(1).Shapes(1)
    .IncrementLeft 192
    .IncrementTop 14.4
    End With

    wb1.Sheets(1).PivotTables("PivotTable1").AddDataFi eld wb1.Sheets(1).PivotTables( _
    "PivotTable1").PivotFields("Tran_Amount"), "Sum of Tran_Amount", xlSum
    With wb1.Sheets(1).PivotTables("PivotTable1").PivotFiel ds("Period")
    .Orientation = xlRowField
    .Position = 1
    End With
    With wb1.Sheets(1).PivotTables("PivotTable1").PivotFiel ds("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)

    xlApp.Quit

    Set wb1 = Nothing
    Set xlApp = Nothing

  14. #14
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Thanks for telling us what it was that caused the issue.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Creating a pivot table in Excel
    By crowegreg in forum Programming
    Replies: 7
    Last Post: 01-20-2014, 08:32 PM
  2. Access 2010 VBA Creating Pivot Table in Excel
    By Tomlon in forum Programming
    Replies: 1
    Last Post: 02-07-2013, 02:25 PM
  3. Creating Excel pivot table issue
    By Reaper in forum Import/Export Data
    Replies: 1
    Last Post: 06-14-2012, 12:32 PM
  4. Pivot chart or Excel?
    By Daryl2106 in forum Access
    Replies: 1
    Last Post: 05-14-2012, 01:37 PM
  5. Creating a Pivot Chart....
    By spcalan in forum Access
    Replies: 0
    Last Post: 01-08-2009, 03:28 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums