Suddenly this code fails most of the time at the statement
because there are no Shapes in the Chart Object!
Does anyone know why pasting the Chart would sometimes not result in a Shape?
Code:
With .chart.Shapes(1)
Does anyone know why pasting the Chart would sometimes not result in a Shape?
Code:
Public Sub saveOrgChart(title As String)
Dim shp As Shape, sht As Worksheet
Dim chtObj As ChartObject
Dim objTemp As Object
Dim objPicTemp As Object
Dim sngWidth As Single, sngHeight As Single, fName As String, i As Integer
For Each shp In ActiveSheet.Shapes
If shp.Type = msoSmartArt Then
Call ConfigureShape(shp)
Set objTemp = shp
End If
Next shp
Set sht = Workbooks("GraphicsExporter.xla").Worksheets("PicHolder")
Set chtObj = sht.ChartObjects(1)
Do While Not Application.Ready: Sleep 10: Loop
objTemp.Copy
Do While Not Application.Ready: Sleep 10: Loop
Set objPicTemp = ActiveSheet.Pictures.Paste
With objPicTemp
sngWidth = .Width
sngHeight = .Height
End With
With chtObj
.Width = sngWidth + 20
.Height = sngHeight + 20
End With
With objPicTemp
Do While Not Application.Ready: Sleep 10: Loop
.Copy
Do While Not Application.Ready: Sleep 10: Loop
.Delete
End With
Set objPicTemp = Nothing
With chtObj
For i = .chart.Shapes.Count To 1 Step -1
.chart.Shapes(i).Delete
Next i
Do While Not Application.Ready: Sleep 10: Loop
.chart.Paste
Do While Not Application.Ready: Sleep 10: Loop
With .chart.Shapes(1)
.Placement = xlMove
.Left = -4
.Top = -4
End With
.Width = sngWidth + 1
.Height = sngHeight + 1
fName = savePath & "\Org Charts\" & title
' .Chart.Export fName & ".jpg"
.Activate
sht.Visible = xlSheetVisible
.chart.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fName & ".pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
sht.Visible = xlSheetHidden
myWorkbook.Sheets(mySheet.Name).Activate
End With
Set chtObj = Nothing
End Sub