6 Replies Latest reply: Oct 29, 2014 5:04 AM by Deepti Gupta RSS

    Export chart from container by macro

    Mathias Vanden Auweele

      Hey all,

       

      It seems I can only export a chart from a container when the tab of the container is active using this code:

       

              Set obj = ActiveDocument.GetSheetObject("CH01")        ChartName = obj.GetCaption.Name.v

              obj.ExportBitmapToFile "D:\Documents and Settings\All Users\Desktop\Export\" &ChartName &".png"

       

      Is it possible to export the chart even though it isn't activated? How do I do this?

       

      Thanks!

        • Export chart from container by macro
          Miguel Baeyens

          Hi,

           

          If you know the ID of the chart, just change the code above modifying "CH01" by the value of the chart. If you don't know it, go to the chart properties, General tab.

           

          Is that waht you mean?

           

          Miguel

            • Export chart from container by macro
              Mathias Vanden Auweele

              Thank you for your fast response. However the chart in the container is named "CH01" (like I have a CH02 and a CH03). If the tab of CH02 is selected. Then I can only export that chart by using the macro code above. I'm looking for a macro code that can export me the chart CH01 even though the tab CH02 is selected in the container.

                • Export chart from container by macro
                  Mathias Vanden Auweele

                  Sub Export_Dashboard_Chart_Images()

                      SET app = ActiveDocument.GetApplication

                      Dim ExportCharts(7)

                      Dim ContainerIndex(7)

                      Dim ChartTypes(1)

                     

                      ' define chart ID's to be exported

                      ExportCharts(0) = "CH19"

                      ExportCharts(1) = "CH20"

                      ExportCharts(2) = "CH23"

                      ExportCharts(3) = "CH24"

                      ExportCharts(4) = "CH25"

                      ExportCharts(5) = "CH47"

                      ExportCharts(6) = "CH50"

                      ExportCharts(7) = "CH52"

                     

                      ' if chart is part of container, specify index in container, else give -1

                      ContainerIndex(0) = -1

                      ContainerIndex(1) = -1

                      ContainerIndex(2) = 0

                      ContainerIndex(3) = 2

                      ContainerIndex(4) = -1

                      ContainerIndex(5) = -1

                      ContainerIndex(6) = 1

                      ContainerIndex(7) = 3

                     

                      ChartTypes(0) = "Cumulative"

                      ChartTypes(1) = "Absolute"

                     

                      ' point at container properties

                      Set ContainerObj = ActiveDocument.GetSheetObject("CT01")

                      Set ContProp=ContainerObj.GetProperties

                   

                      ' start with cycling trough the chart types

                      For h=lbound(ChartTypes) to ubound(ChartTypes)

                         

                          ActiveDocument.Variables("vDashboardChartType").SetContent ChartTypes(h), true

                          ' give the application time to generate the chart

                          ActiveDocument.getApplication.Refresh

                          ActiveDocument.getApplication.Waitforidle

                                 

                          For i=lbound(ExportCharts) to ubound(ExportCharts)           

                             

                              ' cycle container to relevant tab

                              if ContainerIndex(i) > -1 then

                                  ContProp.SingleObjectActiveIndex = ContainerIndex(i)

                                 

                                  ContainerObj.SetProperties ContProp

                                 

                                  ' give the application time to generate the chart

                                  ActiveDocument.getApplication.Refresh

                                  ActiveDocument.getApplication.Waitforidle

                              end if

                             

                              ' set export to be exported

                              Set obj = ActiveDocument.GetSheetObject(ExportCharts(i))

                             

                              ' incase no caption is defined, use iterative number to identify problem

                              if obj.GetCaption.Name.v="" then

                                  ChartName = i

                              else

                                  ChartName = obj.GetCaption.Name.v

                              end if

                             

                              ' final export

                              obj.ExportBitmapToFile "D:\Documents and Settings\All Users\Desktop\Export\" &ChartName &".png"

                          Next

                      Next

                  End Sub

              • Re: Export chart from container by macro

                Hi friends,

                 

                1. How can we export all the objects of the sheet in same UI format into excel??

                 

                2. Also, the below macro works fine with a table object - CH25 but not when I use the ID of a container object (CT01). Can any one help how can I export all the objects of a container by looping or by any other means?

                 

                Sub Test1

                set obj = ActiveDocument.GetSheetObject("CH25")

                obj.ExportBiff "C:\test.xls"

                End Sub

                 

                 

                Thanks,

                Deepti Gupta