Generic Function to read the data from Excel/sql database using ADODB connection

[supputuri]

Hi guys,

this function will increase the performance of your script as we are using the ADODB connection to retrieve the values from excel (treated as DB).

Code:

'******************************************************************************************************************************** ' Function Name: DataReader() ' Purpose: Returns query output from either SQl database or Excel spreadsheet using ADODB connection ' ' Inputs: DataPath as string ' Table as string ' OutptValues as string ' ConditionNm as string ' ConditionValue as string ' Return Values: Semi-colon seperated result set as string ' Author: Sridhar Upputuri ' Creation Date: 5/25/2012 ' Requirement Files: ' Add-in: Web, .NET ' Modification History ' Date Author Change ' '******************************************************************************************************************************** Public Function DataReader(arrVar) DataPath = Valid(arrVar,0) Table = Valid(arrVar,1) OutputValues = Valid(arrVar,2) ConditionNm = Valid(arrVar,3) ConditionValue = Valid(arrVar,4) DataType = Valid(arrVar,4) Dim rs, rsC, cnn, firstCol Dim myPath Dim strClose Dim fld Dim fldCat Dim intCount Dim DataFormat, boolNegative Set cnn = CreateObject("ADODB.Connection") Set rs = CreateObject("ADODB.Recordset") Set rsC = CreateObject("ADODB.Recordset") 'Check if it is a Excel file path or SQL instance and specify the connection string appropriately If InStr(DataPath,".xls") > 0 Then 'It is an Excel file path cnn.Open("Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & DataPath & ";ReadOnly=1; Extended Properties='IMEX=1'") strClose = "$]" Else 'It is a SQL instance cnn.Open("Provider=SQLOLEDB;Integrated Security=SSPI; PersistSecurity Info=False; Initial Catalog=" &DataTable("Database",dtGlobalSheet) & "; Data Source=" & DataPath) strClose = "]" End If 'Construct the query string If OutputValues = "" Then OutputValues = "*" If Not ConditionNm = "" Then arrConditionNm = Split(ConditionNm, ";") arrConditionValue = Split(ConditionValue, ";") intLoop = 0 rsC.Open "SELECT * FROM [" & Table & strClose, cnn For each strConditionNm in arrConditionNm boolNegative = False ' Identify negative condition If LCase(Left(strConditionNm,4)) = "not " Then boolNegative = True strConditionNm = Trim(Mid(strConditionNm, 5)) End If 'Find the dataType for the Condition name For intCount = 0 To rsC.Fields.Count -1 If LCase(rsC.Fields(intcount).name) = LCase(strConditionNm) Then DataFormat= CStr(rsC.Fields(intCount).Type) If DataFormat = "200" Or DataFormat = "130" Or DataFormat = "202"then 'The Dataformat is not varChar DataType = "varChar" End If Exit For End If Next If boolNegative Then strConditionNm = "Not " & strConditionNm End If If DataType = "varChar" then If intLoop = 0 Then strCondition = strConditionNm & "='" & arrConditionValue(intLoop) & "'" Else strCondition = strCondition & " AND " & strConditionNm & "='" & arrConditionValue(intLoop) & "'" End If Else If intLoop = 0 Then strCondition = strConditionNm & "=" & arrConditionValue(intLoop) Else strCondition = strCondition & " AND " & strConditionNm & "=" & arrConditionValue(intLoop) End If End If DataType = "" intLoop = intLoop + 1 Next 'close the first recordset rsC.Close rs.Open "SELECT " & OutputValues & " FROM [" & Table & strClose & " WHERE " & strCondition, cnn Else rs.Open "SELECT " & OutputValues & " FROM [" & Table & strClose, cnn End If firstCol = true Do While Not rs.EOF For Each fld In rs.Fields If firstCol Then fldCat = fldCat & fld firstCol = false Else fldCat = fldCat & ";" & fld End If Next DataReader = fldCat Exit Do ' Exit after first record Loop 'clean up variables rs.Close cnn.Close Set rs = Nothing Set rsC = Nothing Set cnn = Nothing End Function Function Valid(arrayVar,arrayIndex) If ubound(arrayVar)<arrayIndex or lbound(arrayVar)>arrayIndex Then Valid="" Else Valid=arrayVar(arrayIndex) End If End Function

'Create an excel file with name as "Test" and name the first sheet as "Table1". Then add below columns
1) UserName
2) Environment
3) PWD
4) ActiveUser

Now fill the dummy data in the file and make sure that you have "Sridhar" as one of the username in the data.
And now try to execute the below LOC.


Function Call:

Code:

myoutput = DataReader(Array("C:\Test","Table1","PWD,ActiveUser,Environment","UserName","Sridhar")) msgbox myoutput

Please let me know if any body have issues using this function or have any suggestions.