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 Angel Baeyens de Arce

          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
                      crystles

                      I have tried using this code to export a container of charts but the macro keeps getting stuck on this line:

                       

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

                       

                      Could you please supply the information for this variable. The code will export only one of the charts, not all of them.

                        • Re: Export chart from container by macro
                          Mathias Vanden Auweele

                          Hey crystles,

                           

                          That lines sets the content of a variable in the qv app. If you don't have that variable, then the macro will stop running. In my case, I used that var in order to change the expressions used in certain charts (instead of absolute values I made them cumulative). You'll probably don't need it so I would advice to delete that line and delete the loop made for it also.

                  • Re: Export chart from container by macro
                    Deepti Gupta

                    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