10 Replies Latest reply: Apr 28, 2017 9:17 AM by Rubandoss H RSS

    How to sort with the help of macro!

      Hi All


      We are generating the Straight table with the help of macro. In that one of the field we wants to sort it by "Load Order: Original". For example if the "Dimension=Month" then sort it by "Load Order: Original". How can I achieve this with the help of macro.

       

      Following is the sample of code which we were using in the macro for setting up the number properties. I want to similar kind of coding to add to sort the Dimension: "Month" by "Load Order: Original".

       

       

        SET objNumberProperties = objPivotTableProperties.Expressions.Item(intAddedMeasureCount + intLoop).Item(0).Data.ExpressionVisual.NumberPresentation
        objNumberProperties.Fmt = "#,##0.000"
        objNumberProperties.nDec = 0
        objNumberProperties.Thou = ","
        objNumberProperties.Type = 11
      
      

       

      Hope some one can help me out on this soon as it is little urgent.

       

      Thanks

       

      Attitude

        • How to sort with the help of macro!
          Kaushik Solanki

          Hi,

           

             I am not in my office today, so will not be able to give you tested answer.

           

             Pls try this.,

           

           

          rem set load order sort for all fields in chart Mychart

          set chart=ActiveDocument.GetSheetObject("CH01")

          set Prop = chart.GetProperties

          set vars = Prop.Dimensions

          for i = 0 to vars.Count-1

              vars.Item(i).SortCriteria.SortByAscii = 0

              vars.Item(i).SortCriteria.SortByLoadOrder = 1

          next

          chart.SetProperties Prop

           

           

          Regards,

          Kaushik Solanki

            • How to sort with the help of macro!

              Hi Kaushik

               

              For your information, the scenario is...

               

              We are creating the straight table with the help of macro. In that what happens is user selected Dimension and expression will be passed in and straight table is created with the help of macro. So the properties such as position and number properties is controlled in the macro script.

               

              The script which I have a added above is just the part of the script which we are using to set the NumberProperties. So something in a similar way I would like to sort as mentioned in my post.

               

              Seeing your script looks like you are passing the GetSheetObject("CH01") but in my scenario we are not sure about the GetSheetObject. Also I have included in my macro but it didn't work. I am sorry! If you get some time can you please look into it. If not can you please give me some solution once you are available in office.

               

              Before you digin please let me know if you need anymore details from me.

               

              Thanks

               

              Attitude


              • How to sort with the help of macro!

                Hi Kaushik

                 

                I added your code along with my macro script and looks like it is sorting as expected. But the only concern is I am setting the set GetSheetObject manually. How we can pass the sheet object dynamically whenever the macro has been called. If we get to know this then I think it will work fine.

                 

                chart=ActiveDocument.GetSheetObject("CH01")

                 

                If you have time please look into it and give some solution please.

                 

                Thanks

                 

                Attitude

                  • Re: How to sort with the help of macro!
                    Deepak Vadithala

                    Hi,

                     

                    How many sheetobjects do you have in the sheet? If you have more than one similar type of object then how will QV decides on which object you want the sorting? Or do you want to sort all the sheet objects? Please can you explain in detail?

                     

                    Thanks - DV

                      • Re: How to sort with the help of macro!

                        Hi DV

                         

                        Thanks for your reply. Once the user selects the sheet dimension and expression in the front end they will select the button which calls the macro to create the straight table. So obiviously the number of straight table(object) is depends on how times the user clicks on the button.

                         

                        If fact as you said I want to sort all the sheet object. Those many time user clicks on the button those many sheet objects will be created. I want to sort all those sheet object.

                         

                        Hope my requirement are understandable now.

                         

                        Thanks

                         

                        Attitude

                          • How to sort with the help of macro!
                            Kaushik Solanki

                            Hi,

                             

                                If you are creating a chart with a button click and macro then you can do this way.

                             

                               

                            set chart = ActiveDocument.Sheets("SH01").CreateStraightTable

                            //Straight table dimension and expression code

                            // then
                            set Prop = chart.GetProperties

                            set vars = Prop.Dimensions

                            for i = 0 to vars.Count-1

                                vars.Item(i).SortCriteria.SortByAscii = 0

                                vars.Item(i).SortCriteria.SortByLoadOrder = 1

                            next

                            chart.SetProperties Prop

                             

                             

                            Regards,

                            Kaushik Solanki

                              • Re: How to sort with the help of macro!

                                Hi Kaushik

                                 

                                Thanks for the code! But again I think you are hardcoding the sheet object("SH01"). That is what my concern it.

                                 

                                The previous code which you have given was working fine but the only issue with that was

                                ("SH01") was hard coded.

                                 

                                Thanks

                                 

                                Attitude

                                  • Re: How to sort with the help of macro!
                                    Deepak Vadithala

                                    Hi Mate,

                                     

                                    Have you checked my post? I have used ActiveDocument instead of hard coding the Sheets or SheetObjects. Please see the below code which exports all the Objects in the ActiveDocument. And you dont need to give the sheet name nor SheetObject name. Please use the below idea and integrate with your code.

                                     

                                    Sub Export_All_Chart_Images()

                                        For i = 0 To ActiveDocument.NoOfSheets - 1

                                            Set MySheet = ActiveDocument.GetSheet(i)

                                            MyCharts=MySheet.GetSheetObjects

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

                                                        ActiveDocument.Sheets(i).Activate

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

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

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

                                                     obj.ExportBitmapToFile "C:\Users\deepak.vadithala\Desktop\All\Test\Images_" & Right(MyCharts(X).GetObjectId, 4)  & ".bmp"

                                                     End if

                                                Next

                                        Next  

                                    End Sub

                                     

                                    Good luck!

                                     

                                    Cheers - DV

                                    • How to sort with the help of macro!
                                      Kaushik Solanki

                                      Hi,

                                       

                                         Try

                                       

                                         set chart=ActiveDocument.ActiveSheet.CreateStraightTable

                                       

                                      Regards,

                                      Kaushik Solanki