Micro Focus QTP (UFT) Forums
Storing data in excel sheet - Printable Version

+- Micro Focus QTP (UFT) Forums (https://www.learnqtp.com/forums)
+-- Forum: Micro Focus UFT (earlier known as QTP) (https://www.learnqtp.com/forums/Forum-Micro-Focus-UFT-earlier-known-as-QTP)
+--- Forum: VB Scripting/Descriptive Programming (https://www.learnqtp.com/forums/Forum-VB-Scripting-Descriptive-Programming)
+--- Thread: Storing data in excel sheet (/Thread-Storing-data-in-excel-sheet)

Storing data in excel sheet - linhke - 12-10-2009

Hi all,

I have an excel sheet with structure:

KEY USER_NAME PASS_WORD
LOG2 niss niss

I try to get the USER_NAME value with key LOG2 by SELECT query and it done. When i set randomize it (e.g: niss1234), i want to store this new variable value to excel sheet by UPDATE query.
Can anybody help me for that?

Thanks and BRs,

RE: Storing data in excel sheet - Saket - 12-11-2009

If you can tell us how you are connecting to excel, that will be helpful for us to help you.
Paste your lines of code.

I guess you are using ADODB to connect to excel , if so try the code below
Code:
Dim cn  
Set cn = CreateObject("ADODB.Connection")

cn.Open "Driver={Microsoft Excel Driver (*.xls)};DBQ=D:\My QTP\ExcelADO\Test.xls; ReadOnly=False;"
Set rs = cn.Execute ("update [sheet1$] set USER_NAME = 'niss1234' where KEY = 'LOG2'")
Set rs = cn.Execute ("select * from [sheet1$] where KEY = 'LOG2'")
msgbox rs.Fields("USER_NAME").Value


RE: Storing data in excel sheet - linhke - 12-11-2009

Hi Saket,

ADODB is the method i used. I try your code and it done.

Thanks and Best Regards,

RE: Storing data in excel sheet - linhke - 12-13-2009

Hi Saket,

If i set the empty value for USER_NAME field, your code will get a Run Error. Could you tell me how to cover this issue?

Thanks and BRs,

RE: Storing data in excel sheet - Saket - 12-14-2009

where exactly you get a Run error? can you paste whatever you have tried?
OK, I think I got it, you getting error at msgbox statement. right?
Msgbox will not be able to prompt the null so in that case use
Code:
if rs.Fields("USER_NAME").Value <> Null then msgbox rs.Fields("USER_NAME").Value


RE: Storing data in excel sheet - linhke - 01-26-2010

Hi Saket,

I tried your code, but when i leave the USER_NAME field to blank, it does not do this loop:

Code:
If rs.Fields("USER_NAME").Value <> Null Then
         msgbox rs.Fields("USER_NAME").Value
      End If

Pls help me on that?
Regards,
linhke

RE: Storing data in excel sheet - linhke - 01-27-2010

RE: Storing data in excel sheet
Hi Saket,

I tried your code, but when i leave the USER_NAME field to blank, it does not do this loop:

Code:
If rs.Fields("USER_NAME").Value <> Null Then
msgbox rs.Fields("USER_NAME").Value
End If

Pls help me on that?
Regards,
linhke
Any Ideas?
Hi Saket,

Hi Saket,

Sorry, In my previous reply i didn't describe as my idea. Pls focus on this below:

I tried your code, but when i leave the USER_NAME field to blank, it does not do this loop:

Code:
If rs.Fields("USER_NAME").Value = Null Then
       Reporter.ReportEvent micFail......
End If

Pls help me on that?
Regards,
linhke

RE: Storing data in excel sheet - Saket - 01-27-2010

try 'If rs.Fields("USER_NAME").Value = "" Then'

RE: Storing data in excel sheet - linhke - 01-28-2010

I tried with "" and Empty but it's still not done???

Regards,
linhke

RE: Storing data in excel sheet - Saket - 01-28-2010

I think, I am net getting the issue.what exactly you are trying to do?

if you are executling a query like "update [sheet1$] set USER_NAME = '' where KEY = 'LOG2'")
then value of rs.Fields("USER_NAME").Value will be 'Null' which can not be populated using a message box.
if there is anything required fourther steps on Null value then you can go with the earlier statements only

Code:
if rs.Fields("USER_NAME").Value <> Null then
   msgbox rs.Fields("USER_NAME").Value
else
  msgbox "Field has a Null Value" 'or whatever steps you would like to perform
end if