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
