Desparately need help. 2 of 12 charts on 1 page can't be saved as webpages?

[FatherGuido]

Posted to Programming & Charting

Windows 2000 with Excel 2002

I have created an excel spreadsheet that has a sheet containg 12 charts.
I recorded a macro as I saved them to my web location, then ran thought
it manually. I discovered 2 of the 12 charts would fail (see actual
recorded macro below). Why? Each chart was originally copied from the
same chart and just had its source data changed. I tried changing the
order, and re-writing the code from other parts that worked -- but the
same two charts always failed. Again why, they are identical except for
the chart numbers.

Test macro exactly as recorded.

Sub Macro15()
'
' Macro15 Macro
' Macro recorded 8/14/03 by Norman Dillon
'
'
ActiveSheet.ChartObjects("Chart 9").Activate
ActiveChart.ChartArea.Select
With ActiveWorkbook.PublishObjects.Add(xlSourceChart, _
"D:\Profiles\ndillon\Desktop\Norm1.htm", "Web", "Chart 9",
xlHtmlStatic, _
"DOH_26509", "")
.Publish (True)
.AutoRepublish = False
End With
ActiveSheet.ChartObjects("Chart 9").Activate
ChDir "D:\Profiles\ndillon\Desktop"
ActiveWindow.Visible = False
Windows("DOH.xls").Activate
ActiveSheet.ChartObjects("Chart 30").Activate
ActiveChart.ChartArea.Select
With ActiveWorkbook.PublishObjects.Add(xlSourceChart, _
"D:\Profiles\ndillon\Desktop\Page2.htm", "Web", "Chart 30",
xlHtmlStatic, _
"DOH_27930", "")
.Publish (True)
.AutoRepublish = False
End With
ActiveSheet.ChartObjects("Chart 30").Activate
End Sub

Run-time error '1004';
Unable to get the ChartObjects property of the Worksheet Class

It always crashes on line 'ActiveSheet.ChartObjects("Chart
30").Activate'

The other chart also fails in the exact same location.

I really need to get this working ASAP if I want to go on vacation
Saturday. *L*

Any/all help greatly appreciated!!!

Thanks in advance.

Norm



Father Guido
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
I plan on living forever... so far, so good

 

[Jon Peltier]

Norm-

Recorded macros often have excess steps. Do you need these two lines to
add the chart to the published objects:

ActiveSheet.ChartObjects("Chart 30").Activate
ActiveChart.ChartArea.Select

Another obvious question is whether you have charts named "Chart 30" and
"Chart 9". You could have a different set of charts than you had when
the macro was recorded.

You could try this **UNTESTED** code, adapted from my web page at
http://www.geocities.com/jonpeltier/Excel/XL_PPT.html, which copies the
active chart, or each of the selected charts, to powerpoint. If I fixed
it properly, it adds the selected chart, or each of the selected charts,
to your PublishObjects routine. If I goofed it up, at least you have a
framework, and a few hours to the end of Friday.

To select multiple charts, select one, then hold down Shift while
selecting others.

Sub ChartsIntoPublishObjects()
Dim iShapeIx As Integer, iShapeCt As Integer
Dim myShape As Shape, myChart As ChartObject
Dim bCopied As Boolean, iPg as Integer
Dim sPage as String, sDOH as String

iPg = 0
If ActiveChart Is Nothing Then
''' SELECTION IS NOT A SINGLE CHART
On Error Resume Next
iShapeCt = Selection.ShapeRange.count
If Err Then
MsgBox "Select charts and try again", vbCritical, _
"Nothing Selected"
Exit Sub
End If
On Error GoTo 0
For Each myShape In Selection.ShapeRange
''' IS SHAPE A CHART?
On Error Resume Next
Set myChart = ActiveSheet.ChartObjects(myShape.name)
If Not Err Then
iPg = iPg + 1
'' NOTE: ADJUST THIS, I DON'T KNOW WHAT YOU NEED
sPage = "D:\Profiles\ndillon\Desktop\Page" & iPg & ".htm"
'' NOTE: FIX THIS, I DON'T KNOW HOW IT SHOULD LOOK
sDOH = "DOH_" & 27930 + iPg
bCopied = CopyChartToPowerPoint(sPage, sDOH, myChart)
End If
On Error GoTo 0
Next
Else
''' CHART ELEMENT OR SINGLE CHART IS SELECTED
Set myChart = ActiveChart.Parent
'' NOTE: ADJUST THIS, I DON'T KNOW WHAT YOU NEED
sPage = "D:\Profiles\ndillon\Desktop\Page1.htm"
'' NOTE: FIX THIS, I DON'T KNOW HOW IT SHOULD LOOK
sDOH = "DOH_"27931
bCopied = PublishChart(sPage, sDOH, myChart)
End If

Set myChart = Nothing
Set myShape = Nothing
End Sub

Function PublishChart(sPage As String, _
sDOH As String, oChart As ChartObject)
PublishChart = False
With ActiveWorkbook.PublishObjects.Add(xlSourceChart, _
sPage, "Web", oChart.Name, xlHtmlStatic, _
sDOH, "")
.Publish (True)
.AutoRepublish = False
End With
End Function

- Jon

 

[FatherGuido]

Thanks Jon, I was unable to try your code today. But I will follow up
when I return in 2 weeks. In the meantime what I did, that appeared to
work, I won't know for awhile, was to convert the 11 charts on one sheet
into 11 separate charts sheets, then just select the sheets and save
them as individual html files. Worked today, hopefully it'll last for a
couple of weeks.

Thanks again,

Norm

Norm-

Recorded macros often have excess steps. Do you need these two lines to
add the chart to the published objects:

ActiveSheet.ChartObjects("Chart 30").Activate
ActiveChart.ChartArea.Select

Another obvious question is whether you have charts named "Chart 30" and
"Chart 9". You could have a different set of charts than you had when
the macro was recorded.

You could try this **UNTESTED** code, adapted from my web page at
http://www.geocities.com/jonpeltier/Excel/XL_PPT.html, which copies the
active chart, or each of the selected charts, to powerpoint. If I fixed
it properly, it adds the selected chart, or each of the selected charts,
to your PublishObjects routine. If I goofed it up, at least you have a
framework, and a few hours to the end of Friday.

To select multiple charts, select one, then hold down Shift while
selecting others.

Sub ChartsIntoPublishObjects()
Dim iShapeIx As Integer, iShapeCt As Integer
Dim myShape As Shape, myChart As ChartObject
Dim bCopied As Boolean, iPg as Integer
Dim sPage as String, sDOH as String

iPg = 0
If ActiveChart Is Nothing Then
''' SELECTION IS NOT A SINGLE CHART
On Error Resume Next
iShapeCt = Selection.ShapeRange.count
If Err Then
MsgBox "Select charts and try again", vbCritical, _
"Nothing Selected"
Exit Sub
End If
On Error GoTo 0
For Each myShape In Selection.ShapeRange
''' IS SHAPE A CHART?
On Error Resume Next
Set myChart = ActiveSheet.ChartObjects(myShape.name)
If Not Err Then
iPg = iPg + 1
'' NOTE: ADJUST THIS, I DON'T KNOW WHAT YOU NEED
sPage = "D:\Profiles\ndillon\Desktop\Page" & iPg & ".htm"
'' NOTE: FIX THIS, I DON'T KNOW HOW IT SHOULD LOOK
sDOH = "DOH_" & 27930 + iPg
bCopied = CopyChartToPowerPoint(sPage, sDOH, myChart)
End If
On Error GoTo 0
Next
Else
''' CHART ELEMENT OR SINGLE CHART IS SELECTED
Set myChart = ActiveChart.Parent
'' NOTE: ADJUST THIS, I DON'T KNOW WHAT YOU NEED
sPage = "D:\Profiles\ndillon\Desktop\Page1.htm"
'' NOTE: FIX THIS, I DON'T KNOW HOW IT SHOULD LOOK
sDOH = "DOH_"27931
bCopied = PublishChart(sPage, sDOH, myChart)
End If

Set myChart = Nothing
Set myShape = Nothing
End Sub

Function PublishChart(sPage As String, _
sDOH As String, oChart As ChartObject)
PublishChart = False
With ActiveWorkbook.PublishObjects.Add(xlSourceChart, _
sPage, "Web", oChart.Name, xlHtmlStatic, _
sDOH, "")
.Publish (True)
.AutoRepublish = False
End With
End Function

- Jon

Father Guido
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
I plan on living forever... so far, so good

 

[Jon Peltier]

Norm -

If what you're doing is simply exporting the charts as GIF files, it's
much easier than I showed you before. Download and install John
Walkenbach's Chart Tools add-in, select a chart, and select JWalk Chart
Tools from the Chart menu. Click on the Chart tab, then pick the export
option you want (This chart, All charts on this sheet, or All charts in
the workbook). The chart names are rudimentary: "Sheet 1 Chart 1.gif",
for example, but you can rename them. Or you can just use the relevant
bits of code to further customize your export:

Sub ExportMyCharts()
Dim myPath as String
Dim myName as String
Dim myChtOb as ChartObject
myPath = ActiveSheet.Cells(1, 1).Value '' PATH IN CELL A1
If Right(myPath, 1) <> "\" Then
myPath = myPath & "\"
End If
For Each myChtOb in ActiveSheet.ChartObjects
myName = myChtOb.TopLeftCell.Offset(-1, 0)
'' NAME IN CELL ABOVE TOP LEFT OF CHART
myChtOb.Chart.Export myPath & myName & ".gif", "GIF"
Next
End Sub

- Jon

 

[FatherGuido]

Norm -

If what you're doing is simply exporting the charts as GIF files, it's
much easier than I showed you before. Download and install John
Walkenbach's Chart Tools add-in, select a chart, and select JWalk Chart
Tools from the Chart menu. Click on the Chart tab, then pick the export
option you want (This chart, All charts on this sheet, or All charts in
the workbook). The chart names are rudimentary: "Sheet 1 Chart 1.gif",
for example, but you can rename them. Or you can just use the relevant
bits of code to further customize your export:

Sub ExportMyCharts()
Dim myPath as String
Dim myName as String
Dim myChtOb as ChartObject
myPath = ActiveSheet.Cells(1, 1).Value '' PATH IN CELL A1
If Right(myPath, 1) <> "\" Then
myPath = myPath & "\"
End If
For Each myChtOb in ActiveSheet.ChartObjects
myName = myChtOb.TopLeftCell.Offset(-1, 0)
'' NAME IN CELL ABOVE TOP LEFT OF CHART
myChtOb.Chart.Export myPath & myName & ".gif", "GIF"
Next
End Sub

- Jon

_______________________
Thanks Jon,

What I did seems to be working OK. I will try your suggestion in the
next phase of our test reporting. I've had problems in the past
incorporating the use of Add-ins in macros, using the macro recorder to
get the base code. But looking at your code above, even though I don't
understand it all, I see you're going to sequence through each chart.
The names don't matter as I can just address the proper chart using
HTML.

Thanks again!

Norm


Father Guido
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
I plan on living forever... so far, so good