Skip to main content


Showing posts from November, 2012

Read, Write Excel - QTP, VbScript

Lot of members are facing some errors whenever trying to connect and read/write values from excel or other databases. Here is some code snippet which will easy the task for you...

To Get Data:
Function GetData(strFilePath, strSQL)
 Dim objConn, objRecord, strConnectionString
 Set objConn = CreateObject("ADODB.Connection")
 Set objRecord = CreateObject("ADODB.RecordSet")

 strConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFilePath & ";Excel 12.0;HDR=YES;READONLY=1;IMEX=1;"

 objConn.Open strConnectionString
 objRecord.Open strSQL, objConn
 msgbox objRecord(0).Value
End Function

Points to take care:
1. Always use readonly=1 to force read-only mode while reading.
2.Use IMEX value based on your requirement.
IMEX=1 - It will convert all data types to text and return.
IMEX=2 - It will return the data as it is.

To Write Data:
Function UpdateData(strFilePath, strSQL)
 Dim objConn, strConnectionString
 Set objConn = CreateObjec…