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

    Get objects used in a container using vba?

      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

        • 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?

              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

                • 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