Jump to content

Criteria based Sorting in an Excel File

litlmike

By litlmike,
in AutoIt General Help and Support

 Share

Recommended Posts

randallc Universalist

randallc

Posted
Posted (edited)

Hi,

;AccessExcelExs3.au3 
Local $o_Con, $o_Con2, $o_Rs
#include "_ArrayView2D1D.au3"
#include"AccessCOM.au3"
Global $s_dbname=FileGetShortName(@ScriptDir&"\Excel Example.xls"), $s_Tablename = "[Sheet1$]"
;CONNECT========================================================
;~ _AccessConnectConn ($s_dbname, $o_Con, 0)
$o_adoCon = ObjCreate("ADODB.Connection")
$o_adoCon.Open ("Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq="&$s_dbname&";") 
;DISPLAY1========================================================
$query = "SELECT * FROM " & $s_Tablename; & " IN '" & $s_dbname & "'"; &" IN "&$s_dbname; & " WHERE " & $Fieldname0 & " = 'OPEN'"
$ar_Rows = _RecordSearch ($s_dbname, $query, $o_adoCon)
;~ _ArrayViewQueryTable($ar_Rows,$query)
_ArrayView2D1D ($ar_Rows, $query & "$s_Tablename Array", 1)
;~ for $i=1 to UBound($ar_Rows)-1
for $i=1 to 2
    MsgBox(0,"","$s_Fysmac["&$i&"]="&$ar_Rows[$i][3]&@CRLF&"$s_Logmac["&$i&"]="&$ar_Rows[$i][4])
Next
This udf gets it into the array 2D

Best, Randall

[so the func "RecordSearch" in Accesscom.au3 has the loop....]

Edited by randallc
ExcelCOM... AccessCom.. Word2... FileListToArrayNew...SearchMiner... Regexps...SQL...Explorer...Array2D.. _GUIListView...array problem...APITailRW
Link to comment
Share on other sites
  • Replies 53
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

randallc Universalist

randallc

Posted
Posted (edited)

Simpler;

;AccessExcelExs3.au3 
#include"AccessCOM.au3"
Global $s_dbname=FileGetShortName(@ScriptDir&"\Excel Example.xls"), $s_Tablename = "[Sheet1$]"
$o_adoCon = ObjCreate("ADODB.Connection")
$o_adoCon.Open ("Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq="&$s_dbname&";") 
$query = "SELECT * FROM " & $s_Tablename &" WHERE State = 'CA' and Town = 'San Francisco';"
$ar_Rows = _RecordSearch ($s_dbname, $query, $o_adoCon)
_ArrayViewQueryTable($ar_Rows,$query)
Randall Edited by randallc
ExcelCOM... AccessCom.. Word2... FileListToArrayNew...SearchMiner... Regexps...SQL...Explorer...Array2D.. _GUIListView...array problem...APITailRW
Link to comment
Share on other sites
randallc Universalist

randallc

Posted
Posted

Hi,

Here's a modified 2D array grabber; record search;

Func _RecordSearch1($s_dbname, $_query, ByRef $o_adoCon, $i_adoMDB = 1, $USRName = "", $PWD = "")
    $o_adoRs = ObjCreate("ADODB.Recordset")
    $o_adoRs.CursorType = 1
    $o_adoRs.LockType = 3
    $o_adoRs.Open ($_query, $o_adoCon)
    With $o_adoRs
        Dim $_output[.RecordCount + 1][.Fields.Count + 1]
        $_output[0][0] = .RecordCount
        For $i = 1 To .Fields.Count
            $_output[0][$i] = .Fields ($i - 1).Name
        Next
        If $o_adoRs.RecordCount Then
            $z = 0
            While Not .EOF
                $z = $z + 1
                For $x = 1 To .Fields.Count
                    $_output[$z][$x] = .Fields ($x - 1).Value
                Next
                .MoveNext
            WEnd
        EndIf
    EndWith
    $o_adoRs.Close
    Return $_output
EndFunc   ;==>_RecordSearch
Best, Randall
ExcelCOM... AccessCom.. Word2... FileListToArrayNew...SearchMiner... Regexps...SQL...Explorer...Array2D.. _GUIListView...array problem...APITailRW
Link to comment
Share on other sites
litlmike Universalist

litlmike

Posted
  • Author
Posted

Hi,

Here's a modified 2D array grabber; record search;

Best, Randall

Hey there. Thanks for that code that helps a lot. I do have a question about the 2D part, does that mean that I could only use 2 criteria to search a RecordSet? What if there were 10 criteria to refine the search?

Also, I got in touch with ptrex at your reccomendation and he PMed me the following. I am still going through it, because I couldn't make much sense of it on the first read through. Maybe our fellow community members can shed some light on this. Quoted below.

Thanks again

Hi,

You can find my example on how to use the ADOR.Recordset in here http://www.autoitscript.com/forum/index.ph...c=26804&hl=

More info on how to sort the data of a recordset is here

http://www.microsoft.com/technet/scriptcen...05/hey0225.mspx

Or converted into AutoIT it gives :

CODE: AutoItConst $adVarChar = 200
Const $MaxCharacters = 255
Const $ForReading = 1
Const $ForWriting = 2
Const $Col = "ComputerName"

$DataList = ObjCreate("ADOR.Recordset")
$DataList.Fields.Append ($Col, $adVarChar, $MaxCharacters)
$DataList.Open()

 $objFSO = ObjCreate("Scripting.FileSystemObject")
 $objFile = $objFSO.OpenTextFile("C:\Test.txt", $ForReading)

Do 
    $strLine = $objFile.ReadLine
    $DataList.AddNew()
    $DataList.Fields($Col).Value = $strLine
        Msgbox(0,"Debug",$strLine)
    $DataList.Update()
Until $objFile.AtEndOfStream() 

$objFile.Close()

$DataList.Sort = $Col


If Not $DataList.Bof Then
        $DataList.MoveFirst()
EndIf
    

Do 
    Local $strText
    $strText &= $DataList.Fields($Col).Value & @CRLF
        Msgbox(0,"Debug",$strText)
    $DataList.MoveNext()
Until $DataList.EOF() 

 $objFile = $objFSO.OpenTextFile("C:\Test.txt", $ForWriting)

$objFile.WriteLine ($strText)
$objFile.Close()

This should get you started.

regards,

ptrex

_ArrayPermute()_ArrayUnique()Excel.au3 UDF
Link to comment
Share on other sites
1905russell Prodigy

1905russell

Posted
Posted

Simpler;

;AccessExcelExs3.au3 
#include"AccessCOM.au3"
Global $s_dbname=FileGetShortName(@ScriptDir&"\Excel Example.xls"), $s_Tablename = "[Sheet1$]"
$o_adoCon = ObjCreate("ADODB.Connection")
$o_adoCon.Open ("Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq="&$s_dbname&";") 
$query = "SELECT * FROM " & $s_Tablename &" WHERE State = 'CA' and Town = 'San Francisco';"
$ar_Rows = _RecordSearch ($s_dbname, $query, $o_adoCon)
_ArrayViewQueryTable($ar_Rows,$query)
Randall
Amazing!
Link to comment
Share on other sites
litlmike Universalist

litlmike

Posted
  • Author
Posted (edited)

@ptrex

I am trying to think of the best question to ask you, but I am having a hard time doing that. I guess let us start with something simple.

What is the best way to create an ADO object so that it can be used for reading and sorting?

In that, it looks like randallc, and your approach are completely different.

Edited by litlmike
_ArrayPermute()_ArrayUnique()Excel.au3 UDF
Link to comment
Share on other sites
ptrex Universalist

ptrex

Posted
Posted

@litlmike

I am not sure that I understand your question well. Because I don' t know if there is a BEST way.

I only know 1 way like shown in the example which I PM to you.

Let me explain this in bits and pieces :

AutoItConst $adVarChar = 200
Const $MaxCharacters = 255
Const $ForReading = 1
Const $ForWriting = 2
Const $Col = "ComputerName"oÝ÷ ÚB­æ­è~Ø^9¸ÞrÖ§u§]x(W(é§q©eyÝ7è*%Z+jY^v«¨´*&¦ë^®v¦z«¨¶Ç
쵫­¢+ØÀÌØí½©M

-> We close everything.

I think this explains in detail how to fill up an ADOR.record set with data, sort it and retrieve it back after sorting.

What can I tell more about it :whistle:

If you think it is to difficult for what you need, an alternative is to use the embedded EXCEL GRID and do the sorting in the EXCEL GRID.

This way the user can sort on anything like in Excel.

See my signature for the Excel Grid link.

regards,

ptrex

Contributions :Firewall Log Analyzer for XP - Creating COM objects without a need of DLL's - UPnP support in AU3Crystal Reports Viewer - PDFCreator in AutoIT - Duplicate File FinderSQLite3 Database functionality - USB Monitoring - Reading Excel using SQLRun Au3 as a Windows Service - File Monitor - Embedded Flash PlayerDynamic Functions - Control Panel Applets - Digital Signing Code - Excel Grid In AutoIT - Constants for Special Folders in WindowsRead data from Any Windows Edit Control - SOAP and Web Services in AutoIT - Barcode Printing Using PS - AU3 on LightTD WebserverMS LogParser SQL Engine in AutoIT - ImageMagick Image Processing - Converter @ Dec - Hex - Bin -Email Address Encoder - MSI Editor - SNMP - MIB ProtocolFinancial Functions UDF - Set ACL Permissions - Syntax HighLighter for AU3ADOR.RecordSet approach - Real OCR - HTTP Disk - PDF Reader Personal Worldclock - MS Indexing Engine - Printing ControlsGuiListView - Navigation (break the 4000 Limit barrier) - Registration Free COM DLL Distribution - Update - WinRM SMART Analysis - COM Object Browser - Excel PivotTable Object - VLC Media Player - Windows LogOnOff Gui -Extract Data from Outlook to Word & Excel - Analyze Event ID 4226 - DotNet Compiler Wrapper - Powershell_COM  - New

Link to comment
Share on other sites
randallc Universalist

randallc

Posted
Posted (edited)

Hi,

1. [EDIT] @ptrex is showing how it CAN be done without SQL queries or using Excel sheet or DB connection; but why lose that functionality?.... ; maybe personal preference... perhaps disconnected runs quicker, but a lot more work to get it written?

2. I am not sure if you want to sort; if you only need to sort the selected query result items, use "Order by" in your query; fast learning curve if you are going to learn SQL queries as well now!.

3. You can always update your data by using "Execute" (see the Execute example func in AccessCOM udf.)

4. the 2D array is rows and columns dimensions of the returned query result; nothing to do with how many criteria you used in the SQL query.

;AccessExcelExs5.au3 
#include"AccessCOM.au3"
Global $s_dbname=FileGetShortName(@ScriptDir&"\Excel Example.xls"), $s_Tablename = "[Sheet1$]"
$o_adoCon = ObjCreate("ADODB.Connection")
$o_adoCon.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="&$s_dbname&";Extended Properties=""Excel 8.0;HDR=Yes;"";")
$query = "SELECT * FROM " & $s_Tablename &" WHERE State = 'CA' and Town = 'San Francisco'"& _
    " and Name LIKE '%1%' and Category  LIKE '%1%'"& _
    " ORDER by Category Desc ;"
$ar_Rows = _RecordSearch ($s_dbname, $query, $o_adoCon)
_ArrayViewQueryTable($ar_Rows,$query)
;LOOK at the Whole sheet sorted first on State Desc, subsort Category Asc;
$query = "SELECT * FROM " & $s_Tablename & _
    " ORDER by State Desc , Category Asc ;"
$ar_Rows = _RecordSearch ($s_dbname, $query, $o_adoCon)
_ArrayViewQueryTable($ar_Rows,$query)
Best, randall Edited by randallc
ExcelCOM... AccessCom.. Word2... FileListToArrayNew...SearchMiner... Regexps...SQL...Explorer...Array2D.. _GUIListView...array problem...APITailRW
Link to comment
Share on other sites
ptrex Universalist

ptrex

Posted
Posted

@randallc/litlmike

but why lose that functionality

I am not sure what you are referring to ? I don' t see any functionality missing.

perhaps disconnected runs quicker,

YES it does and that's why it is the preferred option

but a lot more work to get it written

I think it is the opposite way. Look at the example below.

There are as much of lines of code needed to get it to work.

And most important of all NO INCLUDES are DEPENDENCIES are needed. :whistle:

Const $adOpenStatic = 3
Const $adLockOptimistic = 3
Const $adCmdText = 0x0001 ;  =&H0001
Global $s_Filename=FileGetShortName("C:\Book1.xls")
Global $s_Tablename = "[Sheet1$]" ; 

$objConnection = ObjCreate("ADODB.Connection")
$objRecordSet = ObjCreate("ADODB.Recordset")

$objConnection.Open ("Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source="&$s_Filename&";" & _
            "Extended Properties=""Excel 8.0;HDR=Yes;"";")

$objRecordSet.Open ("Select * FROM"& $s_Tablename & "Order by 3 Asc" , _
                $objConnection, $adOpenStatic, $adLockOptimistic, $adCmdText) 
                                    
Do 
    ConsoleWrite ($objRecordSet.Fields(1).value &" | "& $objRecordSet.Fields(2).value &" | "& _
        $objRecordSet.Fields(4).value &" | "& $objRecordSet.Fields(5).value&@CR)
    $objRecordSet.MoveNext()
Until $objRecordSet.EOF()

In order to test, create an Excel file with 5 columns of data.

Enjoy !!

Contributions :Firewall Log Analyzer for XP - Creating COM objects without a need of DLL's - UPnP support in AU3Crystal Reports Viewer - PDFCreator in AutoIT - Duplicate File FinderSQLite3 Database functionality - USB Monitoring - Reading Excel using SQLRun Au3 as a Windows Service - File Monitor - Embedded Flash PlayerDynamic Functions - Control Panel Applets - Digital Signing Code - Excel Grid In AutoIT - Constants for Special Folders in WindowsRead data from Any Windows Edit Control - SOAP and Web Services in AutoIT - Barcode Printing Using PS - AU3 on LightTD WebserverMS LogParser SQL Engine in AutoIT - ImageMagick Image Processing - Converter @ Dec - Hex - Bin -Email Address Encoder - MSI Editor - SNMP - MIB ProtocolFinancial Functions UDF - Set ACL Permissions - Syntax HighLighter for AU3ADOR.RecordSet approach - Real OCR - HTTP Disk - PDF Reader Personal Worldclock - MS Indexing Engine - Printing ControlsGuiListView - Navigation (break the 4000 Limit barrier) - Registration Free COM DLL Distribution - Update - WinRM SMART Analysis - COM Object Browser - Excel PivotTable Object - VLC Media Player - Windows LogOnOff Gui -Extract Data from Outlook to Word & Excel - Analyze Event ID 4226 - DotNet Compiler Wrapper - Powershell_COM  - New

Link to comment
Share on other sites
litlmike Universalist

litlmike

Posted
  • Author
Posted

@randallc/litlmike

In order to test, create an Excel file with 5 columns of data.

Enjoy !!

Well, if you got randallc to surrender this must be the way to go! Haha!

Maybe you can help me put this all in perspective. But, first I want to make sure you understand what my real goal is. I need to be able to report back only the results that match the user's selected criteria. I mention this because I THINK in the examples you gave I couldn't tell what was being accomplished, quite yet. Your code is just way over my head!

I have provided some GUI coding to show an example of what the front end for the user would look like. It is very ugly, but I wanted you to get the idea. These are the exact criteria they will be using. What I tried to accomplish in the GUI was to create an expression builder that would make a line similar to your:

objRecordSet.Open ("Select * FROM"& $s_Tablename & "Order by 3 Asc" oÝ÷ Ù8^­éÉ·mëmz¼ÊÚ¢Øb°-¹©eÈh¥ëaz·¢±¦Þ¶×«Á¬·Ý8^B¡×¡z·¢°Ó¥êßyËEyç[iÉ)æ¬y«­¢+Ø¥¹±Õ±ÐíU%
½¹ÍѹÑ̹ÔÌÐì(()±½°ÀÌØí%¹ÁÕÐÄ°ÀÌØí%¹ÁÕÐÈ°ÀÌØí%¹ÁÕÐÌ°ÀÌØí%¹ÁÕÐаÀÌØí%¹ÁÕÐÔ°ÀÌØí%¹ÁÕÐØ((íøÀÌØí%¹ÁÕÐÄô%¹ÁÕÑ   ½à ÅÕ½ÐìÅÕ½Ðì°ÅÕ½Ðí
¥ÑäÅÕ½Ðì¤(íøÀÌØí%¹ÁÕÐÈô%¹ÁÕÑ   ½à ÅÕ½ÐìÅÕ½Ðì°ÅÕ½ÐíMÑÑÅÕ½Ðì¤(ÀÌØìÄôÅÕ½ÐíM1
P¨I=4mM¡ÐÄÀÌØít]!IÅÕ½Ðì(ÀÌØìÈôÅÕ½ÐìÅÕ½Ðì(ÀÌØìÌôÅÕ½ÐíM¥Ñ9µôÅÕ½Ðì((ÀÌØìÐôÅÕ½Ðí
ѽÉäôÅÕ½Ðì(íøÀÌØìÐôÅÕ½ÐíMÑÑôÅÕ½Ðìì¹ÍÝÈ­ä((íøÀÌØìÐôÅÕ½ÐìÌäí
ÌäìÅÕ½Ðì(ÀÌØìÜôÅÕ½Ðì¹ÅÕ½Ðì(ÀÌØìàôÅÕ½Ðí
¥ÑäôÅÕ½Ðì(íøÀÌØìØôÅÕ½ÐìÌäíM¸É¹¥Í¼ÌäììÅÕ½Ðìì¹ÍÝÈ­ä(ÀÌØíÍÑÑôÅÕ½ÐíMÑÑôÅÕ½Ðì(ÀÌØíÑÕÉôÅÕ½ÐíÑÕÉôÅÕ½Ðì(ÀÌØíÑ¥ÙôÅÕ½ÐíÑ¥ÙôÅÕ½Ðì((ÀÌØí½°ôÅÕ½ÐíM1
P¨I=4mM¡ÐÄÀÌØít]!IMÑÑôÌäí
Ìäì¹
¥ÑäôÌäíM¸É¹¥Í¼ÌäììÅÕ½Ðì(ÀÌØí}ÅÕÉäôÀÌØìĵÀìÀÌØìÈ(ÀÌØìÔôÅÕ½ÐìÌäìÅÕ½Ðì(ÀÌØìØôÅÕ½ÐìÌäìÅÕ½Ðì((ÀÌØìäôÅÕ½ÐìÌäìÅÕ½Ðì(ÀÌØìÄÀôÅÕ½ÐìÌäììÅÕ½Ðì(ìììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììì(ÀÌØí½É´Å|ÄôU%
ÉÑ ÅÕ½Ðí½É´ÄÅÕ½Ðì°ÜÜÀ°Ôäà°ÄäÄ°ÄÄÔ¤(ÀÌØí    ÕÑѽ¸ÄôU%
Ñɱ
ÉÑ    ÕÑѽ¸ ÅÕ½Ðí=,ÅÕ½Ðì°ÄØ°ÌØà°ÜÔ°ÈÔ°À¤(ÀÌØí  ÕÑѽ¸ÈôU%
Ñɱ
ÉÑ    ÕÑѽ¸ ÅÕ½Ðí   Õ¥±áÁÉÍÍ¥½¸½ÈÀÌØí}ÅÕÉäÅÕ½Ðì°ÄØ°ÐÀà°ÈÄÄ°Ðä°À¤(ÀÌØí1°ÄôU%
Ñɱ
ÉÑ1° ÅÕ½ÐìÀÌØí¥¹±QáÐÅÕ½Ðì°ÄØ°ÐÜÈ°ÐÀÀ°ØÀ¤(ÀÌØí  ÕÑѽ¸ÌôU%
Ñɱ
ÉÑ    ÕÑѽ¸ ÅÕ½ÐíIÍÐÅÕ½Ðì°ÈÐÀ°ÐÀà°ÄÀÔ°Ðä°À¤(ÀÌØíɽÕÀÌôU%
Ñɱ
ÉÑɽÕÀ ÅÕ½ÐíɽÕÀÌÅÕ½Ðì°ÐÈÔ°ÈÔ°ÈÀÀ°ÄÔÔ¤(ÀÌØí
½µ¼ÈôU%
Ñɱ
ÉÑ
½µ¼ ÅÕ½Ðí
¥ÑäÅÕ½Ðì°ÐÌÔ°àÔ°ÄÐÔ°ÈÔ¤)U%
ÑɱMÑMÑÑ ´Ä°ÀÌØíU%}%M  1¤)U%
ÑɱMÑÑ ´Ä°ÅÕ½Ðí]ÍÐI¥¡±¹ñ=¬!¥±±ñµÉåÙ¥±±ñ
ÁQ½Ý¹ñ!¥¹Íñ    É­±åñ  ½Íѽ¹ñ]½½Íѽ­ñ ½Õ¹Ñ¥Õ±ñ%ͱ½A±µÍñM¸É¹¥Í½ñA¡¥±±Á¡¥ñ5É¥ñ5½Õ¹ÐA±Í¹ÑñM¸É¹¥Í½ñɱ¥¹Ñ½¹ñA¥¹éñÉÕ¥Ññ-¡Õ±Õ¥ñ9Üe½É¬ÅÕ½Ðì¤(ÀÌØí
¡­½àÌôU%
Ñɱ
ÉÑ
¡­½à ÅÕ½ÐíUÍ
¥ÑäÅÕ½Ðì°ÐÌÔ°ÔÔ°äÜ°ÈÔ¤)U%
Ñɱ
ÉÑɽÕÀ ÅÕ½ÐìÅÕ½Ðì°´ää°´ää°Ä°Ä¤(ÀÌØíɽÕÀÈôU%
Ñɱ
ÉÑɽÕÀ ÅÕ½ÐíɽÕÀÈÅÕ½Ðì°ÄÔ°ÈÀÀ°ÈÀÀ°ÄÔÔ¤(ÀÌØí
½µ¼ÌôU%
Ñɱ
ÉÑ
½µ¼ ÅÕ½ÐíMÑÑÅÕ½Ðì°ÈÔ°ÈØÀ°ÄÐÔ°ÈÔ¤)U%
ÑɱMÑMÑÑ ´Ä°ÀÌØíU%}%M  1¤)U%
ÑɱMÑÑ ´Ä°ÅÕ½Ðí]ñ]Yñ
ñ]ÍÑɸ
Áñ-ñ
ñ5ñYQñUQñM
ñ
ñAð¹ñM
ñ
ñ5ñM¥¹ñ
=ñ!%ñ9dÅÕ½Ðì¤(ÀÌØí
¡­½àÐôU%
Ñɱ
ÉÑ
¡­½à ÅÕ½ÐíUÍMÑÑÅÕ½Ðì°ÈÔ°ÈÌÀ°äÜ°ÈÔ¤)U%
Ñɱ
ÉÑɽÕÀ ÅÕ½ÐìÅÕ½Ðì°´ää°´ää°Ä°Ä¤(ÀÌØíɽÕÀôU%
Ñɱ
ÉÑɽÕÀ ÅÕ½ÐíɽÕÀÅÕ½Ðì°ÄÔ°ÈÔ°ÈÀÀ°ÄÔÔ¤(ÀÌØí
¡­½àÄôU%
Ñɱ
ÉÑ
¡­½à ÅÕ½ÐíM¥Ñ9µÅÕ½Ðì°ÈÔ°ÔÔ°ÄÄÌ°ÈÔ¤(ÀÌØí%¹ÁÕÑ¥±ÄôU%
Ñɱ
ÉÑ%¹ÁÕÐ ÅÕ½Ðí%¹ÁÕÐÄÅÕ½Ðì°ÈÔ°àÔ°ÄÈÄ°ÈĤ)U%
ÑɱMÑMÑÑ ´Ä°ÀÌØíU%}%M  1¤)U%
Ñɱ
ÉÑɽÕÀ ÅÕ½ÐìÅÕ½Ðì°´ää°´ää°Ä°Ä¤(ÀÌØíɽÕÀÐôU%
Ñɱ
ÉÑɽÕÀ ÅÕ½ÐíɽÕÀÐÅÕ½Ðì°ÈÈÀ°ÈÔ°ÈÀÀ°ÄÔÔ¤(ÀÌØí
¡­½àÈôU%
Ñɱ
ÉÑ
¡­½à ÅÕ½Ðí
ѽÉäÅÕ½Ðì°ÈÌÀ°ÔÔ°äÜ°ÈÔ¤(ÀÌØí
½µ¼ÄôU%
Ñɱ
ÉÑ
½µ¼ ÅÕ½Ðí
½µ¼ÄÅÕ½Ðì°ÈÌÀ°àÔ°ÄÐÔ°ÈÔ¤)U%
ÑɱMÑMÑÑ ´Ä°ÀÌØíU%}%M  1¤)U%
ÑɱMÑÑ ´Ä°ÅÕ½ÐíÙ¹ÑÕÉ
ÉÕ¥ÍÍñ¥È
½µÐM¡½½±ÍñÉ¡½±½¥°Q½ÕÉÍñQXQ½ÕÉÍñ   ¬
½Õ¹ÑÉäM­¥¥¹ñ ¥å±Q½ÕÉ̵Àì5½Õ¹Ñ¥¸  ¥­¥¹ñ  ÕÌQ½ÕÉ̵Àì
½ áÕÉÍ¥½¹Íñ
¹½¥¹ñ
ÑѱɥÙ̵Àì]½É­¥¹I¹¡Íñ
½ÉÁ½ÉÑQ´ Õ¥±¥¹ñ
ɽÍÌ
½Õ¹ÑÉäM­¥¥¹ñ
Õ±¥¹ÉäYÑ¥½¹Íñ
Õ±ÑÕÉ°Q½ÕÉÍñ¥Í½¹Ñ¥¹Õ´9Ñ¥½¹°AɬÁÁɽÙ1½Íñ½M±¥¹YÑ¥½¹ÍñÕµÀìÕÍÐI¹¡Íñ¼µÀì)Õ¹±1½Íñ½Ñ½Õɥ͵ñÕÑ¥½¹°QÉÙ±ñµ¥±ä
µÁÍñ¥Í¡¥¹
¡ÉÑÉÍñ¥Í¡¥¹Õ¥Íñ¥Í¡¥¹1½Íñ½±YÑ¥½¹Íñ!±¤µÙ¹ÑÕÉÍñ!±¤µM­¥¥¹ñ!¥­¥¹µÀìQÉ­­¥¹Q½ÕÉÍñ!½ÉÍA¬QÉ¥Á̵ÀìQÉ¥°I¥Íñ!½ÉͬI¥¥¹MѱÍñ!½Ð¥È   ±±½½¹¥¹ñ!½ÕͽÐI¹Ñ±Íñ)Ð   ½ÐQ½ÕÉÍñ-å­¥¹ñ5½Ñ½Éå±Q½ÕÉÍñ5½Õ¹Ñ¥¸µÀìI½¬
±¥µ¥¹ñ5ձѥMÁ½ÉÐÙ¹ÑÕÉÍñ=Õѽ½ÈM­¥±±ÌQÉ¥¹¥¹ñA¥¹Ñ±°µÀìM­¥Éµ¥Í Ù¹ÑÕÉÍñA¡½Ñ½ÉÁ¡äQ½ÕÉÍñAÉ¥ÙÑe¡Ð
¡ÉÑÉÍñI¥±ÝäµÀìQÉ¥¸Q½ÕÉÍñIͽÉÑÌ°MÁ̵ÀìIÑÉÑÍñMÉ¥ÍñMÕ¥Ù¥¹YÑ¥½¹ÍñM¹½ÝÐM­¥¥¹ñM¹½Ýµ½¥±¥¹ñM½É¥¹µÀì±¥ÈI¥ÍñMÕÉ¥¹YÑ¥½¹ÌµÀì
µÁÍñ]½¸QÉ¥¸YÑ¥½¹Íñ]±­¥¹Q½ÕÉÍñ]¡±]Ñ¡¥¹Q½ÕÉÍñ]¡¥ÑÝÑÈIÑ¥¹ñ]¥±±¥Y¥Ý¥¹Q½ÕÉÍñ]½µ¸ÅÕ½ÐìµÀí
¡È Ì䤵ÀìÅÕ½ÐíÌQÉÙ°ÅÕ½Ðì¤)U%
Ñɱ
ÉÑɽÕÀ ÅÕ½ÐìÅÕ½Ðì°´ää°´ää°Ä°Ä¤(ÀÌØíɽÕÀÔôU%
Ñɱ
ÉÑɽÕÀ ÅÕ½ÐíɽÕÀÔÅÕ½Ðì°ÈÈÀ°ÈÀÀ°ÈÀÀ°ÄÔÔ¤(ÀÌØí
¡­½àÔôU%
Ñɱ
ÉÑ
¡­½à ÅÕ½ÐíÑÕÉÅÕ½Ðì°ÈÌÀ°ÈÌÀ°äÜ°ÈÔ¤(ÀÌØí
½µ¼ÐôU%
Ñɱ
ÉÑ
½µ¼ ÅÕ½Ðí
½µ¼ÐÅÕ½Ðì°ÈÌÀ°ÈØÀ°ÄÐÔ°ÈÔ¤)U%
ÑɱMÑMÑÑ ´Ä°ÀÌØíU%}%M  1¤)U%
ÑɱMÑÑ ´Ä°ÅÕ½ÐíeÍñ9¼ÅÕ½Ðì¤)U%
Ñɱ
ÉÑɽÕÀ ÅÕ½ÐìÅÕ½Ðì°´ää°´ää°Ä°Ä¤(ÀÌØíɽÕÀØôU%
Ñɱ
ÉÑɽÕÀ ÅÕ½ÐíɽÕÀØÅÕ½Ðì°ÐÈÔ°ÈÀÀ°ÈÀÀ°ÄÔÔ¤(ÀÌØí
¡­½àØôU%
Ñɱ
ÉÑ
¡­½à ÅÕ½ÐíÑ¥ÙÅÕ½Ðì°ÐÌÔ°ÈÌÀ°äÜ°ÈÔ¤(ÀÌØí
½µ¼ÔôU%
Ñɱ
ÉÑ
½µ¼ ÅÕ½Ðí
½µ¼ÐÅÕ½Ðì°ÐÌÔ°ÈØÀ°ÄÐÔ°ÈÔ¤)U%
ÑɱMÑMÑÑ ´Ä°ÀÌØíU%}%M  1¤)U%
ÑɱMÑÑ ´Ä°ÅÕ½ÐíeÍñ9¼ÅÕ½Ðì¤)U%
Ñɱ
ÉÑɽÕÀ ÅÕ½ÐìÅÕ½Ðì°´ää°´ää°Ä°Ä¤)U%MÑMÑÑ¡M]}M!=¤(¹I¥½¸9-½U$ÍÑ¥½¸(ììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììì(()]¡¥±Ä($ÀÌØí¹5ÍôU%Ñ5Í ¤(%MÝ¥Ñ ÀÌØí¹5Í($%
ÍÀÌØíU%}Y9Q}
1=M($$%á¥Ð($$(ìììììììììììììììììììììììììììììììììììììììììììììììM¥Ñ%ìììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììì($%
ÍÀÌØí
¡­½àÄ($$%% ¥Ñ¹¡U%
ÑɱI ÀÌØí
¡­½àĤ°ÀÌØíU%}
!
-¤Q¡¸($$$%U%
ÑɱMÑMÑÑ ÀÌØí%¹ÁÕÑ¥±Ä°ÀÌØíU%}9    1¤($$%±Í%    ¥Ñ¹¡U%
ÑɱI ÀÌØí
¡­½àĤ°ÀÌØíU%}U9
!
-¤Q¡¸($$$%U%
ÑɱMÑMÑÑ ÀÌØí%¹ÁÕÑ¥±Ä°ÀÌØíU%}%M   1¤($$%¹%($$$($%
ÍÀÌØí%¹ÁÕÑ¥±Ä($$$ÀÌØí%¹ÁÕÐÄôU%
ÑɱI ÀÌØí%¹ÁÕÑ¥±Ä¤($$$ÀÌØí}ÅÕÉäµÀìôÀÌØìÌ($$$ÀÌØí}ÅÕÉäµÀìôÀÌØìÔ($$$ÀÌØí}ÅÕÉäµÀìôÀÌØí%¹ÁÕÐÄ($$$ÀÌØí}ÅÕÉäµÀìôÀÌØìÔ($$$(ììììììììììììììììììììììììììììììììììììììììììììììììììì
ѽÉäìììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììì($%
ÍÀÌØí
¡­½àÈ($$%% ¥Ñ¹¡U%
ÑɱI ÀÌØí
¡­½àȤ°ÀÌØíU%}
!
-¤Q¡¸($$$%U%
ÑɱMÑMÑÑ ÀÌØí
½µ¼Ä°ÀÌØíU%}9  1¤($$%±Í%    ¥Ñ¹¡U%
ÑɱI ÀÌØí
¡­½àȤ°ÀÌØíU%}U9
!
-¤Q¡¸($$$%U%
ÑɱMÑMÑÑ ÀÌØí
½µ¼Ä°ÀÌØíU%}%M 1¤($$%¹%($$$($$$($%
ÍÀÌØí
½µ¼Ä($$$ÀÌØí%¹ÁÕÐÈôU%
ÑɱI ÀÌØí
½µ¼Ä¤($$%% ¥Ñ¹¡U%
ÑɱI ÀÌØí
¡­½àĤ°ÀÌØíU%}
!
-¤Q¡¸(íø$$$$ÀÌØí}ÅÕÉäµÀìôÀÌØìÈ($$$$ÀÌØí}ÅÕÉäµÀìôÀÌØìÜ($$%¹%($$$ÀÌØí}ÅÕÉäµÀìôÀÌØìÐ($$$ÀÌØí}ÅÕÉäµÀìôÀÌØìÔ($$$ÀÌØí}ÅÕÉäµÀìôÀÌØí%¹ÁÕÐÈ($$$ÀÌØí}ÅÕÉäµÀìôÀÌØìÔ($$$($$$(ììììììììììììììììììììììììììììììììììììììììììììììììììì
¥Ñäììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììì$$$($$$($%
ÍÀÌØí
¡­½àÌ($$%% ¥Ñ¹¡U%
ÑɱI ÀÌØí
¡­½à̤°ÀÌØíU%}
!
-¤Q¡¸($$$%U%
ÑɱMÑMÑÑ ÀÌØí
½µ¼È°ÀÌØíU%}9  1¤($$%±Í%    ¥Ñ¹¡U%
ÑɱI ÀÌØí
¡­½à̤°ÀÌØíU%}U9
!
-¤Q¡¸($$$%U%
ÑɱMÑMÑÑ ÀÌØí
½µ¼È°ÀÌØíU%}%M 1¤($$%¹%($$$($%
ÍÀÌØí
½µ¼È($$$ÀÌØí%¹ÁÕÐÌôU%
ÑɱI ÀÌØí
½µ¼È¤($$$($$%% ¥Ñ¹¡U%
ÑɱI ÀÌØí
¡­½àĤ°ÀÌØíU%}
!
-¤½È ¥Ñ¹¡U%
ÑɱI ÀÌØí
¡­½àȤ°ÀÌØíU%}
!
-¤½È ¥Ñ¹¡U%
ÑɱI ÀÌØí
¡­½àФ°ÀÌØíU%}
!
-¤Q¡¸(($$$$ÀÌØí}ÅÕÉäµÀìôÀÌØìÜ($$%¹%($$$($$$ÀÌØí}ÅÕÉäµÀìôÀÌØìà($$$ÀÌØí}ÅÕÉäµÀìôÀÌØìÔ($$$ÀÌØí}ÅÕÉäµÀìôÀÌØí%¹ÁÕÐÌ($$$ÀÌØí}ÅÕÉäµÀìôÀÌØìä(ìììììììììììììììììììììììììììììììììììììììììììììììììììMÑÑììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììì$$$($%
ÍÀÌØí
¡­½àÐ($$%% ¥Ñ¹¡U%
ÑɱI ÀÌØí
¡­½àФ°ÀÌØíU%}
!
-¤Q¡¸($$$%U%
ÑɱMÑMÑÑ ÀÌØí
½µ¼Ì°ÀÌØíU%}9  1¤($$%±Í%    ¥Ñ¹¡U%
ÑɱI ÀÌØí
¡­½àФ°ÀÌØíU%}U9
!
-¤Q¡¸($$$%U%
ÑɱMÑMÑÑ ÀÌØí
½µ¼Ì°ÀÌØíU%}%M 1¤($$%¹%($$$($%
ÍÀÌØí
½µ¼Ì($$$ÀÌØí%¹ÁÕÐÌôU%
ÑɱI ÀÌØí
½µ¼Ì¤($$$($$%% ¥Ñ¹¡U%
ÑɱI ÀÌØí
¡­½àĤ°ÀÌØíU%}
!
-¤½È ¥Ñ¹¡U%
ÑɱI ÀÌØí
¡­½àȤ°ÀÌØíU%}
!
-¤½È ¥Ñ¹¡U%
ÑɱI ÀÌØí
¡­½à̤°ÀÌØíU%}
!
-¤Q¡¸($$$$ÀÌØí}ÅÕÉäµÀìôÀÌØìÜ($$%¹%($$$($$$ÀÌØí}ÅÕÉäµÀìôÀÌØíÍÑÑ($$$ÀÌØí}ÅÕÉäµÀìôÀÌØìÔ($$$ÀÌØí}ÅÕÉäµÀìôÀÌØí%¹ÁÕÐÌ($$$ÀÌØí}ÅÕÉäµÀìôÀÌØìÔ(ìììììììììììììììììììììììììììììììììììììììììììììììììììÑÕÉììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììì($%
ÍÀÌØí
¡­½àÔ($$%% ¥Ñ¹¡U%
ÑɱI ÀÌØí
¡­½àÔ¤°ÀÌØíU%}
!
-¤Q¡¸($$$%U%
ÑɱMÑMÑÑ ÀÌØí
½µ¼Ð°ÀÌØíU%}9  1¤($$%±Í%    ¥Ñ¹¡U%
ÑɱI ÀÌØí
¡­½àÔ¤°ÀÌØíU%}U9
!
-¤Q¡¸($$$%U%
ÑɱMÑMÑÑ ÀÌØí
½µ¼Ð°ÀÌØíU%}%M 1¤($$%¹%($$$($%
ÍÀÌØí
½µ¼Ð($$$ÀÌØí%¹ÁÕÐÐôU%
ÑɱI ÀÌØí
½µ¼Ð¤($$$($$%% ¥Ñ¹¡U%
ÑɱI ÀÌØí
¡­½àĤ°ÀÌØíU%}
!
-¤½È ¥Ñ¹¡U%
ÑɱI ÀÌØí
¡­½àȤ°ÀÌØíU%}
!
-¤½È ¥Ñ¹¡U%
ÑɱI ÀÌØí
¡­½à̤°ÀÌØíU%}
!
-¤Q¡¸($$$$ÀÌØí}ÅÕÉäµÀìôÀÌØìÜ($$%¹%($$$($$$ÀÌØí}ÅÕÉäµÀìôÀÌØíÑÕÉ($$$ÀÌØí}ÅÕÉäµÀìôÀÌØìÔ($$$ÀÌØí}ÅÕÉäµÀìôÀÌØí%¹ÁÕÐÐ($$$ÀÌØí}ÅÕÉäµÀìôÀÌØìÔ((ìììììììììììììììììììììììììììììììììììììììììììììììììììÑ¥Ùììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììì($%
ÍÀÌØí
¡­½àØ($$%% ¥Ñ¹¡U%
ÑɱI ÀÌØí
¡­½àؤ°ÀÌØíU%}
!
-¤Q¡¸($$$%U%
ÑɱMÑMÑÑ ÀÌØí
½µ¼Ô°ÀÌØíU%}9  1¤($$%±Í%    ¥Ñ¹¡U%
ÑɱI ÀÌØí
¡­½àؤ°ÀÌØíU%}U9
!
-¤Q¡¸($$$%U%
ÑɱMÑMÑÑ ÀÌØí
½µ¼Ô°ÀÌØíU%}%M 1¤($$%¹%($$$($%
ÍÀÌØí
½µ¼Ô($$$ÀÌØí%¹ÁÕÐÔôU%
ÑɱI ÀÌØí
½µ¼Ô¤($$$($$%% ¥Ñ¹¡U%
ÑɱI ÀÌØí
¡­½àĤ°ÀÌØíU%}
!
-¤½È ¥Ñ¹¡U%
ÑɱI ÀÌØí
¡­½àȤ°ÀÌØíU%}
!
-¤½È ¥Ñ¹¡U%
ÑɱI ÀÌØí
¡­½à̤°ÀÌØíU%}
!
-¤Q¡¸($$$$ÀÌØí}ÅÕÉäµÀìôÀÌØìÜ($$%¹%($$$($$$ÀÌØí}ÅÕÉäµÀìôÀÌØíÑ¥Ù($$$ÀÌØí}ÅÕÉäµÀìôÀÌØìÔ($$$ÀÌØí}ÅÕÉäµÀìôÀÌØí%¹ÁÕÐÔ($$$ÀÌØí}ÅÕÉäµÀìôÀÌØìÔ(ììììììììììììììììììììììììììììììììììììììììììììììììììì ÕÑѽ¹Ìììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììììì($%
ÍÀÌØí  ÕÑѽ¸Äì=¬   ÕÑѽ¸($$$ÀÌØí%¹ÁÕÐÄôU%
ÑɱI ÀÌØí
½µ¼È¤($$$ÀÌØí%¹ÁÕÐÈôU%
ÑɱI ÀÌØí
½µ¼Ä¤($$%á¥Ñ1½½À($$($%
ÍÀÌØí  ÕÑѽ¸Èì  Õ¥±  ÕÑѽ¸($$%U%
ÑɱMÑÑ ÀÌØí1°Ä°ÀÌØí}ÅÕÉä¤($$($%
ÍÀÌØí  ÕÑѽ¸ÌìIÍÐ ÕÑѽ¸($$$ÀÌØí}ÅÕÉäôÀÌØìĵÀìÀÌØìÈ($$%U%
ÑɱMÑÑ ÀÌØí1°Ä°ÀÌØí}ÅÕÉä¤($$((%¹MÝ¥Ñ )]¹
_ArrayPermute()_ArrayUnique()Excel.au3 UDF
Link to comment
Share on other sites
ptrex Universalist

ptrex

Posted
Posted

@litlmike

you are on the right track !! :whistle:

the only thing you need to do is make sure the SELECT criteria which is build by the GUI goes in here :

objRecordSet.Open ($Goal)

Then run a test and you will see that it works.

regards

ptrex

Contributions :Firewall Log Analyzer for XP - Creating COM objects without a need of DLL's - UPnP support in AU3Crystal Reports Viewer - PDFCreator in AutoIT - Duplicate File FinderSQLite3 Database functionality - USB Monitoring - Reading Excel using SQLRun Au3 as a Windows Service - File Monitor - Embedded Flash PlayerDynamic Functions - Control Panel Applets - Digital Signing Code - Excel Grid In AutoIT - Constants for Special Folders in WindowsRead data from Any Windows Edit Control - SOAP and Web Services in AutoIT - Barcode Printing Using PS - AU3 on LightTD WebserverMS LogParser SQL Engine in AutoIT - ImageMagick Image Processing - Converter @ Dec - Hex - Bin -Email Address Encoder - MSI Editor - SNMP - MIB ProtocolFinancial Functions UDF - Set ACL Permissions - Syntax HighLighter for AU3ADOR.RecordSet approach - Real OCR - HTTP Disk - PDF Reader Personal Worldclock - MS Indexing Engine - Printing ControlsGuiListView - Navigation (break the 4000 Limit barrier) - Registration Free COM DLL Distribution - Update - WinRM SMART Analysis - COM Object Browser - Excel PivotTable Object - VLC Media Player - Windows LogOnOff Gui -Extract Data from Outlook to Word & Excel - Analyze Event ID 4226 - DotNet Compiler Wrapper - Powershell_COM  - New

Link to comment
Share on other sites
litlmike Universalist

litlmike

Posted
  • Author
Posted

@litlmike

you are on the right track !! :whistle:

the only thing you need to do is make sure the SELECT criteria which is build by the GUI goes in here :

objRecordSet.Open ($Goal)oÝ÷ Ù8^»§j׬µ©ÝÊ°YlyëajØ­Âä²· j·l¦ÚÞÇú®¢×©ä±ú+¶«zrjwm©ä±ú+¶Ê.­¨éíßr¢¯yªk¡¹^×اíæÞ²ÜÊÚº)]¶®+^®&ØZ¶Øb°eËÞ¯*èºZÚÞjYr¢yrÂä²)Ú}ì·.ËZǬN®±êì¥u·nW­¢Ç¥yË_®µîmìjw]÷Þ­éí¢b¢{¢·r¶,zmìJaz|"¶.¶¯x-¡»¢Ö§!û-jצz{_¢·rh²ÈX­ÊX¤{½÷§-¢¸0vZ0¶®±êíçå{[r¶!¢#¬¶§)à¡û¬x-+éä¡÷(uæî´¦jY^j»ºÚ"µÍÈRY ÌÍÛØXÛÜÙ]Y[ÊK[YHH   ÌÍÐØ]YÛÜH[BSÙÐÞ
    ][ÝÉ][ÝË    ÌÍÛØXÛÜÙ]Y[Ê
K[YKBQ[YH   ÌÍÛØXÛÜÙ][ÝS^

B[[ ÌÍÛØXÛÜÙ]SÑ

Thanks.

_ArrayPermute()_ArrayUnique()Excel.au3 UDF
Link to comment
Share on other sites
ptrex Universalist

ptrex

Posted
Posted

@litlmike

If there are that many option and options in options, there is no other way than to build the logic into the GUI application.

But since it will a long list of combinations I suggest to drop the IF THEN statements and go for the SELECT CASE (see help file)

Select
    Case $var = 1
        MsgBox(0, "", "First Case expression was true")
    Case $var2 = "test"
        MsgBox(0, "", "Second Case expression was true")
    Case Else
        MsgBox(0, "", "No preceding case was true!")
EndSelect

This give you a much better overview on what option is linked to which action.

regards

ptrex

Contributions :Firewall Log Analyzer for XP - Creating COM objects without a need of DLL's - UPnP support in AU3Crystal Reports Viewer - PDFCreator in AutoIT - Duplicate File FinderSQLite3 Database functionality - USB Monitoring - Reading Excel using SQLRun Au3 as a Windows Service - File Monitor - Embedded Flash PlayerDynamic Functions - Control Panel Applets - Digital Signing Code - Excel Grid In AutoIT - Constants for Special Folders in WindowsRead data from Any Windows Edit Control - SOAP and Web Services in AutoIT - Barcode Printing Using PS - AU3 on LightTD WebserverMS LogParser SQL Engine in AutoIT - ImageMagick Image Processing - Converter @ Dec - Hex - Bin -Email Address Encoder - MSI Editor - SNMP - MIB ProtocolFinancial Functions UDF - Set ACL Permissions - Syntax HighLighter for AU3ADOR.RecordSet approach - Real OCR - HTTP Disk - PDF Reader Personal Worldclock - MS Indexing Engine - Printing ControlsGuiListView - Navigation (break the 4000 Limit barrier) - Registration Free COM DLL Distribution - Update - WinRM SMART Analysis - COM Object Browser - Excel PivotTable Object - VLC Media Player - Windows LogOnOff Gui -Extract Data from Outlook to Word & Excel - Analyze Event ID 4226 - DotNet Compiler Wrapper - Powershell_COM  - New

Link to comment
Share on other sites
litlmike Universalist

litlmike

Posted
  • Author
Posted

I GOT IT!

I cannot believe it!!!!! I actually got it!!!!!!!! Sorry but this deserves a:

WOOT!

God Bless those arrays!

Thanks soooo much for everyone's help! It paid off...big time!!!

I am posting the code and reposting the final Excel (.xls) file. I had to delete/change some info, because it is sensitive info, but you will still get the idea.

@ptrex

How can I display this data into your "Excel Grid In AutoIT"?

#include <GUIConstants.au3>

Global $aArray_Criteria[2]
Global $query

#Region ### START Koda GUI section ### Form=c:\documents and settings\...\my documents\personal\autoit\koda forms\criteriaselector_excel3.kxf
$Form1_1 = GUICreate("AForm1", 770, 598, 191, 115)
$Button1 = GUICtrlCreateButton("OK", 16, 368, 75, 25, 0)
$Button2 = GUICtrlCreateButton("Build Expression for $Final Text", 16, 408, 211, 49, 0)
$Label1 = GUICtrlCreateLabel("$FinalText", 16, 472, 53, 17)
$Button3 = GUICtrlCreateButton("Reset", 240, 408, 105, 49, 0)
$Group3 = GUICtrlCreateGroup("AGroup3", 425, 25, 200, 155)
$Combo2 = GUICtrlCreateCombo("City", 435, 85, 145, 25)
GUICtrlSetData(-1, "West Richland|Oak Hill|Emeryville|Cape Town|Haines|Berkeley|Boston|Woodstock|Bountiful|Isle of Palms|San Francisco|Philadelphia|Madrid|Mount Pleasant|San Francisco|Arlington|Pienza|Fruita|Kahului|New York")
GUICtrlSetState(-1, $GUI_DISABLE)
$Checkbox3 = GUICtrlCreateCheckbox("Use City", 435, 55, 97, 25)
GUICtrlCreateGroup("", -99, -99, 1, 1)
$Group2 = GUICtrlCreateGroup("AGroup2", 15, 200, 200, 155)
$Combo3 = GUICtrlCreateCombo("State", 25, 260, 145, 25)
GUICtrlSetData(-1, "WA|WV|CA|Western Cape|AK|CA|MA|VT|UT|SC|CA|PA|.|SC|CA|MA|Siena|CO|HI|NY")
GUICtrlSetState(-1, $GUI_DISABLE)
$Checkbox4 = GUICtrlCreateCheckbox("Use State", 25, 230, 97, 25)
GUICtrlCreateGroup("", -99, -99, 1, 1)
$Group = GUICtrlCreateGroup("AGroup", 15, 25, 200, 155)
$Checkbox1 = GUICtrlCreateCheckbox("Site Name", 25, 55, 113, 25)
$InputField1 = GUICtrlCreateInput("AInputField1", 25, 85, 121, 21)
GUICtrlSetState(-1, $GUI_DISABLE)
GUICtrlCreateGroup("", -99, -99, 1, 1)
$Group4 = GUICtrlCreateGroup("AGroup4", 220, 25, 200, 155)
$Checkbox2 = GUICtrlCreateCheckbox("Category", 230, 55, 97, 25)
$Combo1 = GUICtrlCreateCombo("ACombo1", 230, 85, 145, 25)
GUICtrlSetData(-1, "Adventure Cruises|Air Combat Schools|Archaeological Tours|ATV Tours|Back Country Skiing|Bicycle Tours & Mountain Biking|Bus Tours & Coach Excursions|Canoeing|Cattle Drives & Working Ranches|Corporate Team Building|Cross Country Skiing|Culinary Vacations|Cultural Tours|Discontinued - National Park Approved Lodges|Dog Sledding Vacations|Dude & Guest Ranches|Eco & Jungle Lodges|Ecotourism|Educational Travel|Family Camps|Fishing Charters|Fishing Guides|Fishing Lodges|Golf Vacations|Heli-Adventures|Heli-Skiing|Hiking & Trekking Tours|Horse Pack Trips & Trail Rides|Horseback Riding Stables|Hot Air Ballooning|Houseboat Rentals|Jet Boat Tours|Kayaking|Motorcycle Tours|Mountain & Rock Climbing|MultiSport Adventures|Outdoor Skills Training|Paintball & Skirmish Adventures|Photography Tours|Private Yacht Charters|Railway & Train Tours|Resorts, Spas & Retreats|Safaris|Scuba Diving Vacations|Snowcat Skiing|Snowmobiling|Soaring & Glider Rides|Surfing Vacations & Camps|Wagon Train Vacations|Walking Tours|Whale Watching Tours|Whitewater Rafting|Wildlife Viewing Tours|Women"&Chr(39)&"s Travel")
GUICtrlSetState(-1, $GUI_DISABLE)
GUICtrlCreateGroup("", -99, -99, 1, 1)
$Group5 = GUICtrlCreateGroup("AGroup5", 220, 200, 200, 155)
$Checkbox5 = GUICtrlCreateCheckbox("Featured", 230, 230, 97, 25)
$Combo4 = GUICtrlCreateCombo("ACombo4", 230, 260, 145, 25)
GUICtrlSetData(-1, "Yes|No")
GUICtrlSetState(-1, $GUI_DISABLE)
GUICtrlCreateGroup("", -99, -99, 1, 1)
$Group6 = GUICtrlCreateGroup("AGroup6", 425, 200, 200, 155)
$Checkbox6 = GUICtrlCreateCheckbox("Active", 435, 230, 97, 25)
$Combo5 = GUICtrlCreateCombo("ACombo4", 435, 260, 145, 25)
GUICtrlSetData(-1, "Yes|No")
GUICtrlSetState(-1, $GUI_DISABLE)
GUICtrlCreateGroup("", -99, -99, 1, 1)
GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###

While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit
        
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;   Site ID   ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
        Case $Checkbox1 
            If BitAnd(GUICtrlRead($Checkbox1),$GUI_CHECKED) Then
                GUICtrlSetState ($InputField1, $GUI_ENABLE)
            ElseIf BitAnd(GUICtrlRead($Checkbox1),$GUI_UNCHECKED) Then
                GUICtrlSetState ($InputField1, $GUI_DISABLE)
            EndIf
            
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;    Category   ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
        Case $Checkbox2
            If BitAnd(GUICtrlRead($Checkbox2),$GUI_CHECKED) Then
                GUICtrlSetState ($Combo1, $GUI_ENABLE)
            ElseIf BitAnd(GUICtrlRead($Checkbox2),$GUI_UNCHECKED) Then
                GUICtrlSetState ($Combo1, $GUI_DISABLE)
            EndIf

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;     City    ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;         
            
        Case $Checkbox3
            If BitAnd(GUICtrlRead($Checkbox3),$GUI_CHECKED) Then
                GUICtrlSetState ($Combo2, $GUI_ENABLE)
            ElseIf BitAnd(GUICtrlRead($Checkbox3),$GUI_UNCHECKED) Then
                GUICtrlSetState ($Combo2, $GUI_DISABLE)
            EndIf
            
        ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;     State    ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;            
        Case $Checkbox4
            If BitAnd(GUICtrlRead($Checkbox4),$GUI_CHECKED) Then
                GUICtrlSetState ($Combo3, $GUI_ENABLE)
            ElseIf BitAnd(GUICtrlRead($Checkbox4),$GUI_UNCHECKED) Then
                GUICtrlSetState ($Combo3, $GUI_DISABLE)
            EndIf
            
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;    Featured    ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
        Case $Checkbox5
            If BitAnd(GUICtrlRead($Checkbox5),$GUI_CHECKED) Then
                GUICtrlSetState ($Combo4, $GUI_ENABLE)
            ElseIf BitAnd(GUICtrlRead($Checkbox5),$GUI_UNCHECKED) Then
                GUICtrlSetState ($Combo4, $GUI_DISABLE)
            EndIf
        
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;    Active    ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
        Case $Checkbox6
            If BitAnd(GUICtrlRead($Checkbox6),$GUI_CHECKED) Then
                GUICtrlSetState ($Combo5, $GUI_ENABLE)
            ElseIf BitAnd(GUICtrlRead($Checkbox6),$GUI_UNCHECKED) Then
                GUICtrlSetState ($Combo5, $GUI_DISABLE)
            EndIf
            
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;    Buttons    ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
        Case $Button1 ; Ok Button
;~          ;We are going to make an array to count the number of Criteria the user has selected
            $aArray_Count = 0

            If BitAnd(GUICtrlRead($Checkbox1),$GUI_CHECKED) Then 
                    ReDim $aArray_Criteria[$aArray_Count+1]
                    $aArray_Criteria[$aArray_Count] = "Site Name = " & "'" & GUICtrlRead($InputField1) & "'"
                    $aArray_Count +=1
            EndIf

            If BitAnd(GUICtrlRead($Checkbox2),$GUI_CHECKED) Then 
                    ReDim $aArray_Criteria[$aArray_Count+1]
                    $aArray_Criteria[$aArray_Count] = "Category = " & "'" & GUICtrlRead($Combo1) & "'"
                    $aArray_Count +=1
            EndIf

            If BitAnd(GUICtrlRead($Checkbox3),$GUI_CHECKED) Then 
                    ReDim $aArray_Criteria[$aArray_Count+1]
                    $aArray_Criteria[$aArray_Count] = "City = " & "'" & GUICtrlRead($Combo2) & "'"
                    
                    $aArray_Count +=1
            EndIf

            If BitAnd(GUICtrlRead($Checkbox4),$GUI_CHECKED) Then 
                    ReDim $aArray_Criteria[$aArray_Count+1]
                    $aArray_Criteria[$aArray_Count] = "State = " & "'" & GUICtrlRead($Combo3) & "'"
                    $aArray_Count +=1
            EndIf
            
            If BitAnd(GUICtrlRead($Checkbox5),$GUI_CHECKED) Then 
                    ReDim $aArray_Criteria[$aArray_Count+1]
                    $aArray_Criteria[$aArray_Count] = "Featured = " & "'" & GUICtrlRead($Combo4) & "'"
                    $aArray_Count +=1

            EndIf

            If BitAnd(GUICtrlRead($Checkbox6),$GUI_CHECKED) Then 
                    ReDim $aArray_Criteria[$aArray_Count+1]
                    $aArray_Criteria[$aArray_Count] = "Active = " & "'" & GUICtrlRead($Combo5) & "'"
                    $aArray_Count +=1
            EndIf               

            Step1 ()
            Step2 ()
            ExitLoop
        
    EndSwitch
WEnd


;~  ;This is the Expression Builder
Func Step1() 
    $s_Tablename = "[Sheet1$]"
    $query = "SELECT * FROM " & $s_Tablename &" WHERE " ;Starting expression

    $i = 0
    While $i < UBound ($aArray_Criteria)
        $query &= $aArray_Criteria[$i]
        $i+=1
        If $i < UBound ($aArray_Criteria) Then
            $query &= " and "
        EndIf
    WEnd
EndFunc

;~  ;This is the Excel Criteria Sorter and Publisher
Func Step2 ()
    Const $adOpenStatic = 3
    Const $adLockOptimistic = 3
    Const $adCmdText = 0x0001 ;  =&H0001
    Global $s_Filename=FileGetShortName(@ScriptDir & "\Excel Example.xls")
    Global $s_Tablename = "[Sheet1$]" ; 

    $objConnection = ObjCreate("ADODB.Connection")
    $objRecordSet = ObjCreate("ADODB.Recordset")

    $objConnection.Open ("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source="&$s_Filename&";" & _
                "Extended Properties=""Excel 8.0;HDR=Yes;"";")

    ;~  ;Grabes the data from Excel, based on the Criteria for the Query
    $objRecordSet.Open ( $query, $objConnection, $adOpenStatic, $adLockOptimistic, $adCmdText) 
                                        

    ;~  ;Prints the Data
    Do 
        ConsoleWrite ($objRecordSet.Fields(0).value &" | "& $objRecordSet.Fields(12).value &" | "& _
            $objRecordSet.Fields(13).value &@CR)
        $objRecordSet.MoveNext()
    Until $objRecordSet.EOF()
EndFunc
_ArrayPermute()_ArrayUnique()Excel.au3 UDF
Link to comment
Share on other sites
litlmike Universalist

litlmike

Posted
  • Author
Posted

@ ptrex

I figured out how to display it in your grid.

Having a hard time figuring out how to read the numbers, and calculate their averages. Any Ideas?

Here is what I have:

#include <GUIConstants.au3>

Global $aArray_Criteria[2]
Global $query
;~ ;Vars
Dim $oMyError
Dim $i_viewer=1
Dim $j_viewer=1

#Region ### START Koda GUI section ### Form=c:\documents and settings\...\my documents\personal\autoit\koda forms\criteriaselector_excel3.kxf
$Form1_1 = GUICreate("AForm1", 770, 598, 191, 115)
$Button1 = GUICtrlCreateButton("OK", 16, 368, 75, 25, 0)
$Button2 = GUICtrlCreateButton("Build Expression for $Final Text", 16, 408, 211, 49, 0)
$Label1 = GUICtrlCreateLabel("$FinalText", 16, 472, 53, 17)
$Button3 = GUICtrlCreateButton("Reset", 240, 408, 105, 49, 0)
$Group3 = GUICtrlCreateGroup("AGroup3", 425, 25, 200, 155)
$Combo2 = GUICtrlCreateCombo("City", 435, 85, 145, 25)
GUICtrlSetData(-1, "West Richland|Oak Hill|Emeryville|Cape Town|Haines|Berkeley|Boston|Woodstock|Bountiful|Isle of Palms|San Francisco|Philadelphia|Madrid|Mount Pleasant|San Francisco|Arlington|Pienza|Fruita|Kahului|New York")
GUICtrlSetState(-1, $GUI_DISABLE)
$Checkbox3 = GUICtrlCreateCheckbox("Use City", 435, 55, 97, 25)
GUICtrlCreateGroup("", -99, -99, 1, 1)
$Group2 = GUICtrlCreateGroup("AGroup2", 15, 200, 200, 155)
$Combo3 = GUICtrlCreateCombo("State", 25, 260, 145, 25)
GUICtrlSetData(-1, "WA|WV|CA|Western Cape|AK|CA|MA|VT|UT|SC|CA|PA|.|SC|CA|MA|Siena|CO|HI|NY")
GUICtrlSetState(-1, $GUI_DISABLE)
$Checkbox4 = GUICtrlCreateCheckbox("Use State", 25, 230, 97, 25)
GUICtrlCreateGroup("", -99, -99, 1, 1)
$Group = GUICtrlCreateGroup("AGroup", 15, 25, 200, 155)
$Checkbox1 = GUICtrlCreateCheckbox("Site Name", 25, 55, 113, 25)
$InputField1 = GUICtrlCreateInput("AInputField1", 25, 85, 121, 21)
GUICtrlSetState(-1, $GUI_DISABLE)
GUICtrlCreateGroup("", -99, -99, 1, 1)
$Group4 = GUICtrlCreateGroup("AGroup4", 220, 25, 200, 155)
$Checkbox2 = GUICtrlCreateCheckbox("Category", 230, 55, 97, 25)
$Combo1 = GUICtrlCreateCombo("ACombo1", 230, 85, 145, 25)
GUICtrlSetData(-1, "Adventure Cruises|Air Combat Schools|Archaeological Tours|ATV Tours|Back Country Skiing|Bicycle Tours & Mountain Biking|Bus Tours & Coach Excursions|Canoeing|Cattle Drives & Working Ranches|Corporate Team Building|Cross Country Skiing|Culinary Vacations|Cultural Tours|Discontinued - National Park Approved Lodges|Dog Sledding Vacations|Dude & Guest Ranches|Eco & Jungle Lodges|Ecotourism|Educational Travel|Family Camps|Fishing Charters|Fishing Guides|Fishing Lodges|Golf Vacations|Heli-Adventures|Heli-Skiing|Hiking & Trekking Tours|Horse Pack Trips & Trail Rides|Horseback Riding Stables|Hot Air Ballooning|Houseboat Rentals|Jet Boat Tours|Kayaking|Motorcycle Tours|Mountain & Rock Climbing|MultiSport Adventures|Outdoor Skills Training|Paintball & Skirmish Adventures|Photography Tours|Private Yacht Charters|Railway & Train Tours|Resorts, Spas & Retreats|Safaris|Scuba Diving Vacations|Snowcat Skiing|Snowmobiling|Soaring & Glider Rides|Surfing Vacations & Camps|Wagon Train Vacations|Walking Tours|Whale Watching Tours|Whitewater Rafting|Wildlife Viewing Tours|Women"&Chr(39)&"s Travel")
GUICtrlSetState(-1, $GUI_DISABLE)
GUICtrlCreateGroup("", -99, -99, 1, 1)
$Group5 = GUICtrlCreateGroup("AGroup5", 220, 200, 200, 155)
$Checkbox5 = GUICtrlCreateCheckbox("Featured", 230, 230, 97, 25)
$Combo4 = GUICtrlCreateCombo("ACombo4", 230, 260, 145, 25)
GUICtrlSetData(-1, "Yes|No")
GUICtrlSetState(-1, $GUI_DISABLE)
GUICtrlCreateGroup("", -99, -99, 1, 1)
$Group6 = GUICtrlCreateGroup("AGroup6", 425, 200, 200, 155)
$Checkbox6 = GUICtrlCreateCheckbox("Active", 435, 230, 97, 25)
$Combo5 = GUICtrlCreateCombo("ACombo4", 435, 260, 145, 25)
GUICtrlSetData(-1, "Yes|No")
GUICtrlSetState(-1, $GUI_DISABLE)
GUICtrlCreateGroup("", -99, -99, 1, 1)
GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###

While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit
        
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;   Site ID   ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
        Case $Checkbox1 
            If BitAnd(GUICtrlRead($Checkbox1),$GUI_CHECKED) Then
                GUICtrlSetState ($InputField1, $GUI_ENABLE)
            ElseIf BitAnd(GUICtrlRead($Checkbox1),$GUI_UNCHECKED) Then
                GUICtrlSetState ($InputField1, $GUI_DISABLE)
            EndIf
            
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;    Category   ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
        Case $Checkbox2
            If BitAnd(GUICtrlRead($Checkbox2),$GUI_CHECKED) Then
                GUICtrlSetState ($Combo1, $GUI_ENABLE)
            ElseIf BitAnd(GUICtrlRead($Checkbox2),$GUI_UNCHECKED) Then
                GUICtrlSetState ($Combo1, $GUI_DISABLE)
            EndIf

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;     City    ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;         
            
        Case $Checkbox3
            If BitAnd(GUICtrlRead($Checkbox3),$GUI_CHECKED) Then
                GUICtrlSetState ($Combo2, $GUI_ENABLE)
            ElseIf BitAnd(GUICtrlRead($Checkbox3),$GUI_UNCHECKED) Then
                GUICtrlSetState ($Combo2, $GUI_DISABLE)
            EndIf
            
        ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;     State    ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;            
        Case $Checkbox4
            If BitAnd(GUICtrlRead($Checkbox4),$GUI_CHECKED) Then
                GUICtrlSetState ($Combo3, $GUI_ENABLE)
            ElseIf BitAnd(GUICtrlRead($Checkbox4),$GUI_UNCHECKED) Then
                GUICtrlSetState ($Combo3, $GUI_DISABLE)
            EndIf
            
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;    Featured    ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
        Case $Checkbox5
            If BitAnd(GUICtrlRead($Checkbox5),$GUI_CHECKED) Then
                GUICtrlSetState ($Combo4, $GUI_ENABLE)
            ElseIf BitAnd(GUICtrlRead($Checkbox5),$GUI_UNCHECKED) Then
                GUICtrlSetState ($Combo4, $GUI_DISABLE)
            EndIf
        
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;    Active    ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
        Case $Checkbox6
            If BitAnd(GUICtrlRead($Checkbox6),$GUI_CHECKED) Then
                GUICtrlSetState ($Combo5, $GUI_ENABLE)
            ElseIf BitAnd(GUICtrlRead($Checkbox6),$GUI_UNCHECKED) Then
                GUICtrlSetState ($Combo5, $GUI_DISABLE)
            EndIf
            
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;    Buttons    ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
        Case $Button1 ; Ok Button
;~          ;We are going to make an array to count the number of Criteria the user has selected
            $aArray_Count = 0

            If BitAnd(GUICtrlRead($Checkbox1),$GUI_CHECKED) Then 
                    ReDim $aArray_Criteria[$aArray_Count+1]
                    $aArray_Criteria[$aArray_Count] = "Site Name = " & "'" & GUICtrlRead($InputField1) & "'"
                    $aArray_Count +=1
            EndIf

            If BitAnd(GUICtrlRead($Checkbox2),$GUI_CHECKED) Then 
                    ReDim $aArray_Criteria[$aArray_Count+1]
                    $aArray_Criteria[$aArray_Count] = "Category = " & "'" & GUICtrlRead($Combo1) & "'"
                    $aArray_Count +=1
            EndIf

            If BitAnd(GUICtrlRead($Checkbox3),$GUI_CHECKED) Then 
                    ReDim $aArray_Criteria[$aArray_Count+1]
                    $aArray_Criteria[$aArray_Count] = "City = " & "'" & GUICtrlRead($Combo2) & "'"
                    
                    $aArray_Count +=1
            EndIf

            If BitAnd(GUICtrlRead($Checkbox4),$GUI_CHECKED) Then 
                    ReDim $aArray_Criteria[$aArray_Count+1]
                    $aArray_Criteria[$aArray_Count] = "State = " & "'" & GUICtrlRead($Combo3) & "'"
                    $aArray_Count +=1
            EndIf
            
            If BitAnd(GUICtrlRead($Checkbox5),$GUI_CHECKED) Then 
                    ReDim $aArray_Criteria[$aArray_Count+1]
                    $aArray_Criteria[$aArray_Count] = "Featured = " & "'" & GUICtrlRead($Combo4) & "'"
                    $aArray_Count +=1

            EndIf

            If BitAnd(GUICtrlRead($Checkbox6),$GUI_CHECKED) Then 
                    ReDim $aArray_Criteria[$aArray_Count+1]
                    $aArray_Criteria[$aArray_Count] = "Active = " & "'" & GUICtrlRead($Combo5) & "'"
                    $aArray_Count +=1
            EndIf               

            Step1 ()
            Step2 ()
            ExitLoop
        
    EndSwitch
WEnd


;~  ;This is the Expression Builder
Func Step1() 
    $s_Tablename = "[Sheet1$]"
    $query = "SELECT * FROM " & $s_Tablename &" WHERE " ;Starting expression

    $i = 0
    While $i < UBound ($aArray_Criteria)
        $query &= $aArray_Criteria[$i]
        $i+=1
        If $i < UBound ($aArray_Criteria) Then
            $query &= " and "
        EndIf
    WEnd
EndFunc

;~  ;This is the Excel Criteria Sorter and Publisher
Func Step2 ()
    Const $adOpenStatic = 3
    Const $adLockOptimistic = 3
    Const $adCmdText = 0x0001 ;  =&H0001
    Global $s_Filename=FileGetShortName(@ScriptDir & "\Excel Example.xls")
    Global $s_Tablename = "[Sheet1$]" ; 

    $objConnection = ObjCreate("ADODB.Connection")
    $objRecordSet = ObjCreate("ADODB.Recordset")

    $objConnection.Open ("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source="&$s_Filename&";" & _
                "Extended Properties=""Excel 8.0;HDR=Yes;"";")

    ;~  ;Grabes the data from Excel, based on the Criteria for the Query
    $objRecordSet.Open ( $query, $objConnection, $adOpenStatic, $adLockOptimistic, $adCmdText) 
                                        

    ;~  ;Prints the Data
    
    
;~ ;Vars
    
;~  Declare objects
$oExcel = ObjCreate("OWC10.spreadsheet"); Default to Office XP

If not IsObj($oExcel) Then
    $oExcel = ObjCreate("OWC11.spreadsheet"); Office 2003    
EndIf
IF not IsObj($oExcel) Then
    $oExcel = ObjCreate("OWC00.spreadsheet"); Office 2000
EndIf
    If IsObj($oExcel) Then
        with $oExcel
        ;.Worksheets ("Sheet1").Activate
        ;.activesheet.range ("A1:B10").value = "TEST INFO"
                
;~              ;Name the First Cells Description
                .cells($i_viewer,$j_viewer) = "Site Name"
                .cells($i_viewer,$j_viewer+1) = "Average Yearly Traffic"
                .cells($i_viewer,$j_viewer+2) = "Average Yearly Leads"
                $i_viewer+=1

;~          ; Fill the cell values with the Data from the ADO RecordSet
            Do 
                .cells($i_viewer,$j_viewer) = $objRecordSet.Fields(0).value
                .cells($i_viewer,$j_viewer+1) = $objRecordSet.Fields(12).value
                .cells($i_viewer,$j_viewer+2) = $objRecordSet.Fields(13).value
                $objRecordSet.MoveNext()
                $i_viewer+=1
            Until $objRecordSet.EOF()
                MsgBox (0,"", "TEST",2)

;~          ; Try to Calc the Average of the Data showing
            Dim $aArray_Averages[$i_viewer]
            $i_average = 0
            $i_viewer = 0
            $j_viewer = 0
;~          $i
            
            While $i_average < $i_viewer
                $aArray_Averages[$i_average] = .cells($i_viewer,$j_viewer)
                MsgBox (0,"", $aArray_Averages[$i_average],2)
                $i_average +=1
            WEnd
            
;~ $i_aArray_Averages[$i_aArray_Averages] = .cells($i_viewer,$j_viewer)
                
                
;~              .cells($i_viewer,$j_viewer) = $objRecordSet.Fields(0).value
;~              .cells($i_viewer,$j_viewer+1) = $objRecordSet.Fields(12).value
;~              .cells($i_viewer,$j_viewer+2) = $objRecordSet.Fields(13).value
;~              $objRecordSet.MoveNext()
;~              $i_viewer+=1
;~          Until $objRecordSet.EOF()

;~              While .cells($i_viewer,$j_viewer+3)
;~                  .cells($i_viewer,$j_viewer+3) = .cells.Range $objRecordSet.Fields(0).value
            
            
        EndWith
    Else
       MsgBox(0,"Reply","Not an Object",4)
   EndIf


   
;Main Gui
GuiCreate("Excel Object", 802, 590,(@DesktopWidth-802)/2, (@DesktopHeight-590)/2 , $WS_OVERLAPPEDWINDOW + $WS_VISIBLE + $WS_CLIPSIBLINGS)

$GUI_ActiveX = GUICtrlCreateObj ($oExcel, 10, 10 , 780 , 550)
GUICtrlSetStyle ( $GUI_ActiveX,  $WS_VISIBLE )
GUICtrlSetResizing ($GUI_ActiveX,$GUI_DOCKAUTO)    ; Auto Resize Object
    
GuiSetState()
While 1
    $msg = GuiGetMsg()
    Select
    Case $msg = $GUI_EVENT_CLOSE
        ExitLoop
    EndSelect
WEnd
Exit

EndFunc


;This is Sven P's custom error handler
Func MyErrFunc()
  $HexNumber=hex($oMyError.number,8)
  Msgbox(0,"AutoItCOM Test","We intercepted a COM Error !"       & @CRLF  & @CRLF & _
             "err.description is: "    & @TAB & $oMyError.description    & @CRLF & _
             "err.windescription:"     & @TAB & $oMyError.windescription & @CRLF & _
             "err.number is: "         & @TAB & $HexNumber              & @CRLF & _
             "err.lastdllerror is: "   & @TAB & $oMyError.lastdllerror   & @CRLF & _
             "err.scriptline is: "     & @TAB & $oMyError.scriptline     & @CRLF & _
             "err.source is: "         & @TAB & $oMyError.source         & @CRLF & _
             "err.helpfile is: "       & @TAB & $oMyError.helpfile       & @CRLF & _
             "err.helpcontext is: "    & @TAB & $oMyError.helpcontext _
            )
  SetError(1) ; to check for after this function returns
Endfunc
_ArrayPermute()_ArrayUnique()Excel.au3 UDF
Link to comment
Share on other sites
randallc Universalist

randallc

Posted
Posted

Hi,

glad it works;

Here's one way (many others!)

While Not $objRecordSet.EOF ()
                .cells ($i_viewer, $j_viewer) = $objRecordSet.Fields (0).value
                .cells ($i_viewer, $j_viewer + 1) = $objRecordSet.Fields (12).value
                $aArray_sum1 += $objRecordSet.Fields (12).value
                .cells ($i_viewer, $j_viewer + 2) = $objRecordSet.Fields (13).value
                $aArray_sum2 += $objRecordSet.Fields (13).value
                $objRecordSet.MoveNext ()
                $i_viewer += 1
            WEnd
                .cells ($i_viewer, $j_viewer) = "Avge"
                .cells ($i_viewer, $j_viewer + 1) =$aArray_sum1/($i_viewer-2)
                .cells ($i_viewer, $j_viewer + 2) = $aArray_sum2/($i_viewer-2)
            MsgBox(0, "", "TEST", 2)
Randall
ExcelCOM... AccessCom.. Word2... FileListToArrayNew...SearchMiner... Regexps...SQL...Explorer...Array2D.. _GUIListView...array problem...APITailRW
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share
Go to topic listing

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...