Criteria based Sorting in an Excel File

[litlmike]

Challenge

Read the Data from an Excel file and then sort that Data. So that, I can produce a report of data back to the user.

I have included an example Excel File of how the data is stored. Also, I have provided the GUI for the user to select the criteria to sort by.

What I need Help With:

At this point, just how to read the Excel File and sort the data. I assume that I can figure out the rest, once I know how to do that. So then, if the user picks CA (for California) and San Francisco, the script will return all contacts that are in CA and live in San Francisco. But, not someone that lives in Berkeley, CA.

I prefer to use COM with Excel, if possible.

Thanks In Advance.

Example File:

Excel_Example.zip

Front End just for reference:

expandcollapse popup

#include <GUIConstants.au3>
#Region ### START Koda GUI section ### Form=C:\Documents and Settings\blah\My Documents\Personal\AutoIt\Koda Forms\CriteriaSelector_Excel2.kxf
$Form1 = GUICreate("Select Criteria", 499, 330, 304, 144)
$Group1 = GUICtrlCreateGroup("", 115, 229, 265, 73)
$Button1 = GUICtrlCreateButton("&OK", 123, 254, 75, 25, 0)
$Button2 = GUICtrlCreateButton("&Cancel", 203, 254, 75, 25, 0)
$Button3 = GUICtrlCreateButton("&Help", 283, 254, 75, 25, 0)
GUICtrlCreateGroup("", -99, -99, 1, 1)
$Group2 = GUICtrlCreateGroup("", 24, 8, 441, 209)
$Combo1 = GUICtrlCreateCombo("Featured SF?", 328, 128, 120, 25)
GUICtrlSetData(-1, "Yes|No")
GUICtrlSetFont(-1, 12, 400, 0, "Times New Roman")
$Input1 = GUICtrlCreateInput("Site Name", 40, 80, 120, 27)
GUICtrlSetFont(-1, 12, 400, 0, "Times New Roman")
$Input2 = GUICtrlCreateInput("Site ID", 184, 80, 120, 27)
GUICtrlSetFont(-1, 12, 400, 0, "Times New Roman")
$Input3 = GUICtrlCreateInput("Category", 328, 80, 120, 27)
GUICtrlSetFont(-1, 12, 400, 0, "Times New Roman")
$Input4 = GUICtrlCreateInput("City", 40, 128, 120, 27)
GUICtrlSetFont(-1, 12, 400, 0, "Times New Roman")
$Input5 = GUICtrlCreateInput("State", 184, 128, 120, 27)
GUICtrlSetFont(-1, 12, 400, 0, "Times New Roman")
$Combo2 = GUICtrlCreateCombo("Active SF?", 40, 176, 120, 25)
GUICtrlSetData(-1, "Yes|No")
GUICtrlSetFont(-1, 12, 400, 0, "Times New Roman")
$Label1 = GUICtrlCreateLabel("Select Your Criteria For Search", 123, 30, 267, 27)
GUICtrlSetFont(-1, 16, 400, 0, "Times New Roman")
GUICtrlCreateGroup("", -99, -99, 1, 1)
GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###

While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit
        
        Case $Button1
            GUICtrlRead ($Input1)
            GUICtrlRead ($Input2)
            GUICtrlRead ($Input3)
            GUICtrlRead ($Input4)
            GUICtrlRead ($Input5)
            GUICtrlRead ($Combo1)
            GUICtrlRead ($Combo2)
        
        Case $Button2
            ExitLoop
        
        Case $Button3
            ExitLoop
        
    EndSwitch
WEnd

[JSThePatriot]

Out of curiosity it seems you are interested in using Excel for more of a database solution than the spreadsheet that it is. Excel can be used for data storage, but it is a spreadsheet application.

If you are interested in possibly using a database then there are a few things in the scripts and scraps forum that may be of interest to you.

JS

[Locodarwin]

Lots of people store this sort of information in an Excel spreadsheet for any number of reasons, not the least of which is to associate lists with financial calculations.

So with your needs in mind, here are a couple of things:

1. Consider sorting the data in Excel prior to reading it into your AutoIt applet, utilizing Excel's flexible multicolumn sort routine.

Here's a code example, pulled from the ExcelCOM UDF I've been working on:

$oExcel.Range("A1:D9").Sort ($oExcel.Range("A"), 2) ; Sorts A1 through D9 with A as the key column, in descending order
oÝ÷ Ù.q©Üç$%Ƨv'Zµçè­©Þ½éÛyçâz»(®ÚèºØ§{d^iا§X¬¦·­·'³&®¶­s`¢b33cµf&&ÆRÒb33c¶ôW6VÂä7FfW6VWBä6VÆÇ2ÂåfÇVR²â#3f÷&Ö@¢b33cµf&&ÆRÒb33c¶ôW6VÂä7FfW6VWBå&ævRgV÷C´gV÷C²åfÇVR²âf÷&Ö@

With R1C1 format, you can use a loop to gather ranges of cells into, say, some arrays.

There are other examples of reading cells in my UDF candidate - you're welcome to examine and use any of that code for whatever purpose.

-S

Edited November 3, 2006 by Locodarwin

[litlmike]

1. Consider sorting the data in Excel prior to reading it into your AutoIt applet, utilizing Excel's flexible multicolumn sort routine.

in R1C1 format,

$oExcel.Range($oExcel.Cells(1, 1), $oExcel.Cells(10, 10)).Sort ($oExcel.Range("A"), 2)
oÝ÷ Ù/êº^N§Çè®Ø^}çm§$¶¬ËÞ¯(^î*jÇ¢|!jܨ¹ªÞ±¬¢ØbH§Øb±ÊzÚ,¢»hºÛayø«²×+׫¦®¶­sbb33c´6GÒgV÷C´BgV÷C°¢b33cµ7FFRÒgV÷C´RgV÷C°¢b33c¶ôW6VÂå&ævRb33c¶ôW6VÂä6VÆÇ2ÂÂb33c¶ôW6VÂä6VÆÇ2Âå6÷'Bb33c¶ôW6VÂå&ævRb33c´6GÂ"

But, 2 questions:

1) How do I make $oExcel.Cells(10, 10)) change from 10,10 to the entire excel file? Or, all of colums 'A' through 'J'?

2) How do I do a second sort on my first sort? So after sorting Cities ('D'), then how I do I next refine that search to Cities in these States ('E')?

Thanks again.

[Locodarwin]

Thanks for the feedback, this is very helpful.

So based on what you are saying, I am thinking this code to sort out the first criteria.

$City = "D"
$State = "E"
$oExcel.Range($oExcel.Cells(1, 1), $oExcel.Cells(10, 10)).Sort ($oExcel.Range($City), 2)oÝ÷ Ø­Ú«²Ø¨ÍG£h"f¤{Mú LzP[5Ó]©àyúè]5ÒÚ-秶*Þ{ø¥xêÚZrnÍýmº.ý'v hjÇ¢wl¢»hl»-²íJµê좻b¢¶'¬ßÐ÷öØ^0!Úìm­çâëajÛj·!¶¢¶'¬{azǵ«^³DßÔájy,j¢ú®¢×µRǴߪÅÇ¥ ée±*+¶Ú,¢»m秶*ÞÂä²´Z½é÷öÛ^²×Ö(ºh ÙÞyÛhÓ~¨§ä¨®Ó§zí·
,¥uÚ"¶èm¦åɺ-e¡jܨ»ky«$x¢¸¬q©eyÖ¦º[b~'¥vÊ+¶)ÄÅÇ¥)à í+jDz¢ë-Yn±ê'y*+µÆ¥åÊ+b²j+z)ï¢[Þt¬Æ¥,¨ºÇ¥yËmꮢÙÊ«¨¶º0²Ü¢[¦Ë©¶a­ç¢Ø^¢·^¯*.Á©í¶¦²íyÑÞ­íý²Ø^~æjÖrÝx- Ä5ú%u¼­ ^Å©©ë¬x-çZµ«r¥ëÅÇ¥Â)e{^r×(uí7ê1qéBzYlJíãBË^|

You can get more information on this method from this link:

MSDN Excel.Sort()

-S

[litlmike]

-S

Thanks again for all the really great info. To be quite frank, my brain almost exploded when I read your post and looked at the MSDN page. I am pretty new to COM and well, not that great of a programmer either! Ha!

I've been studying and testing for the last hour with out much progress, so let me reduce this problem to it's simplest form. Let us start with making an Excel file and sort one column of data. I tried your code, but just got an error, then I did more trial and error, with no luck. Feedback please.

Thanks.

;~  Create an Excel File to Print to
$oExcel = ObjCreate("Excel.Application")                   ; Create an Excel Object
$oExcel.Visible = 1                                        ; Let Excel show itself
$oExcel.WorkBooks.Add                                      ; Add a new workbook

$Row = 1
$Column = 1
$x = 1
While $x < 20
$oExcel.ActiveWorkBook.ActiveSheet.Cells($Row, $Column).Value="Test" & $x ; Fill a cell with the SF Name
$x+=1
$Row +=1
WEnd


$oExcel.Range($oExcel.Cells(1,1), $oExcel.Cells(1,10)).Sort ($oExcel.Range("A1"),2)

[Locodarwin]

Actually, I just ran the code you posted, and received no errors at all. It looks to me like you're dangerously close to understanding this stuff.

One little hitch, though. It won't sort as written, because in this line:

$oExcel.Range($oExcel.Cells(1,1), $oExcel.Cells(1,10)).Sort ($oExcel.Range("A1"),2)
oÝ÷ Û*.ßÚÞµéex"¶Ú,¢»Z®'¢Ö¢[¦Z+¶zYlreyËb¢{h­ën®{az¶§ì¨»kzZ()à~ò¢íýYÞyÛh²ü­êèÁø«²Ûaz{ayÊ%ºiåG­+zYl®¢[¦w«{l¶¢ºÞrب«­¢+Ø(ÀÌØí½á°¹I¹ ÀÌØí½á°¹
±±Ì İĤ°ÀÌØí½á°¹
±±Ì ÄÀ°Ä¤¤¹M½ÉÐ ÀÌØí½á°¹I¹ ÅÕ½ÐíÄÅÕ½Ð줰Ȥ(

Be advised, however, that in the example you provided, the column won't sort the way you're probably expecting it to. When you sort strings, you need to provide leading zeros to numbers that have fewer digits than the highest number in the sort range.

Test01

Test02

.

.

.

Test99

-S

Edited November 4, 2006 by Locodarwin

[litlmike]

Actually, I just ran the code you posted, and received no errors at all. It looks to me like you're dangerously close to understanding this stuff.

Thanks for the feedback. I see what you mean by getting the row and column mixed up. I have made the changes.

I have been doing some brainstorming, however, and I am starting to wonder if this is the wrong approach to this problem. I can't forsee how we will sorting the rows/columns will give us the end result desired. That being a filtered report of the data, based upon criteria.

Can you shine some light at the end of this tunnel?

Thanks.

Edited November 6, 2006 by litlmike

[randallc]

Hi,

As JSThePatriot has said in post2, you may need a databse approach.

name your columns in the first row; then this works;

;exceladodb3.au3
$s_XLfile=FileGetShortName(@ScriptDir&"\Excel Example.xls")
$objConn = ObjCreate("ADODB.Connection")
$objConn.Open ("Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq="&$s_XLfile&";") 
$strSQL = "SELECT * FROM [Sheet1$] WHERE State = 'CA' and Town = 'San Francisco'; "
$oRS = ObjCreate("ADODB.Recordset")
$oRS.Open($strSQL, $objConn, 1, 3)
MsgBox(0, "test", $oRS(0).value)
MsgBox(0, "test", $oRS(1).value)
MsgBox(0, "test", $oRS(2).value)
$oRS.close

randall

Excel_Example.zip

Edited November 7, 2006 by randallc

[1905russell]

Is it possible to run the xls at the same time as exceladodb3.au3?

PS. This is a powerful little script.

Edited November 7, 2006 by 1905russell

[Locodarwin]

You can use the ADO database hook approach just mentioned, which is preferable.

Another approach is to use the .AutoFilter() method in ExcelCOM to display only the data you want to filter by in the sheet, then copy & paste. It's less preferable because it ends up being less clean and precise, but it's an option.

The .AutoFilter syntax for VB/VBA is:

Function AutoFilter( _
    <InAttribute()> Optional ByVal Field As Object, _
    <InAttribute()> Optional ByVal Criteria1 As Object, _
    <InAttribute()> Optional ByVal Operator As XlAutoFilterOperator, _
    <InAttribute()> Optional ByVal Criteria2 As Object, _
    <InAttribute()> Optional ByVal VisibleDropDown As Object _
) As Object

And here's how you'd create and apply a filter to your sheet. First, you need to do as randallc mentioned and insert a header at the top of each row. Then you could do something like:

$oExcel.ActiveSheet.AutoFilter     ; start the filter
$oExcel.ActiveSheet.AutoFilter (5, "CA")  ; filter row 5 by "CA" (California)

...and then copy the results (a list of only Californians) into whatever format you need it.

There are other advanced filtering methods and properties that you can use to more specifically target the areas you want to filter, but for your relatively simple worksheet, AutoFilter will work fine.

Here's the MSDN documentation on it:

http://msdn2.microsoft.com/en-us/library/m...ter(VS.80).aspx

Again, this is just an option. You'd be better off in the long run learning how to use ADO COM if you're going to be pulling simple data like this.

-S

[litlmike]

You can use the ADO database hook approach just mentioned, which is preferable.

-S

@ randallc & @ locodarwin

Thanks very much for your feedback. I think that I will go the route that the both of you reccomend the most, this ADO database hook approach.

Do you think that the both of you could elaborate on this approach a bit more? Frankly, this is my first dealings with a hook, and I have no idea what ADO is.

The details I think I would need to know:

1) What is ADO?

2) How would I modify this script, by randallc, to return the results of more than one contact?

3) What is a hook, exactly?

4) Why does the array in MsgBox use () instead of []?

5) Does the following code say this?

"Open the Object that was created, called ADODB.Connection, with the Microsoft Excel Driver, with the ID 790 and the database to use it on is $s_XLfile"

$objConn.Open ("Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq="&$s_XLfile&";")

Thanks again for your help.

[JSThePatriot]

@ randallc & @ locodarwin

Thanks very much for your feedback. I think that I will go the route that the both of you reccomend the most, this ADO database hook approach.

Do you think that the both of you could elaborate on this approach a bit more? Frankly, this is my first dealings with a hook, and I have no idea what ADO is.

The details I think I would need to know:

1) What is ADO?

2) How would I modify this script, by randallc, to return the results of more than one contact?

3) What is a hook, exactly?

4) Why does the array in MsgBox use () instead of []?

5) Does the following code say this?

"Open the Object that was created, called ADODB.Connection, with the Microsoft Excel Driver, with the ID 790 and the database to use it on is $s_XLfile"

$objConn.Open ("Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq="&$s_XLfile&";")

Thanks again for your help.

1) ADO is one method in which you can connect to a database without using its pre-configured DMS (Database management system). ADO (Wikipedia)

2) I dont generally use ADO to "HOOK" into a database so he will have to answer that question for you.

3) A hook is how you are able to get into a database. There are several methods. ADO happens to be the one randallc has shown you.

4) It uses ()'s because it is a COM object, and not a native Array in AutoIt.

5) I will leave this one to be answered by randallc, but that sounds about right.

I hope that helps.

JS

[litlmike]

1) ADO is one method in which you can connect to a database without using its pre-configured DMS (Database management system). ADO (Wikipedia)

2) I dont generally use ADO to "HOOK" into a database so he will have to answer that question for you.

3) A hook is how you are able to get into a database. There are several methods. ADO happens to be the one randallc has shown you.

4) It uses ()'s because it is a COM object, and not a native Array in AutoIt.

5) I will leave this one to be answered by randallc, but that sounds about right.

I hope that helps.

JS

This has been very helpful, especially your Wiki link. The more I look at this code, the more it makes sense, but I am still perplexed by this line, especially what the 1, and 3 refer to.

$oRS.Open($strSQL, $objConn, 1, 3)

Also, it seems like I could use a list of methods that are available with this connection. Is there a resource out the like this?

Thanks.

[randallc]

Hi,

I think the above summarizes my knowledge (I don't understand it, just can work the example!).

I would go with getting a script going with the Autofilter if @LocoDarwin can get it working OK; a neat short-term answer while you explore the other technique.

@ptrex has used the adodb.recordset a lot and may be able to help understand and develop it. [or search out his scripts for more examples of methods; may help understand]

[PS if there were more than 1 matching answer, I think that would be available in the object returned; just keep reading the values?]

Best, randall

Edited November 7, 2006 by randallc

[litlmike]

Hi,

I think the above summarizes my knowledge (I don't understand it, just can work the example!).

I would go with getting a script going with the Autofilter if @LocoDarwin can get it working OK; a neat short-term answer while you explore the other technique.

@ptrex has used the adodb.recordset a lot and may be able to help understand and develop it. [or search out his scripts for more examples of methods; may help understand]

[PS if there were more than 1 matching answer, I think that would be available in the object returned; just keep reading the values?]

Best, randall

Thanks for the ref. to ptrex, and thanks for the help you've given. I think you may be right about the data being available in the object returned. But HOW do I continue reading the values?

I tried goofing around with some loops, but that did not work. Probably because I really don't know what the lines of code are saying. The other thought was that maybe the data is returned in an array, but I was unable to retrieve it as of yet, if so.

Ideas?

Thanks.

[randallc]

Hi,

Not off hand; I think my AccessCom UDF [link in sig] might use some of those loop methods?... I can look later

Best, randall

[1905russell]

You might find this useful.

http://www.microsoft.com/technet/scriptcen...5/tips0607.mspx

[litlmike]

You might find this useful.

http://www.microsoft.com/technet/scriptcen...5/tips0607.mspx

Ooooo... I like. I will read it now, and see what I can find out.

Thanks!

[litlmike]

You might find this useful.

http://www.microsoft.com/technet/scriptcen...5/tips0607.mspx

I want to cry after reading the article. He was about to answer my question and then didn't! He went all the way to explain that you could answer my problem, but then he didn't! ahhh! So close!

Thanks for that reading mat. I enjoyed it.