VBA error 2147024809
- Thread starter geotzi
- Start date
Hi,
The error occurs " exo_wsheet.ChartObjects(ChartName).Chart.CopyPicture Appearance:=xlScreen, Size:=xlScreen, Format:=xlPicture "
So the logic is that the macro creates a presentation with some graphs.
A ppt opens and some graphs are copied & pasted in the presentation deck. The number, names and the format of the graphs are predefined ranges in the workbook.
NARAYANK991
Excel Ninja
Hi Georgia ,
Resolving this problem is so easy if the workbook with the code is uploaded ; debugging an error , especially a Run Time Error is that much more difficult , since a lot of RTEs can be due to the data.
A lot of time will be spent in various members making suggestions which you will either confirm or reject.
This particular error has so many reasons , one of which can be the chart name ; can you say what is the value of the variable l when this error occurs i.e. does this error occur in the first pass through the For loop or is it after some iterations through the loop ?
What does the variable ChartName contain when the error occurs ? Does this chart exist in the worksheet ?
Narayan
Resolving this problem is so easy if the workbook with the code is uploaded ; debugging an error , especially a Run Time Error is that much more difficult , since a lot of RTEs can be due to the data.
A lot of time will be spent in various members making suggestions which you will either confirm or reject.
This particular error has so many reasons , one of which can be the chart name ; can you say what is the value of the variable l when this error occurs i.e. does this error occur in the first pass through the For loop or is it after some iterations through the loop ?
What does the variable ChartName contain when the error occurs ? Does this chart exist in the worksheet ?
Narayan
The MsgBox ChartName gives me the name of each chart (Chart1, Chart2, Chart3..etc Chart6).The value of the ChartName is predefined in a table(chart_list2) When the loop is ending it shows a blank message for the ChartName Value.
When the error occurs the I variable takes the value 9 and then i receive the attached error message. In the end of this loop I have 10 slides with the last one to be blank. hope that this will help & thanks for the help
Just a note the graphs are 6 in my example but there are 3 slides by default when the ppt opens
Attachments
The VBA error gives also a message " This member can only be accessed for a chart object"
Tnx,
Georgia.
Hi, i tried it but i am still taking the same error.
Hmm. Okay, trying to debug. Can you replace the problem line with this
and let us know what the 3 messages say, if anything unusual
Code:
Dim ch As Chart
Set ch = exo_wsheet.ChartObjects(ChartName).Chart
'Debug info
MsgBox "Sheet is: " & exo_wsheet.Name
MsgBox "Chart Object is: " & ChartName
MsgBox "Chart Item is: " & ch.Name
ch.CopyPicture Appearance:=xlScreen, Size:=xlScreen, Format:=xlPictureHello,
the MsgBox gives the following results:
Sheet is: Charts
Chart Object is: Chart1
Chart item is: Charts Chart1
the loop runs 6 times and gives the right MSgbox messages.
I receive again the same error and the debug now indicates the line
"Set ch = exo_wsheet.ChartObjects(ChartName).Chart"
Tnx,
Georgia
Yes the total number of charts are 6 which are copied correctly.The MsgBox ChartName gives me Chart1 Chart2.. and Chart6 and the last msgbox is blank.
Do you think that error occus because the loop runs one more time and there is no object to add in the slide. ( So the macro creates 7 slides in total and the last one is blank)
First idea would be to remove the blank lines, if possible.
Alternative idea is to add an If statement to check if ChartName is blank. First idea:
Alternative idea is to add an If statement to check if ChartName is blank. First idea:
Code:
For Each exo_listrow_cht In config.ListObjects("chart_list2").ListRows
ChartNo = exo_listrow_cht.Range.Cells(1, exo_listcol_cht("Chart_No").Index).Value
ChartName = exo_listrow_cht.Range.Cells(1, exo_listcol_cht("Chart_name").Index).Value
ChartField = "chart_tbl16"
'=============
'NEW LINE HERE
'=============
If ChartName = "" Then GoTo SkipRow
Set exo_listcol_fld = config.ListObjects(ChartField).ListColumns
l = l + 1
Set ppo_slide = ppo_pres.Slides.AddSlide(ppo_app.ActivePresentation.Slides.Count + 1, ppo_ctpl)
exo_wsheet.ChartObjects(ChartName).Chart.CopyPicture Appearance:=xlScreen, Size:=xlScreen, Format:=xlPicture
ppo_app.ActivePresentation.Slides(l).Shapes.Paste
For Each exo_listrow_fld In config.ListObjects(ChartField).ListRows
'exo_wsheet.Range(exo_listrow_fld.Range.Cells(1, exo_listcol_fld("rangename").Index).Value).CopyPicture Appearance:=xlScreen, Format:=xlPicture
exo_wsheet.Range(exo_listrow_fld.Range.Cells(1, exo_listcol_fld("rangename").Index).Value).Copy
ppo_slide.Select
i = ppo_app.ActivePresentation.Slides(l).Shapes.Count
ppo_app.ActivePresentation.Slides(l).Shapes.PasteSpecial , , , , , msoTrue
'ppo_app.ActivePresentation.Slides(l).Shapes.Paste
Set ppo_shape = ppo_app.ActivePresentation.Slides(l).Shapes(i + 1)
Application.CutCopyMode = False 'clear the clipboard
ppo_shape.Name = exo_listrow_fld.Range.Cells(1, exo_listcol_fld("ShapeName").Index)
ppo_shape.Top = exo_listrow_fld.Range.Cells(1, exo_listcol_fld("top").Index)
ppo_shape.Left = exo_listrow_fld.Range.Cells(1, exo_listcol_fld("left").Index)
Next
'BOUNCES TO HERE
SkipRow:
Next