Storing data in excel sheet

[linhke]

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,

[Saket]

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

[linhke]

Hi Saket,

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

Thanks and Best Regards,

[linhke]

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,

[Saket]

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

[linhke]

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

[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

[Saket]

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

[linhke]

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

Regards,
linhke

[Saket]

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