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,
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
Hi Saket,
ADODB is the method i used. I try your code and it done.
Thanks and Best Regards,
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,
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
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
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
try 'If rs.Fields("USER_NAME").Value = "" Then'
I tried with "" and Empty but it's still not done???
Regards,
linhke
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