Highlighted
Absent Member.
Absent Member.
746 views

Facing issue in Data Sheet connection Establishment

I am Facing issue in Data Sheet connection Establishment. Please help !!
Below code i have and attached screeenshot for error receiving.       

''Connect with datasheet
        Set l_objDataRec = fConnectDatatSheet(l_strDataSheetFilePath,"Datasheet",l_strTestPlanName,l_intIterationNo)

'Function Name:            fConnectDatatSheet
'Function description:     Data Sheet connection Establishment

Public Function fConnectDatatSheet(l_strFilePath,l_strSheetName,l_strScriptName,ByVal l_intIterationNo)    
    Dim l_objrec
    Set l_objconn = CreateObject("ADODB.connection")
    Set l_objrec = CreateObject("ADODB.Recordset")
    
    If Instr(UCASE(l_strFilePath),".XLSX")>0 Then
        l_objconn.open "provider=microsoft.ace.oledb.12.0;"&"data source="& l_strFilePath &";" &"Extended properties=""Excel 12.0 Xml;HDR=YES;IMEX=2"";"
    Else
        l_objconn.open "provider=microsoft.jet.oledb.4.0;"&"data source="& l_strFilePath &";" &"Extended properties=""Excel 8.0;HDR=YES;IMEX=2"";"
    End If
    l_objrec.Open "Select * from " & "[" & l_strSheetName &"$]",l_objconn
    ScriptNameForSQL = Trim(l_objrec(0).Name)
    IterationNameForSQL = Trim(l_objrec(1).Name)
        
    l_IterationNo=UCase(TypeName(l_objrec(1).Value))
    l_objrec.Close
    
    If  l_IterationNo="STRING" Then
        l_intIterationNo="'" &  l_intIterationNo & "'"
    End If

    If l_intIterationNo <> "" Then
        l_strSQL = "Select * from [" & l_strSheetName &"$]  Where " & ScriptNameForSQL & " = '" & l_strScriptName & "' And " & IterationNameForSQL & " = " & l_intIterationNo 
    Else    
        l_strSQL = "Select * from " & "[" & l_strSheetName &"$]  Where " & ScriptNameForSQL & " ='" & l_strScriptName &"'"
    End If   
    
    l_objrec.Open l_strSQL,l_objconn
    
    If Err.Number <> 0 Then
        Reporter.ReportEvent micFail,"Verify Opening Data Sheet","Failed. Due to " & "'" & Err.Description & "'"
        Err.Clear
        ExitTestIteration
    End If    
    fConnectDatatSheet = l_objrec    
End Function

0 Likes
2 Replies
Highlighted
Absent Member.
Absent Member.

Is "Datasheet" the name of your excel workbook ?


Accept as solution/Give Kudos to the author if the piece of information answered/helped you to solve your problem.
All the thoughts here are mine not related to my employer nor anyone.
0 Likes
Highlighted
Absent Member.
Absent Member.

Yes, "Datasheet" is name of sheet.

My purpose is to connect my Keyword driven automation framework to Datasheet.

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.