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
 objConn.Close
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 = CreateObject("ADODB.Connection")

 strConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFilePath & ";Excel 12.0;HDR=YES;READONLY=0;IMEX=0;"
 objConn.Open strConnectionString
 objConn.Execute strSQL
 objConn.Close
End Function

Points to take care:
1. Always use readonly=0 to force read-only mode to OFF while writing.
2.Here IMEX=1 will restrict the write access. So you should not use IMEX property. Otherwise use IMEX=0.

Some common Errors:
1. Cannot find installable ISAM
if the corresponding version dll is not found, then this error will come.Sometimes this below will do trick. If you mentioned your connection string like,
Extended Properties=""Excel 12.0"", then remove it and simply mention "Excel 12.0" as version. Otherwsie mention the max version available/installed on your system.
2.Should be updatable Query
For writing data, if you ever used IMEX property as 1/2 - this error will come as it blocks the write access. Remove the IMEX part, otherwsie set as IMEX=0.
3. One or more required parameters are incorrect
If error contains term like one or more required parameters, then you should re-visit your query. Query should be the problem.