writing value to .xls file from input box

[chaitanyagutala]

Dear all,

My requirement is I want to collect all my friend's name and mobile number in a xls form.For that I have script which is attached below(Friends details.au3). If I am using it, the data is being entered in the xls file in column form. I want to enter it in row wise. If you want to test the below attached script, type your name, click on click here button,then esc key or close button. Then second dialogue box prompts to enter mobile number. Type mobile number and click esc.An .xls file called friends details will be created in the folder where the script is there.Can anyone please help me out.

For your better understanding of my problem, I am attaching two autoit attachments(As I am not able to upload excel forms in this page, I am uploading the same in an au3 file)."saving as" and "need to save". "Saving as" is the file in which it is getting saved with my script and "need to save" is the file in which I need.

Please help me out in this regard.

Friends details.au3

Saving as.au3

Need to save.au3

[hawky358]

It seems like you are using a text file and the importing to Excel using delimiters?

If you want to work with pure excel look at the _Excel..... functions

Anyway you can use this code as a guide (I made the delimiter "|" since you might have spaces in your names)

You enter the name as exit to end the script

$file = FileOpen("file.txt",0)
$allnames = FileReadLine($file,1)
$allnumbers = FileReadLine($file,2)
FileClose($file)
while 1

    $name = InputBox("Contact name","Name:")
    if StringCompare($name,"exit") = 0 Then ExitLoop
    $number  = InputBox($name & "'s number","Number:")

    $allnames &= $name & "|"
    $allnumbers &= $number & "|"

WEnd

$file = FileOpen("file.txt",2)
FileWriteLine($file,$allnames)
FileWriteLine($file,$allnumbers)
FileClose($file)

edit: fixed code box

Edited May 8, 2010 by hawky358

[chaitanyagutala]

I am not using any text file and I am not importing anything. when we click on the exe it should ask for ur name and then it should ask for mobile number. Both should be copied to an xls file in row wise. But in my script it is entered in column wise.I am trying to use _excelwritecell function. But I am getting some problem in using it.

can u give me some examples in using it? I tried in help file but no use.

Edited May 9, 2010 by chaitanyagutala

[hawky358]

Both should be copied to an xls file in row wise. But in my script it is entered in column wise

You say it's an xls file, but clearly you are just working with plain text (Just giving the text file an xls extension)

Anyway here's using the Excel functions

enter the name as exit to end and write - I just use input boxes for the example, you can build a gui if you want

expandcollapse popup

#include <Excel.au3>
#include <array.au3>

$filename = "friends.xls"

global $newnames[1],$newnumbers[1]

while 1

    $name = InputBox("Contact name","Name:")
    if StringCompare($name,"exit") = 0 Then ExitLoop
    $number  = InputBox($name & "'s number","Number:")
    
    _ArrayAdd($newnames,$name)
    _ArrayAdd($newnumbers,$number)


WEnd

if FileExists($filename) = 1 Then
    $excelobj = _ExcelBookOpen(@ScriptDir &"\" &$filename)
    $k = 0
    Do ;Find first open column
        $k += 1
        $val = _ExcelReadCell($excelobj,1,$k) 
    Until $val = ""
        
Else
    $k=1    
    $excelobj = _ExcelBookNew() ;Create new book, make it visible
    
EndIf


For $i = 1 To UBound($newnames) - 1
    
    _ExcelWriteCell($excelobj, $newnames[$i], 1, $k) ;Write to the Cell
    _ExcelWriteCell($excelobj, $newnumbers[$i], 2, $k)
    $k+=1
Next


_ExcelBookSaveAs($excelobj, @ScriptDir & "\" & $filename, "xls", 0, 1) ;
_ExcelBookClose($excelobj)

[chaitanyagutala]

Hey dude, I have changed the script you gave at 2 places and it is working fine as per my requirement. But it is writing values only until 27 lines in excel file after that it is going to first line again and overwriting data in it. we will have data loss after 27 lines.Please help me out in this regard dude. Please check the below script. Enter value 30 times and check. After 27 lines first lines are getting overwritten.I dont know why it is stopping at 27 lines only. Please check and do the needful dude. Thanks in advance.

#include <Excel.au3>

#include <array.au3>

$filename = "friends.xls"

global $newnames[1],$newnumbers[1]

while 1

$name = InputBox("Contact Details","Name:")

if StringCompare($name,"exit") = 0 Then ExitLoop

$number = InputBox($name & "'s number","Number:")

_ArrayAdd($newnames,$name)

_ArrayAdd($newnumbers,$number)

WEnd

if FileExists($filename) = 1 Then

$excelobj = _ExcelBookOpen(@ScriptDir &"\" &$filename,0)

$k = 0

Do ;Find first open column

$k += 1

$val = _ExcelReadCell($excelobj,1,$k)

Until $val = ""

Else

$k=1

$excelobj = _ExcelBookNew() ;Create new book, make it visible

EndIf

For $i = 1 To UBound($newnames) - 1

_ExcelWriteCell($excelobj, $newnames[$i], $k,1) ;Write to the Cell

_ExcelWriteCell($excelobj, $newnumbers[$i],$k,2)

$k+=1

Next

_ExcelBookSaveAs($excelobj, @ScriptDir & "\" & $filename, "xls", 0, 1) ;

_ExcelBookClose($excelobj)

[hawky358]

It doesn't overwrite after 27 entries.

What's happening is that when you exit it starts overwriting at line 3.

You swapped the way it's writing (adding rows) but the check is still looking for open columns.

Just change

$val = _ExcelReadCell($excelobj,1,$k)

to

$val = _ExcelReadCell($excelobj,$k,1)

in the Do where it says ;Find first open column

btw please enclose your code in

brackets in the forum, it just makes it alot easier to read.

[chaitanyagutala]

Thank you very much dude. This is what I want exactly. Thank you once again