Macro to export pivot to excel and create a Line graph

phoenix — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Macro to export pivot to excel and create a Line graph

tamilarasu — Thu, 14 Sep 2017 03:43:06 GMT

Try this,

xlApp.ActiveChart.ChartType = 4

Reference Link: XlChartType Enumeration [Excel 2007 Developer Reference]

topic Macro to export pivot to excel and create a Line graph in QlikView

Macro to export pivot to excel and create a Line graph

Re: Macro to export pivot to excel and create a Line graph