How to sort with the help of macro!

[#1]

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

[kaushiknsolanki]

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

[#3]

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

[#4]

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

[IAMDV]

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

[#6]

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

[kaushiknsolanki]

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

[#8]

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

[IAMDV]

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

[kaushiknsolanki]

Hi,

   Try

   set chart=ActiveDocument.ActiveSheet.CreateStraightTable

Regards,

Kaushik Solanki