Getting inspiration from
ADO COM - Database Query and especially from
ADODB Excel Database Query [AHK_L], I wrote a function to interrogate an Excel database with ADO, using AHK_L.
Those who want to improve this function are welcome. For example, the result is returned in the form of a string which can be used with StringSplit to get the results. Anyone who want to modify the function to return an associative array is welcome.
I don't know so well objects and associatives arrays, nor their usage, so I used an old (but well known) solution...
The Excel file must be organised as a database, with each column representing a field, and each line a record.
Now the function :
Entry parameters :
GXD_Base : the workbook - the Excel file (with full path)
GXD_Sheet : the name of the (Work)Sheet of the Workbook on which we want to work, not followed by "$", which is added by the function itself.
GXD_ColsToGet : Column(s) to get
The default value is the "*" character (which means "get all the columns/fields of the base "). You can give a list of columns names, separated by commas.
If the list end with a comma, an error is genrated by SQL, but the function check if it is ok and if not, correct the entry.
GXD_ColToVerif1 and
GXD_ColToVerif2 : Column (field) in which we want to check the data.
GXD_Filtre : SQL statement indicating the operation that we want to do (Example : BETWEEN, LIKE, =, ...)
GXD_ValToComp1 and
GXD_ValToComp2 : Values to compare. If the value is alpha or contains characters specifics to the LIKE Statement (%-_![]), the function will surround it with two "'". If you have an alphanumeric value (ex : blabla22) or if the values are numeric but specified as text in Excel (ex : 0652), you must to surround it by "'" characters yourself ("'blabal22'" or "'0652'").
GXD_Link : SQL Statement allowing to link two values or statements in an SQL request (AND, OR). Examples :
BETWEEN 20 AND 50 or
Region = 'Quebec' AND Rep = 'Jones'
Return value :
GXD_Records : Recovered datas. Each value of a field of a record recovered is separated from the next field by a semicolon. Each record is separated from the next by the character "|".
It is possible to recover values by using StringSplit to separate records, and then to separate each field of a record.
To test the function, I used the SampleData.xls of
ADODB Excel Database Query [AHK_L], which must be in the directory in which you put the ahk file.
Here is the function :
Code:
GXD_Base := A_ScriptDir . "\SampleData.xls"
GXD_Sheet := "Sheet1"
;clipboard := GetXLData(GXD_Base, GXD_Sheet,, "Units", "BETWEEN", 20, "AND",,, 50)
;clipboard := GetXLData(GXD_Base, GXD_Sheet, "Region,Rep,Unit Cost,", "Units", "BETWEEN", "20", "AND",,, 60)
;clipboard := GetXLData(GXD_Base, GXD_Sheet,, "Units", "BETWEEN", 30, "AND",,, 50)
;clipboard := GetXLData(GXD_Base, GXD_Sheet,, "Units", "IN", "(50, 60, 90)")
;clipboard := GetXLData(GXD_Base, GXD_Sheet,, "Units", ">", 90)
;clipboard := GetXLData(GXD_Base, GXD_Sheet, "Region, Rep,Unit Cost,", "Units", "BETWEEN", 30, "AND",,, 60)
;clipboard := GetXLData(GXD_Base, GXD_Sheet, "Region, Rep,Unit Cost,", "Region", "=", "Quebec", "AND", "Rep", "=", "Jones")
;clipboard := GetXLData(GXD_Base, GXD_Sheet, "Region, Rep,Unit Cost,", "Units", "LIKE", "3%")
GetXLData(GXD_Base, GXD_Sheet, GXD_ColsToGet = "*", GXD_ColToVerif1 = "", GXD_Filtre1 = "", GXD_ValToComp1 = "", GXD_Link = "", GXD_ColToVerif2 = "", GXD_Filtre2 = "", GXD_ValToComp2 = "")
{
adOpenStatic = 3
adLockOptimistic = 3
adCmdText = 1
adApproxPosition := 0x4000
GXD_Sheet := GXD_Sheet . "$"
If (GXD_ColsToGet != "*")
{
GXD_LColsToGet := StrLen(GXD_ColsToGet)
If SubStr(GXD_ColsToGet, StrLen(GXD_ColsToGet), 1) = ","
GXD_ColsToGet := SubStr(GXD_ColsToGet, 1, StrLen(GXD_ColsToGet) - 1)
If InStr(GXD_ColsToGet, ",")
{
If InStr(GXD_ColsToGet, ", ")
StringReplace, GXD_ColsToGet, GXD_ColsToGet, `,%A_SPACE%, `,, All
StringSplit, GXD_ColsContent, GXD_ColsToGet, `,]
Loop, %GXD_ColsContent0%
{
If InStr(GXD_ColsContent%A_Index%, A_Space)
GXD_ColsContent%A_Index% := "``" . GXD_ColsContent%A_Index% . "``"
GXD_ColsContent := GXD_ColsContent . GXD_ColsContent%A_Index% . ", "
}
}
GXD_ColsToGet := GXD_ColsContent
If SubStr(GXD_ColsToGet, StrLen(GXD_ColsToGet)-1, 2) = ", "
GXD_ColsToGet := SubStr(GXD_ColsToGet, 1, StrLen(GXD_ColsToGet) - 2)
}
Loop, 2
{
If GXD_ColToVerif%A_Index%
{
If InStr(GXD_ColToVerif%A_Index%, A_Space)
GXD_ColToVerif%A_Index% := "``" . GXD_ColToVerif%A_Index% . "``"
}
If GXD_ValToComp%A_Index%
{
If var is not number
GXD_ValToComp%A_Index% := "'" . GXD_ValToComp%A_Index% . "'"
}
}
If (GXD_ColToVerif1 and GXD_ValToComp2 and GXD_ColToVerif2 and GXD_Filtre2)
Request := "SELECT " . GXD_ColsToGet . " FROM [" . GXD_Sheet . "]" . " WHERE " . GXD_ColToVerif1 . " " . GXD_Filtre1 . " " . GXD_ValToComp1 . " " . GXD_Link . " " . GXD_ColToVerif2 . " " . GXD_Filtre2 . " " . GXD_ValToComp2
If (GXD_ColToVerif1 and GXD_ValToComp2 and !GXD_ColToVerif2 and !GXD_Filtre2)
Request := "SELECT " . GXD_ColsToGet . " FROM [" . GXD_Sheet . "]" . " WHERE " . GXD_ColToVerif1 . " " . GXD_Filtre1 . " " . GXD_ValToComp1 . " " . GXD_Link . " " . GXD_ValToComp2
If (GXD_ColToVerif1 and !GXD_ValToComp2 and !GXD_ColToVerif2 and !GXD_Filtre2)
Request := "SELECT " . GXD_ColsToGet . " FROM [" . GXD_Sheet . "]" . " WHERE " . GXD_ColToVerif1 . " " . GXD_Filtre1 . " " . GXD_ValToComp1
If (!GXD_ColToVerif1 and !GXD_ValToComp2 and !GXD_ColToVerif2 and !GXD_Filtre2)
Request := "SELECT " . GXD_ColsToGet . " FROM [" . GXD_Sheet . "]"
objConnection := ComObjCreate("ADODB.Connection")
objRecordSet := ComObjCreate("ADODB.Recordset")
objConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" . GXD_Base . ";Extended Properties=""Excel 8.0;HDR=Yes;"";")
objRecordSet.Open(Request, objConnection, adOpenStatic, adLockOptimistic, adCmdText)
GXD_NumRec := objRecordSet.RecordCount
GXD_pFields := objRecordSet.Fields
GXD_NumFiels := GXD_pFields.Count
Loop %GXD_NumRec%
{
Loop %GXD_NumFiels%
{
If A_Index < %GXD_NumFiels%
GXD_Records := GXD_Records . GXD_pFields.Item(A_Index-1).Value . ";"
Else
GXD_Records := GXD_Records . GXD_pFields.Item(A_Index-1).Value
}
If A_Index < %GXD_NumRec%
GXD_Records := GXD_Records . "|"
objRecordSet.MoveNext
}
objRecordSet.Close()
objConnection.Close()
objRecordSet := ""
objConnection := ""
Return GXD_Records
}
Of course, to make a test, you delete a semicolon before a "clipboard := " that you choose, you save the script and you run it.
Again if you want to improve the function, to make comments or suggestions, you are welcome.
I hope you will find this function usefull.
Login
Register
FAQ
Search
