sorting excel worksheet using activex

[jbreefer]

Hello Everyone,

I need to find a way to open and excel document, add a line of data, then sort the data by the first column and resave the document.  I have seen and used other activex commands for excel, but I have not found a way to perform what I would like to do.  I am also using labview 6.1.  I found an example in 7.0 but i could not use it.

Thanks for any help.  It has been a nightmare trying to figure out the sort command.

Thanks Jeff

[smercurio_fc]

Usually the best way to figure out how to use the Excel object model and functions is to have Excel write the macro for you, and then look at the macro code. For example, if you have Excel write a macro where you select the first column and sort it you'll get this:

    Columns("A:A").Select
    Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

Basically the important line is the second one, which tells you how to wire up the "Sort" method for a range object:

[jbreefer]

---

Thanks smercurio_fc,

I had created the macro before, but did not know how to handle xlAscending ....ETC.....

I put them in a ring box according to your picture.  Attached is the vi.  It does not sort yet, but I can assume that you would be able to see what i have done incorrectly.  The section you are looking for is at he bottom of the main screen and the bottom left of the diagram.

Thanks Jeff

[smercurio_fc]

You can't just create arbitrary constants for those Excel constants. You have to right-click on the parameter and select Create->Constant. This will create the appropriate datatype.

As for your code... Um... errr... um.... gee... I'm not even sure where to begin. If you were working for me I'd probably consider firing you. I'm not kidding. Your code is a mess:

• Your code requires a 200-inch monitor to just look at it. This is extremely poor programming.
• You have hard-coded "magic" values (like paths and IP address) everywhere. 
• A joyous sprinkling of local variables.
• Multiple event structures - your code should only have one event structure. Having four is a recipe for code not working. 
• Unnecessary sequence frames everywhere that hide code.
• Performing long operations inside of events. This is always a bad idea. You want to handle the event quickly, and then go back to listening. If the handling requires a long time to do, then you should spin that off. A producer-consumer architecture will do that.
• I lost track of how many workbooks you're opening/closing, and I couldn't tell if the references were being closed properly - all due to incomprehensible code.
• Opening/closing Excel three times. You should open/close Excel once. When the app is open you can open/close workbooks without requiring the full-blown opening/closing of Excel.

Sorry to be so blunt, but sometimes people need to hear the hard cold truth.

I would strongly recommend spending some time reading over some of the design framework patterns documents that are available in the NI Knowledgebase. E.g., state machines and producer-consumer. 

Also, this:

 

is a no-op.

[jbreefer]

Thanks for being blunt.  I have used labview enough to understand how to get what i want, but i am not good at grammatics and redundant functions.  I am glad it is not my job, as it is just one of the hats i wear and it is a project for the shop floor.  It does work the way you see it.  I will review the documents that you sent me and i will clean the code up, but the activex for sort does not work.  It will open the excel document and highlight everything, not sort, then save and close.

I just dont understand activex well enough make it work on my own.

Thanks for your help

Jeff

[Joe_H]

For more information and some sample VI's and tool kits, you can go to the excel board

[smercurio_fc · Accepted solution]

---

jbreefer wrote:

but the activex for sort does not work.  It will open the excel document and highlight everything, not sort, then save and close.

---

I am also seeing this. Quite peculiar. One problem is that when the "Key1" parameter is specified by a string then the assumption is that there's a defined range in the workbook called "A1" (the example in this case). Even if you explicitly wire a range to the "Key1" parameter, as the documentation for Sort indicates, it still doesn't work. Yet it works perfectly well in the macro. I'm not sure what's going on and will need to look at this further.

A short-term fix is to have a macro in the spreadsheet to perform the sort and then simply call the macro from LabVIEW.

[Joe_H]

A short-term fix is to have a macro in the spreadsheet to perform the sort and then simply call the macro from LabVIEW.

---

This is by far the fastes, easiest and most convenient way.

[jbreefer]

I'll clean the code up and use a macro.  thanks for all your help

Jeff