Hello,
I am trying to create an picture inside of a chart object made of cells copied from a worksheet and shapes added to the chart. This is what my code looks like
Sub SaveImage(predictionTable As Range, forecastTable As Range, FileName As String)
Dim Cht As Chart, bScreen As Boolean, Shp As Shape
bScreen = Application.ScreenUpdating
Application.ScreenUpdating = False
Set Cht = Workbooks.Add(xlChart).Charts(1)
Cht.ChartArea.Clear
Cht.Shapes.AddTextbox(msoTextOrientationHorizontal, 0, 0, 200, 50).TextFrame.Characters.Text = "Returns Table"
With Cht.Shapes(1)
.Left = 800
.Top = 0
.Name = "Returns Title"
End With
predictionTable.CopyPicture xlScreen, xlPicture
Cht.Paste
Dim nc As Integer: nc = Cht.Shapes.Count
With Cht.Shapes(nc)
.Name = "Returns Table"
.ScaleHeight 0.7, msoTrue
.IncrementLeft 200
.IncrementTop 50
End With
Cht.Export FileName, "JPEG", False
Cht.Parent.Close False
Application.ScreenUpdating = bScreen
End Sub
The code is fine if i run through it in Debug mode, however if i run it normally it gives mea an 'the index into the specified location is out of bounds' error, even though nc is in fact 2.
Somehow I am unable access the Picture I copyPaste into the chart...
The weird thing is that if i go through the code line by line in debug mode is works fine. Any suggestions on how to get around this/what is going on?
Thanks for your help,
ikf
I am trying to create an picture inside of a chart object made of cells copied from a worksheet and shapes added to the chart. This is what my code looks like
Sub SaveImage(predictionTable As Range, forecastTable As Range, FileName As String)
Dim Cht As Chart, bScreen As Boolean, Shp As Shape
bScreen = Application.ScreenUpdating
Application.ScreenUpdating = False
Set Cht = Workbooks.Add(xlChart).Charts(1)
Cht.ChartArea.Clear
Cht.Shapes.AddTextbox(msoTextOrientationHorizontal, 0, 0, 200, 50).TextFrame.Characters.Text = "Returns Table"
With Cht.Shapes(1)
.Left = 800
.Top = 0
.Name = "Returns Title"
End With
predictionTable.CopyPicture xlScreen, xlPicture
Cht.Paste
Dim nc As Integer: nc = Cht.Shapes.Count
With Cht.Shapes(nc)
.Name = "Returns Table"
.ScaleHeight 0.7, msoTrue
.IncrementLeft 200
.IncrementTop 50
End With
Cht.Export FileName, "JPEG", False
Cht.Parent.Close False
Application.ScreenUpdating = bScreen
End Sub
The code is fine if i run through it in Debug mode, however if i run it normally it gives mea an 'the index into the specified location is out of bounds' error, even though nc is in fact 2.
Somehow I am unable access the Picture I copyPaste into the chart...
The weird thing is that if i go through the code line by line in debug mode is works fine. Any suggestions on how to get around this/what is going on?
Thanks for your help,
ikf