Greetings,
I've written a macro that creates PowerPoint charts from data in an Excel workbook.
It is nearly functional, except for a
failed error message.
The macro is run in a PowerPoint instance with no slides. It creates the correct PowerPoint chart with the data from the first Excel workbook sheet, but fails at this line
when it gets to the second sheet in the Excel workbook. Specifically it says method
when I hover over the line in the VBA editor. Also,
and
still refer to the 1st worksheets data range.
This leads me to believe that my:
References are not properly cycling through to the next sheet in the Excel workbook. This is weird because when I use a static range such as in
, the macro goes through the sheets as it should.
I greatly appreciate any help. My full macro is below:
I've written a macro that creates PowerPoint charts from data in an Excel workbook.
It is nearly functional, except for a
Code:
Method ‘Range’ of object ‘_Worksheet’The macro is run in a PowerPoint instance with no slides. It creates the correct PowerPoint chart with the data from the first Excel workbook sheet, but fails at this line
Code:
pptWorkSheet.ListObjects("Table1").Resize pptWorkSheet.Range(Cells(1, 1), Cells(LastRow, LastColumn))
Code:
'Cells' of object '_Global' failed'
Code:
LastRow
Code:
LastColumnThis leads me to believe that my:
Code:
LastRow = xlWS.Range("A1").SpecialCells(xlCellTypeLastCell).Row
LastColumn =xlWS.Range("A1").SpecialCells(xlCellTypeLastCell).ColumnReferences are not properly cycling through to the next sheet in the Excel workbook. This is weird because when I use a static range such as in
Code:
pptWorkSheet.Range("A1:B5")I greatly appreciate any help. My full macro is below:
Code:
Sub CreateChartAllWKsv6()
''''' Cleaned (i.e. syntax) version of v5
'''''Impt
'''This macro can
''' 1.add charts of different types
''' 2. resize chart data range to fit worksheet data
Dim myChart As Chart
Dim pptChartData As ChartData
Dim pptWorkBook As Excel.Workbook
Dim pptWorkSheet As Excel.Worksheet
Dim xlApp As Excel.Application
Dim xlWB As Workbook
Dim xlWS As Worksheet
Dim CurSlide As Slide 'new from update
Dim LastRow As Long ' 8/22
Dim LastColumn As Long ' 8/22
' Create new excel instance and open relevant workbook
Set xlApp = New Excel.Application
xlApp.Visible = True 'Make Excel visable
Set xlWB = xlApp.Workbooks.Open("C:\filepath\ExcelData_PPTChartIteration.xlsm", True, False) 'Open relevant workbook
'Loop through each worksheet in xlWB and transfer data to new pptWorkBook and create new PowerPoint chart
For Each xlWS In xlWB.Worksheets
'Add a new slide where we will create the PowerPoint worksheet and chart
Set CurSlide = ActivePresentation.Slides.Add(ActivePresentation.Slides.Count + 1, ppLayoutText) 'Slide types:
ActiveWindow.View.GotoSlide ActivePresentation.Slides.Count
' Create the chart and set a reference to the chart data.
Set myChart = CurSlide.Shapes.AddChart(xlColumn).Chart 'Chart types: https://msdn.microsoft.com/en-us/library/office/ff837417.aspx
Set pptChartData = myChart.ChartData
' Set the PowerPoint Workbook and Worksheet references.
Set pptWorkBook = pptChartData.Workbook
Set pptWorkSheet = pptWorkBook.Worksheets("Sheet1") 'From Update
'Find Last Row and Column of xlWS
LastRow = xlWS.Cells(1, 1).SpecialCells(xlCellTypeLastCell).Row
LastColumn = xlWS.Cells(1, 1).SpecialCells(xlCellTypeLastCell).Column
' Add the data to the PowerPoint workbook.
pptWorkSheet.ListObjects("Table1").Resize pptWorkSheet.Range(Cells(1, 1), Cells(LastRow, LastColumn)) '<~ Macro breaks down here on second sheet. Works perfect on 1st sheet
pptWorkSheet.Range("a2:z100").Value = xlWS.Range("a2:z100").Value '!!! IMPORTANT: FOR SOME REASON YOU CANNOT PUT A REFERENCE TO A2 OR THE CHART RANGE WILL RESIZE BACK TO THE DEFAULT. I SUSPECT THIS IT BECAUSE OF THE SERIES NAME
' Apply styles to the chart.
With myChart
.ChartStyle = 4
.ApplyLayout 4
.ClearToMatchStyle
End With
'Add Title
myChart.HasTitle = True
'Format title
With myChart.ChartTitle
.Characters.Font.Size = 18
.Text = "Test Chart"
End With
' Add the axis title.
With myChart.Axes(xlValue)
.HasTitle = True
.AxisTitle.Text = "Units"
End With
'Apply data labels
myChart.ApplyDataLabels
'Close PowerPoint worksheet
myChart.ChartData.Workbook.Close
Next xlWS
' Clean up the references.
Set pptWorkSheet = Nothing
' pptWorkBook.Application.Quit
Set pptWorkBook = Nothing
Set pptChartData = Nothing
Set myChart = Nothing
'Clean up Excel references.
Set xlApp = Nothing
'Option to close excel workbook
xlWB.Close
'Option to close the excel application
'xlApp.Quit
End Sub
