Jump to content
Sign in to follow this  
chaitanyagutala

writing value to .xls file from input box

Recommended Posts

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


ChaitanyaKumar GI dont make mistakes.I just have learning.I have no special talents. I am only passionately curious.

Share this post


Link to post
Share on other sites
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 by hawky358

Share this post


Link to post
Share on other sites
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 by chaitanyagutala

ChaitanyaKumar GI dont make mistakes.I just have learning.I have no special talents. I am only passionately curious.

Share this post


Link to post
Share on other sites
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

#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)

Share this post


Link to post
Share on other sites
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)


ChaitanyaKumar GI dont make mistakes.I just have learning.I have no special talents. I am only passionately curious.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
chaitanyagutala

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


ChaitanyaKumar GI dont make mistakes.I just have learning.I have no special talents. I am only passionately curious.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.