Using Excel sort function through DIAdem.

[Kevin_McG]

Hello,

I am looking to make use of the sort function in Excel through DIAdem. I export data from my .TDM files into a running log in Excel, and I would like to be able to sort a selection, in ascending order, based off of the data in the first column.

Has anyone been able to accomplish something close to this?

I appreciate any help you can offer.

[AndreasH_Ha]

Hello Kevin,

are you aware that you can sort in DIAdem ?

If you want to do this in Excel, you want to go through ActiveX, which is totally possible, but probably more effort compared to use DIAdem directly.

Andreas

[Kevin_McG]

I know I can do it in DIAdem, but I need to export to a specific template file that has several merged columns and plots in it. I'd rather not have to load that file in to DIAdem each time I process a file. Doing all of this through ActiveX has worked perfectly up until this point.

[AndreasH_Ha]

Hmm, so what stopped working if you say ActiveX worked fine so far ? To my knowledge nothing in the Excel ActiveX interface changed recently and ActiveX in DIAdem didn't change neither.

Andreas

[Kevin_McG]

Nothing stopped working, I just want to add the sort function to my script and I can't seem to figure out how to make it work (specify the range, the method of sorting and so on). Everything else I wanted to do with ActiveX I've been able to figure out, I just can't seem to figure out the exact code needed to do this.

Here a rough example of what I've been trying:

Dim Excel : Set Excel = CreateObject("Excel.Application") : Excel.Visible = TRUE

Excel.Workbooks.Open("C:\Users\Kevin_McG\Desktop\Sort Test.xlsx")
Excel.Range("A1:A10").Select
Excel.Selection.Sort("Key1:=Range(A1:A9},Order1:=xlAscending")

It's the Excel.Selection.Sort(...) that I can't work out.

[AndreasH_Ha]

Hello Kevin,

now I understand. But I have to admit that I am not an expert on this. Perhaps someone else in the forum can help. 

Andreas

[Sil.VI · Accepted solution]

Hi Kevin,

I was searching a bit on the web about this issue that you are having. Honestly, I am not an expert on ActiveX scripting, however I did found something that at least might be useful regarding the Sort method while scripting Excel Sheets. If you take a look at the second point on this link, they are talking about how some methods and properties are disabled when an AcitveX control is active and particularly they talk about the Sort method and they propose as a workaround to activate another element on the sheet before you call the sort method.

I hope you can find that useful for your development as well!

Warm Regards,

[Kevin_McG]

Thanks, that did end up leading me in the right direction.

The solution is to make a macro in the excel file itself and then call the macro using ActiveX controls.

[Sil.VI]

I am glad that you were able to find it helpful! 

Have a great day!