Skip to main content

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.

Comments

  1. Thanks for sharing great information in your blog. Got to learn new things from your Blog . It was very nice blog to learn about Selenium

    ReplyDelete

Post a Comment

Popular posts from this blog

QTP - Object Identification - Prerequesties

While starting a new project or proof of concept or after new QTP installation, we would face the issue in identifying objects as QTP built objects like WinList, WebEdit, etc.; instead everything will be identified as WinObject.

Here are some steps to ensure you did the environment setup RIGHT before start automating.
Did you enabled required Add-ins only while launchingDid you selected "Record >> Record and Run Settings" - This should be based on what type of application you are working with
Windows app - Windows >> Record and run test on any windows applications. Web app - Web >> Record and run test on any open browser.
Did you tried to launch UFT/QTP and then AUT.If its web, ensure browser zoom level set to 100%Try disabling the protected mode in IE for web.Launch UFT and AUT using RUN AS ADMINISTRATORAlso see the Object Identification Methods if you are interested.

Change IE Browser ZOOM settings

Lot of UI automation testers could have faced this problem as we could change the zoom settings while operating manually for our convenience and forgot to reset to 100%.
But our QTP and some other related tools would operate the browser perfectly if browser zoom is 100%. So wee need to change the zoom before start to run the scripts. Its better to have a code snippet in our framework to change this zoom setting right?
Here we go...

1. We can simply change the Registry values before Invoking IE
Function ChangeRegistry Dim objShell Set objShell = CreateObject("WScript.Shell")  objShell.RegWrite "HKEY_CURRENT_USER\Software\Microsoft\Internet Explorer\Zoom\ZoomFactor", "100000", "REG_DWORD" Set objShell = Nothing EndFunction This option is very useful. But in real time, lot of customers could have restricted write access to windows registry. So we can try other options.

2. Use IE COM object and Change Settings
Function ChangeIEZoom Dim intZoomLevel, objIE  intZoo…

QTP - Common error messages

Always I am seeing  many of my friends asking for help if any error occurs while running script in QTP without googling for help. How much troubleshooting you guys are doing will directly reflect your knowledge on the tool. I agree, no one knows each and every error occurs in the software. But we can improve our knowledge by googling and doing workarounds...
Whenever the question "How much level you know QTP?" arrows me, always I am comfortable with the answer "below 10%". But after answered, my mind will have an inner thought like, What are the things I have to learn to fulfill the answer I have told because I never used lot of features in QTP.

Here I am listing out some of the errors, situations and what might be the original issue things etc. I hope this will give you some ideas to improve your debug ability and to solve your errors. Kindly correct me If I am wrong in any point.

Type mismatch:
- Mostly doing operations with different data types without doing type co…