Excel Ole Sort

I exported a module to excel and would like to automate excel to sort the data. I'm having trouble converting the VBA script to DXL specifically  the Key, SortOn, Order and DataOption settings. This is the VBA (or similar) i would like to convert: 

    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("A2:A18318"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange Range("A1:C18318")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

This is the first part of my DXL: 

OleAutoObj objExcel = null
OleAutoObj objWorkbooks = null
OleAutoObj objWorkbook = null
OleAutoObj objSheet = null
OleAutoObj objCell = null
OleAutoObj objSort = null
OleAutoObj objSortFields = null
OleAutoArgs args   = create
string Mes

objExcel = oleGetAutoObject("excel.application")
olePut(objExcel,"visible",true)

oleGet(objExcel,"ActiveWorkbook",objWorkbook)

oleGet(objWorkbook,"ActiveSheet",objSheet)
Mes = oleGet(objSheet, "Sort", objSort)
print "Sort: " Mes "\n"

Mes = oleGet(objSort, "SortFields",objSortFields)
print "SortFields: " Mes "\n"

Mes = oleMethod(objSortFields,"Clear")
print "Clear: " Mes "\n"

//OK up to here
//having trouble with Sortfields...
clear args
put(args,"Key","Range(\"A2:A18318\")")
put(args,"order" ,"xlAscending")
Mes = oleMethod(objSortFields, "add", args)
print "Add: " Mes "\n"

The out put of the code above is:

Sort:
SortFields:
Clear:
Add: OLE failure: Type mismatch.

I've tried every combination I can think of to get this to work.The goal is to automate an excel sort with doors.

Thank you,

David


tlhIngan - Wed Jul 16 11:43:15 EDT 2014

Re: Excel Ole Sort
Wolfgang Uhr - Thu Jul 17 02:39:17 EDT 2014

Hi

As far as I know, you cannot use named arguments in the doors ole functions.

Best regards

Wolfgang

Re: Excel Ole Sort
tlhIngan - Thu Jul 17 09:32:43 EDT 2014

Is there another way to pass these arguments or get excel to do a sort?

Thanks

Re: Excel Ole Sort
Wolfgang Uhr - Thu Jul 17 15:16:28 EDT 2014

tlhIngan - Thu Jul 17 09:32:43 EDT 2014

Is there another way to pass these arguments or get excel to do a sort?

Thanks

Hi

No, the only chance is to write a complete VBA-Script and perform it at once.

https://www.ibm.com/developerworks/community/forums/html/topic?id=e1e9067c-7bb7-4bf8-a57a-4ad7a3a41fb1&ps=25

Sorry

Wolfgang

 

Re: Excel Ole Sort
Mathias Mamsch - Thu Jul 17 18:35:36 EDT 2014

Sorry to contradict, but the following code works for me. You just need to take care that you can neither pass constant names (e.g. xlAscending) as parameters, neither can you pass inline function calls ("Range(A5:B5)") to the functions. You need to get the value for the constant names and call the functions separately. Then it works, regards, Mathias 

OleAutoObj objExcel = null
OleAutoObj objWorkbooks = null
OleAutoObj objWorkbook = null
OleAutoObj objSheet = null
OleAutoObj objCell = null
OleAutoObj objSort = null
OleAutoObj objSortFields = null
OleAutoArgs args   = create
string Mes

objExcel = oleGetAutoObject("excel.application")
olePut(objExcel,"visible",true)

oleGet(objExcel,"ActiveWorkbook",objWorkbook)

oleGet(objWorkbook,"ActiveSheet",objSheet)
Mes = oleGet(objSheet, "Sort", objSort)
print "Sort: " Mes "\n"

Mes = oleGet(objSort, "SortFields",objSortFields)
print "SortFields: " Mes "\n"

Mes = oleMethod(objSortFields,"Clear")
print "Clear: " Mes "\n"

//OK up to here
//having trouble with Sortfields...

OleAutoObj objSortKeyRange = null
OleAutoObj objSortRange = null
clear args
put (args, "A2:A5")
Mes = oleGet(objSheet, "Range", args,objSortKeyRange  )
print "RANGE: "Mes "\n"

clear args
put (args, "A1:B5")
Mes = oleGet(objSheet, "Range", args, objSortRange )
print "RANGE: "Mes "\n"

clear args
put(args,"Key",objSortKeyRange  )
put(args,"order" ,1)  // xlAscending
Mes = oleMethod(objSortFields, "add", args)
print "Add: " Mes "\n"

clear args
put(args,objSortRange )
Mes = oleMethod(objSort, "SetRange", args)
print "SetRange: " Mes "\n"


Mes = olePut(objSort, "Header", 1) // xlYes
print "Header: " Mes "\n"

Mes = olePut(objSort, "MatchCase", 1) // xlYes
print "MatchCase: " Mes "\n"

Mes = olePut(objSort, "Orientation", 1) // xlTopToBottom
print "Orientation: " Mes "\n"

Mes = olePut(objSort, "SortMethod", 1) // xlPinYin
print "SortMethod: " Mes "\n"

Mes = oleMethod(objSort , "Apply")

print "Apply: " Mes "\n"

 

Re: Excel Ole Sort
Wolfgang Uhr - Fri Jul 18 03:00:58 EDT 2014

Mathias Mamsch - Thu Jul 17 18:35:36 EDT 2014

Sorry to contradict, but the following code works for me. You just need to take care that you can neither pass constant names (e.g. xlAscending) as parameters, neither can you pass inline function calls ("Range(A5:B5)") to the functions. You need to get the value for the constant names and call the functions separately. Then it works, regards, Mathias 

OleAutoObj objExcel = null
OleAutoObj objWorkbooks = null
OleAutoObj objWorkbook = null
OleAutoObj objSheet = null
OleAutoObj objCell = null
OleAutoObj objSort = null
OleAutoObj objSortFields = null
OleAutoArgs args   = create
string Mes

objExcel = oleGetAutoObject("excel.application")
olePut(objExcel,"visible",true)

oleGet(objExcel,"ActiveWorkbook",objWorkbook)

oleGet(objWorkbook,"ActiveSheet",objSheet)
Mes = oleGet(objSheet, "Sort", objSort)
print "Sort: " Mes "\n"

Mes = oleGet(objSort, "SortFields",objSortFields)
print "SortFields: " Mes "\n"

Mes = oleMethod(objSortFields,"Clear")
print "Clear: " Mes "\n"

//OK up to here
//having trouble with Sortfields...

OleAutoObj objSortKeyRange = null
OleAutoObj objSortRange = null
clear args
put (args, "A2:A5")
Mes = oleGet(objSheet, "Range", args,objSortKeyRange  )
print "RANGE: "Mes "\n"

clear args
put (args, "A1:B5")
Mes = oleGet(objSheet, "Range", args, objSortRange )
print "RANGE: "Mes "\n"

clear args
put(args,"Key",objSortKeyRange  )
put(args,"order" ,1)  // xlAscending
Mes = oleMethod(objSortFields, "add", args)
print "Add: " Mes "\n"

clear args
put(args,objSortRange )
Mes = oleMethod(objSort, "SetRange", args)
print "SetRange: " Mes "\n"


Mes = olePut(objSort, "Header", 1) // xlYes
print "Header: " Mes "\n"

Mes = olePut(objSort, "MatchCase", 1) // xlYes
print "MatchCase: " Mes "\n"

Mes = olePut(objSort, "Orientation", 1) // xlTopToBottom
print "Orientation: " Mes "\n"

Mes = olePut(objSort, "SortMethod", 1) // xlPinYin
print "SortMethod: " Mes "\n"

Mes = oleMethod(objSort , "Apply")

print "Apply: " Mes "\n"

 

Hi Mathias

Nomally the code in my scripts looks like this

Mes = olePut(objSort /*, "Header" */, 1) // x

And if we get a new Doors version, I remove the "/*" and "/*" in some code and try it and as far as I know it does not work. But I'll test it again asap

Best regards

Wolfgang

 

Re: Excel Ole Sort
tlhIngan - Fri Jul 18 09:50:55 EDT 2014

Mathias Mamsch - Thu Jul 17 18:35:36 EDT 2014

Sorry to contradict, but the following code works for me. You just need to take care that you can neither pass constant names (e.g. xlAscending) as parameters, neither can you pass inline function calls ("Range(A5:B5)") to the functions. You need to get the value for the constant names and call the functions separately. Then it works, regards, Mathias 

OleAutoObj objExcel = null
OleAutoObj objWorkbooks = null
OleAutoObj objWorkbook = null
OleAutoObj objSheet = null
OleAutoObj objCell = null
OleAutoObj objSort = null
OleAutoObj objSortFields = null
OleAutoArgs args   = create
string Mes

objExcel = oleGetAutoObject("excel.application")
olePut(objExcel,"visible",true)

oleGet(objExcel,"ActiveWorkbook",objWorkbook)

oleGet(objWorkbook,"ActiveSheet",objSheet)
Mes = oleGet(objSheet, "Sort", objSort)
print "Sort: " Mes "\n"

Mes = oleGet(objSort, "SortFields",objSortFields)
print "SortFields: " Mes "\n"

Mes = oleMethod(objSortFields,"Clear")
print "Clear: " Mes "\n"

//OK up to here
//having trouble with Sortfields...

OleAutoObj objSortKeyRange = null
OleAutoObj objSortRange = null
clear args
put (args, "A2:A5")
Mes = oleGet(objSheet, "Range", args,objSortKeyRange  )
print "RANGE: "Mes "\n"

clear args
put (args, "A1:B5")
Mes = oleGet(objSheet, "Range", args, objSortRange )
print "RANGE: "Mes "\n"

clear args
put(args,"Key",objSortKeyRange  )
put(args,"order" ,1)  // xlAscending
Mes = oleMethod(objSortFields, "add", args)
print "Add: " Mes "\n"

clear args
put(args,objSortRange )
Mes = oleMethod(objSort, "SetRange", args)
print "SetRange: " Mes "\n"


Mes = olePut(objSort, "Header", 1) // xlYes
print "Header: " Mes "\n"

Mes = olePut(objSort, "MatchCase", 1) // xlYes
print "MatchCase: " Mes "\n"

Mes = olePut(objSort, "Orientation", 1) // xlTopToBottom
print "Orientation: " Mes "\n"

Mes = olePut(objSort, "SortMethod", 1) // xlPinYin
print "SortMethod: " Mes "\n"

Mes = oleMethod(objSort , "Apply")

print "Apply: " Mes "\n"

 

Mathias,

   Your code works great and I thank you for taking the time to write it. I can see now where the trouble is. First, ranges must be passed in as an OleAutoArgs type. Second, the vba SortField is a collection. Using the method "add" adds the SortField parameters to the collection. I also noticed that multiple parameters can be added with one method. For example, you added the key parameter (using a type oleAutoArgs for the range) and the order parameter. 

clear args
put(args,"Key",objSortKeyRange  )
put(args,"order" ,1)  // xlAscending
Mes = oleMethod(objSortFields, "add", args)
print "Add: " Mes "\n"

Below is a clip of the code I will use for both range parameters (reuses the range object). 

//set add key
clear args
put(args,"B2:B5")
Mes = oleGet(objSheet,"Range",args,objRange)
if(Mes != null) print "Range: " Mes "\n"
clear args
put(args,"Key",objRange)
put(args,"order",1)
Mes = oleMethod(objSortFields,"Add",args)
if(Mes != null) print "Add: " Mes "\n"

//SetRange
clear args
put(args,"A1:B5")
Mes = oleGet(objSheet,"Range",args,objRange)
if(Mes != null) print "Range: " Mes "\n"
clear args
put(args, objRange)
Mes = oleMethod(objSort, "SetRange", args)
if(Mes != null) print "SetRanage: " Mes "\n"

Thank you again for your solution!

Re: Excel Ole Sort
Wolfgang Uhr - Wed Sep 10 08:38:14 EDT 2014

Mathias Mamsch - Thu Jul 17 18:35:36 EDT 2014

Sorry to contradict, but the following code works for me. You just need to take care that you can neither pass constant names (e.g. xlAscending) as parameters, neither can you pass inline function calls ("Range(A5:B5)") to the functions. You need to get the value for the constant names and call the functions separately. Then it works, regards, Mathias 

OleAutoObj objExcel = null
OleAutoObj objWorkbooks = null
OleAutoObj objWorkbook = null
OleAutoObj objSheet = null
OleAutoObj objCell = null
OleAutoObj objSort = null
OleAutoObj objSortFields = null
OleAutoArgs args   = create
string Mes

objExcel = oleGetAutoObject("excel.application")
olePut(objExcel,"visible",true)

oleGet(objExcel,"ActiveWorkbook",objWorkbook)

oleGet(objWorkbook,"ActiveSheet",objSheet)
Mes = oleGet(objSheet, "Sort", objSort)
print "Sort: " Mes "\n"

Mes = oleGet(objSort, "SortFields",objSortFields)
print "SortFields: " Mes "\n"

Mes = oleMethod(objSortFields,"Clear")
print "Clear: " Mes "\n"

//OK up to here
//having trouble with Sortfields...

OleAutoObj objSortKeyRange = null
OleAutoObj objSortRange = null
clear args
put (args, "A2:A5")
Mes = oleGet(objSheet, "Range", args,objSortKeyRange  )
print "RANGE: "Mes "\n"

clear args
put (args, "A1:B5")
Mes = oleGet(objSheet, "Range", args, objSortRange )
print "RANGE: "Mes "\n"

clear args
put(args,"Key",objSortKeyRange  )
put(args,"order" ,1)  // xlAscending
Mes = oleMethod(objSortFields, "add", args)
print "Add: " Mes "\n"

clear args
put(args,objSortRange )
Mes = oleMethod(objSort, "SetRange", args)
print "SetRange: " Mes "\n"


Mes = olePut(objSort, "Header", 1) // xlYes
print "Header: " Mes "\n"

Mes = olePut(objSort, "MatchCase", 1) // xlYes
print "MatchCase: " Mes "\n"

Mes = olePut(objSort, "Orientation", 1) // xlTopToBottom
print "Orientation: " Mes "\n"

Mes = olePut(objSort, "SortMethod", 1) // xlPinYin
print "SortMethod: " Mes "\n"

Mes = oleMethod(objSort , "Apply")

print "Apply: " Mes "\n"

 

Ok, Mathias

If I start this routine 

XMLDocument transformeNodeToObjectXmlDocument(XMLDocument xmlDocumentSource, XMLDocument xmlDocumentStyleSheet) {
    
 OleAutoObj olexmlDocInput = getOleHandle_(xmlDocumentSource);
 OleAutoObj olexmlDocStyleSheet = getOleHandle_(xmlDocumentStyleSheet);
 
 XMLDocument xmlDocumentTarget = create_XMLDocument();
 OleAutoObj olexmlDocOutput = getOleHandle_(xmlDocumentTarget);
 
 OleAutoArgs oleArguments = create();

 olePut(olexmlDocStyleSheet, "async", false);
 olePut(olexmlDocStyleSheet, "validateOnParse", false);
 olePut(olexmlDocStyleSheet, "resolveExternals", false);
 
 clear(oleArguments);
 put(oleArguments, "stylesheet", olexmlDocStyleSheet);
 put(oleArguments, "outputObject", olexmlDocOutput);
 
 checkOLE(oleMethod(olexmlDocInput, "transformNodeToObject", oleArguments)); <<<---

 return(xmlDocumentTarget);
}

I get an error message "<<<---" in the marked line.

If I start this snippet 

XMLDocument transformeNodeToObjectXmlDocument(XMLDocument xmlDocumentSource, XMLDocument xmlDocumentStyleSheet) {
    
 OleAutoObj olexmlDocInput = getOleHandle_(xmlDocumentSource);
 OleAutoObj olexmlDocStyleSheet = getOleHandle_(xmlDocumentStyleSheet);
 
 XMLDocument xmlDocumentTarget = create_XMLDocument();
 OleAutoObj olexmlDocOutput = getOleHandle_(xmlDocumentTarget);
 
 OleAutoArgs oleArguments = create();

 olePut(olexmlDocStyleSheet, "async", false);
 olePut(olexmlDocStyleSheet, "validateOnParse", false);
 olePut(olexmlDocStyleSheet, "resolveExternals", false);
 
 clear(oleArguments);
 put(oleArguments, /* "stylesheet", */ olexmlDocStyleSheet);
 put(oleArguments, /* "outputObject", */ olexmlDocOutput);
 
 checkOLE(oleMethod(olexmlDocInput, "transformNodeToObject", oleArguments));

 return(xmlDocumentTarget);
}


The script runs well

Best regards
Wolfgang