Get objects used in a container using vba?

[rmuhammad]

Hi

Experts I need help. I have sheet with 2 charts and a container. Inside container I have 3 charts. I am trying to export charts from the sheet to PPT. One chart per slide. using following code charts are exporting okay however when I am trying to get object ID from container object I am getting blank.

Please help.

Sub Export_All_Chart_Images_Current_Sheet()

    set objPPT = CreateObject("PowerPoint.Application")

  objPPT.Visible = True

  Set objPresentation = objPPT.Presentations.Add

  'For i = 0 To ActiveDocument.NoOfSheets - 1

  'msgbox(ActiveDocument.ActiveSheet.GetProperties.SheetId)

  'set MySheetID = right(ActiveDocument.ActiveSheet.GetProperties.SheetId,4) '<<<<< this has problem

  'set MySheetID= CHR(34)&right(ActiveDocument.ActiveSheet.GetProperties.SheetId,4)&CHR(34)

        Set MySheet = ActiveDocument.GetSheetByid (right(ActiveDocument.ActiveSheet.GetProperties.SheetId,4))

        MyCharts=MySheet.GetSheetObjects

            For X =lbound(MyCharts) to ubound(MyCharts)

                   'msgbox(X)

                    'msgbox("Chart: " & MyCharts(X).GetObjectId)

                     Set obj = ActiveDocument.GetSheetObject(MyCharts(X).GetObjectId)

                     'msgbox(Obj.GetObjectType)

                     IF Obj.GetObjectType >= 10 AND Obj.GetObjectType =< 16 Then

                     Set PPSlide = objPresentation.Slides.Add(X+1,11)          

  obj.CopyBitmapToClipboard

  PPSlide.Shapes.Paste

  else if Obj.GetObjectType =36 then

  NewIndex = 0

  msgbox(MyCharts(X).GetObjectId)

  Set ContainerObj = ActiveDocument.GetSheetObject(MyCharts(X).GetObjectId)

  set ContProp=ContainerObj.GetProperties

  for i = 0 to ContProp.ContainedObjects.Count - 1

  msgbox("i="&i&"|"&ContProp.ContainedObjects(i).Text.v )

  next

  end if

  End if

            Next

'    Next 

    Set PPSlide = Nothing

  Set PPPres = Nothing

End Sub

[marcus_sommer]

Most often it's a lot easier to use specialized print- and export-objects on a (hidden) sheet and then there is no need to develop (such) complex routines.

- Marcus

[rmuhammad]

Hi Marcus, thanks for reply.

Can you please provide some how steps for the use of specialized print- and export-objects on a (hidden) sheet?

Thanks

[marcus_sommer]

It's quite easy. At first you creates a new sheet and used a visibilty-condition for it, maybe related to you or a certain user-group per querying the osuser() or simple 1 > 2 (then you could only grab the sheet within the document properties).

Then you makes simple copy and paste from your print- and export-objects into this sheet - then you might adjust some things on those objects like more column-widths, other number-fomatting, other colors but many more is possible (if there are many objects you could minimize them and group them into a textbox-frame - minimizing had no effect to exports and printing in opposite to hiding them like the unseen objects within a container).

And then you used those objects within your print-reports or export-macros.

- Marcus