1 Reply Latest reply: Sep 13, 2017 11:43 PM by Tamil Nagaraj RSS

    Macro to export pivot to excel and create a Line graph

    siva koya

      Hi, I am using below macro to export a pivot table to excel and create a line graph. Macro exports the pivot to excel and inserting a graph, but it is stopping at xlApp.ActiveChart.ChartType = xlLine. There is some problem with this statement, it stops at this step.

       

      Need help with debugging this.

       

      SUB AdHocExport_automated
      confirmation = MSGBOX ("Ad hoc Excel export has been initiated." & vbCrLf & "Do you wish to continue?"& vbCrLf &"", 36, "Export Confirmation")
      IF confirmation = 7 THEN
      EXIT SUB
      END IF
      DIM xlApp

       

      SET xlApp = CREATEOBJECT("Excel.Application")
      xlApp.Visible = TRUE
      SET xlBook = xlApp.Workbooks.Add
      SET xlSheet = xlBook.Worksheets("Sheet1")
      SET Doc = ActiveDocument

       

      Doc.GetApplication.WaitForIdle
      Doc.GetSheetObject(var).CopyTableToClipBoard TRUE
      xlApp.ActiveSheet.Paste
      xlSheet.Cells.EntireColumn.AutoFit
      xlSheet.Cells.EntireRow.AutoFit

       

       

       

       

      xlApp.Worksheets("Sheet1").Select

       


      xlApp.Range("I4").Select
      xlApp.ActiveSheet.Shapes.AddChart.Select
      xlApp.ActiveChart.ChartType = xlLine
      xlApp.ActiveSheet.ChartObjects("Chart 1").Activate
      xlApp.ActiveChart.SeriesCollection.NewSeries
      xlApp.ActiveChart.SeriesCollection(1).Name = "=Sheet1!$B$1"
      xlApp.ActiveChart.SeriesCollection(1).Values = "=Sheet1!$B$2:$B$56"
      xlApp.ActiveChart.SeriesCollection(1).XValues = "=Sheet1!$A$2:$A$56"
      xlApp.ActiveChart.SeriesCollection.NewSeries
      xlApp.ActiveChart.SeriesCollection(2).Name = "=Sheet1!$C$1"
      xlApp.ActiveChart.SeriesCollection(2).Values = "=Sheet1!$C$2:$C$44"
      xlApp.ActiveChart.SeriesCollection(2).XValues = "=Sheet1!$A$2:$A$56"
      xlApp.ActiveChart.SeriesCollection.NewSeries
      xlApp.ActiveChart.SeriesCollection(3).Name = "=Sheet1!$D$1"
      xlApp.ActiveChart.SeriesCollection(3).Values = "=Sheet1!$D$2:$D$32"
      xlApp.ActiveChart.SeriesCollection(3).XValues = "=Sheet1!$A$2:$A$56"
      xlApp.ActiveChart.SeriesCollection.NewSeries
      xlApp.ActiveChart.SeriesCollection(4).Name = "=Sheet1!$E$1"
      xlApp.ActiveChart.SeriesCollection(4).Values = "=Sheet1!$E$2:$E$20"
      xlApp.ActiveChart.SeriesCollection(4).XValues = "=Sheet1!$A$2:$A$56"
      xlApp.ActiveChart.SeriesCollection.NewSeries
      xlApp.ActiveChart.SeriesCollection(5).Name = "=Sheet1!$F$1"
      xlApp.ActiveChart.SeriesCollection(5).Values = "=Sheet1!$F$2:$F$8"
      xlApp.ActiveChart.SeriesCollection(5).XValues = "=Sheet1!$A$2:$A$56"
       
      MSGBOX "Ad hoc Excel export is complete!",64,"Task Completion Notification"
      END SUB