Export to Excel - Chart & Table - Fast Changing Object

[nikhilgandhi715]

Hi All,

I want to export the chart and table to excel of a fast changing object. I am using below code; however it is exporting the table only and not the Chart as image.

set chart = ActiveDocument.GetSheetObject("CH669")
intObjectType = chart.GetObjectType
'Msgbox "Business Mix - Table: " & intObjectType
if intObjectType = 12 then
chart.SetChartType 5
else
chart.SetChartType 0
end if

ActiveDocument.GetSheetObject("CH669").CopyTableToClipboard true
ActiveDocument.GetApplication.WaitForIdle 2000
XLSheet1.Paste XLSheet1.Range("A1")

set chart = ActiveDocument.GetSheetObject("CH669")
intObjectType = chart.GetObjectType
'Msgbox "Business Mix - Image: " & intObjectType
if intObjectType = 11 then
chart.SetChartType 0
else
chart.SetChartType 5
end if

ActiveDocument.GetSheetObject("CH669").CopyBitmapToClipboard
ActiveDocument.GetApplication.WaitForIdle 2000
XLSheet1.Paste XLSheet1.Range("A" & XLSheet1.Range("A65535").End(-4162).Row + 2)

I have been trying this since morning... any help would be much appreciated.

Thanks in advance.

Nikhil.

[marcus_sommer]

Without changing the ObjectType within the routine it will always copy/paste once. I'm not sure if this code from APIGuide.qvw will work for changing, but you will find there more examples and descriptions.

set chart = ActiveDocument.GetSheetObject("CH01")

set p = chart.GetProperties

p.GraphLayout.GraphMode = 1  'pie"

chart.SetProperties p

- Marcus

[nikhilgandhi715]

Hi Marcus,

Thanks for the reply.

As we have the chart type details, Do you know where can I get the details for the graph mode for each graph.

Nikhil.

[marcus_sommer]

Possible values/parameters can you see in APIGuide.qvw within the sheet "Automation Members" by hoovering over the column "Comment".

- Marcus

[nikhilgandhi715]

Thanks Marcus... I shall check if that works or not...

[nikhilgandhi715]

Got it working

Below is the code that worked for me

objCurrentSheet.Range("A1").Select
ActiveDocument.ActiveSheet.ActivateAll
Objects = ActiveDocument.ActiveSheet.GetActiveSheetObjects()
x = 0
For j = lbound(Objects) to ubound(Objects)
set objProp = Objects(j).GetProperties
if Objects(j).GetObjectID = "Document\CH693" Then
If Objects(j).GetObjectType = 16 Then
Objects(j).SetObjectType 5
Call Objects(j).CopyTableToClipboard(true)
objCurrentSheet.Range("A1").Offset(x,0).Select
objExcelDoc.Sheets(sheetName).Paste
With objExcelApp.Selection
.WrapText = False
.ShrinkToFit = False
.Columns.AutoFit
.Rows.AutoFit
End With
Objects(j).SetObjectType 6
Call Objects(j).CopyBitmapToClipboard()
objCurrentSheet.Range("A15").Offset(x,0).Select
objExcelDoc.Sheets(sheetName).Paste
End if
End if
Next

[#7]

Hi Nikhil,

I need some help from you.

can you please send me the total code you wrote for exporting into excel.

thanks,

Srinath.

[nikhilgandhi715]

Hi apache77,

Apologies for not coming back to you.

Do you still need the code?