Skip to main content

ADODB Connection For Excel Validation


strFileName = "C:\Jay\Sample.xls"
strQuery =  "SELECT Col1,Col2 FROM SheetName$  WHERE Col1 = 1 AND Col2 = 2"

Set oCurRowData = GetContentFromDB (strFileName, strQuery)
If Not oCurRowData.EOF  Then    
    iRecordCnt = oCurRowData.RecordCount   
        Data = oCurRowData.Fields(i).Value
End If

'DB Connection with Excel Driver
Function GetContentFromDB(strFileName, strSQLStatement)
     Dim objCon, objRecordSet
     Set objCon = CreateObject("ADODB.Connection")
     objCon.Open "DRIVER={Microsoft Excel Driver (*.xls)};DBQ="&strFileName & ";Readonly=True"
  
     If Err.Number <> 0 Then
        Reporter.ReportEvent micFail,"Create Connection", "[Connection] Error has occured. Error : " & Err.Description
        Set GetContentFromDB = Nothing
        Exit Function
     End If    
  
     Set objRecordSet = CreateObject("ADODB.Recordset")
     objRecordSet.CursorLocation=3
     objRecordSet.Open strSQLStatement, objCon,1, 3
     Set GetContentFromDB  = objRecordSet
  
     If Err.Number <>0 Then
        Reporter.ReportEvent micFail,"Open Recordset", "Error has occured.Error Code : " & Err.Description
        Set GetContentFromDB = Nothing
       Exit Function
     End If
  
     Set objRecordSet.ActiveConnection = Nothing
     objCon.Close
     Set objCon = Nothing
End Function

Comments

Popular posts from this blog

Convert JSON to XML using QTP/UFT/VBScript

Sample Code : Dim strPage,strJSON,objIE strPage = "C:\Jay\JLoader.html" Set objIE = CreateObject("InternetExplorer.Application") objIE.Visible = True objIE.Navigate2 strPage While objIE.Busy : Wend strJSON = "{""FirstName"":""Jay"", ""LastName"":""Krishna""}" Set objWin = objIE.document.parentWindow objWin.execScript "var jsonStr2XML = function(strJSON) { return json2xml(JSON.parse(strJSON));};" Msgbox  oWin.jsonStr2XML(strJSON) objIE.Quit In Detail: Converting The most popular data interchange format JSON(JavaScript Object Notation) to XML using QTP/UFT. Parsing JSON in UFT could be a challenge so we will use JavaScript in UFT to make it perfect. SO We need :              Java Script API  - To Convert JSON to XML                         JavaScript Files :  ...

Customised HTML Result Report for UFT/QTP with ScreenShots in HP ALM TestSet attached.

On   Error   Resume   Next '======================================================================================================================================== 'Module Name            :    GenerateCustomReport.qfl ' Author                    :    Jay 'Date Created            :     13/11/2014 'Description                :     The library contains various functions that are used to generate a Custom HTML report '======================================================================================================================================== 'Project/Test Specific Variables ...

How To open Password Protected Excel sheets using Vb Script

Function UnprotectXL(filePath,fileName,pwd,writeresPwd)    Set objExcel=CreateObject(“Excel.Application”)    objExcel.Visible=false    testData=filePath&”\”&fileName    Set oWorkbook=objExcel.Workbooks    Set myWkbook=objExcel.Workbooks.open (testData,0,False,5,pwd,writeresPwd)    objExcel.DisplayAlerts=False    oWorkbook(fileName).Activate    For Each w in objExcel.Workbooks         w.SaveAs testData,,”",”"          Next    objExcel.Workbooks.Close    objExcel.Quit    Set oWorkbook=Nothing    Set objExcel=Nothing   End Function Function ProtectXL(filePath,fileName,pwd,writeresPwd)      On Error Resume Next      Set objExcel=CreateObject(“Excel.Application”)      objExcel.Visible=False      testData=filePath...

Compare Two Excel sheets cell by cell Using Vb Script

'This code will open two excel sheet and compare each sheet cell by cell, if any changes there in cells , it will highlight the cells in red color  in the first sheet. Set objExcel = CreateObject(“Excel.Application”) objExcel.Visible = True Set objWorkbook1= objExcel.Workbooks.Open(“C:\Jaykrishna\Docs1.xls”) Set objWorkbook2= objExcel.Workbooks.Open(“C:\Jaykrishna\Docs2.xls”) Set objWorksheet1= objWorkbook1.Worksheets(1) Set objWorksheet2= objWorkbook2.Worksheets(1)    For Each cell In objWorksheet1.UsedRange        If cell.Value <> objWorksheet2.Range(cell.Address).Value Then            cell.Interior.ColorIndex = 3′Highlights in red color if any changes in cells        Else            cell.Interior.ColorIndex = 0        End If    Next set objExcel=nothing

How to Read or Select Context Menu or Right Click Menu using QTP.

Select The Item in Right Click Menu or Context Menu: Window("sampleWindow").WinMenu("MenuObjType:=1).Select"File;New" Here MenuObjtype can be 1 r 2 r 3 .......n Check wether the Item is Exist or Not: If Window("sampleWindow").WinMenu("MenuObjType:=1).GetItemProperty("1","Exist") Then   Msgbox"Exist" Else  Msgbox"Does Not Exist" End If                                         Or If Window("sampleWindow").WinMenu("MenuObjType:=1).GetItemProperty("File","Exist") Then   Msgbox"Exist" Else  Msgbox"Does Not Exist" End If Get the Items in Context Menu: For i = 1 to 10 Print  Window("sampleWindow").WinMenu("MenuObjType:=" & i).GetItemProperty("1","Label") Then Next