3 Replies Latest reply: Sep 23, 2015 2:53 AM by Marcus Sommer RSS

    Get objects used in a container using vba?

    rashid muhammad


      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


        '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))



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




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

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


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

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




        else if Obj.GetObjectType =36 then

        NewIndex = 0


        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 )




        end if

        End if


      '    Next 

          Set PPSlide = Nothing

        Set PPPres = Nothing

      End Sub

        • Re: Get objects used in a container using vba?
          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

            • Re: Get objects used in a container using vba?
              rashid muhammad

              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?



                • Re: Get objects used in a container using vba?
                  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