Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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
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
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
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
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
Hi,
Try
set chart=ActiveDocument.ActiveSheet.CreateStraightTable
Regards,
Kaushik Solanki