Sign in to follow this  
Followers 0

Export To excel

7 posts in this topic

Posted

Hey guys, trying to write a script that takes a few inputs, and save them to an excel spreadsheet. I want it to check if the spreadsheet is already made in the given path, if it is, then it should write the new data on the next cell. Say I ask the user to input their name, ID, phone number. Once they do that, it should export it to an excel sheet. If the sheet is already present, just write the data below the existing data. Sounds simple, but I cant get it to work.. Examples? Any help is much appreciated!

Share this post


Link to post
Share on other sites



Posted

FileExists will check to see if your file exists

Looping through _ExcelReadCell adding 1 to each loop until you reach an empty cell

then use _ExcelWriteCell to enter the info you want

Share this post


Link to post
Share on other sites

Posted

Sounds straightforward enough, a FileExists() maybe a GUICtrlRead() or two and a couple of _Excel*() functions with a sprinkle of Parmesan :) , what code do you have so far?

Share this post


Link to post
Share on other sites

Posted (edited)

hehe nice replies. Im look for specifics with excel functions. My code:

#include <File.au3>
#include <Excel.au3>
#Include <Array.au3>

$ID= inputbox("Employee ID","Enter Your employee ID")

    $name = inputbox("Employee ID","Enter Your Name")

    $sFilePath1 = @ScriptDir & "\SaveAsExample.xls" ;
if fileexists($sFilePath1) = 0 Then 

    $oExcel = _ExcelBookNew(0)
    
_ExcelBookSaveAs($oExcel, @ScriptDir & "\SaveAsExample", "xls")


else 
    
    $oExcel =$sFilePath1
    _ExcelBookopen($oExcel,0)
        
    
    
    endif 
    _ExcelWriteCell($oExcel, $ID , 1, 1) ;Write to the Cell
_ExcelWriteCell($oExcel, $name, 2, 1) ;Write to the Cell
_ExcelBookClose($oExcel, 1, 0) ;This method will save then Close the file, without any of the normal prompts, regardless of changes

This only works when the file is NOT existing. If the file is already there, nothing changes.. Any ideas? Also, how would I go around looping the cell numbers? Thanks!

Edited by richietheprogrammer

Share this post


Link to post
Share on other sites

Posted

I offer you a light and simple solution, that uses CSV files compatible with excel :)

Global Const $GUI_EVENT_CLOSE = -3

$sDataFilePath = @ScriptDir & "\Records.csv"

#region ### START Koda GUI section ### Form=
$Form1 = GUICreate("Demo1: New Record", 580, 115)
$Input1 = GUICtrlCreateInput("", 10, 30, 270, 21)
$Input2 = GUICtrlCreateInput("", 300, 30, 270, 21)
$Input3 = GUICtrlCreateInput("", 10, 80, 270, 21)
$Label1 = GUICtrlCreateLabel("Name:", 10, 10, 35, 17)
$Label2 = GUICtrlCreateLabel("ID:", 300, 10, 18, 17)
$Label3 = GUICtrlCreateLabel("Phone No:", 10, 60, 55, 17)
$Button1 = GUICtrlCreateButton("Save to CSV", 450, 70, 120, 30)
GUISetState(@SW_SHOW)
#endregion ### END Koda GUI section ###

While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit
        Case $Button1
            _ExportData()
            MsgBox(64, @ScriptName, "Record Saved.")
    EndSwitch
WEnd

Func _ExportData()
    If Not FileExists($sDataFilePath) Then
        FileWriteLine($sDataFilePath, "Name;ID;Phone No.;")
    EndIf
    For $i = $Input1 To $Input3
        FileWrite($sDataFilePath, GUICtrlRead($i) & ";")
    Next
    FileWriteLine($sDataFilePath, "")
EndFunc   ;==>_ExportData

Share this post


Link to post
Share on other sites

Posted

I offer you a light and simple solution, that uses CSV files compatible with excel :)

Global Const $GUI_EVENT_CLOSE = -3

$sDataFilePath = @ScriptDir & "\Records.csv"

#region ### START Koda GUI section ### Form=
$Form1 = GUICreate("Demo1: New Record", 580, 115)
$Input1 = GUICtrlCreateInput("", 10, 30, 270, 21)
$Input2 = GUICtrlCreateInput("", 300, 30, 270, 21)
$Input3 = GUICtrlCreateInput("", 10, 80, 270, 21)
$Label1 = GUICtrlCreateLabel("Name:", 10, 10, 35, 17)
$Label2 = GUICtrlCreateLabel("ID:", 300, 10, 18, 17)
$Label3 = GUICtrlCreateLabel("Phone No:", 10, 60, 55, 17)
$Button1 = GUICtrlCreateButton("Save to CSV", 450, 70, 120, 30)
GUISetState(@SW_SHOW)
#endregion ### END Koda GUI section ###

While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit
        Case $Button1
            _ExportData()
            MsgBox(64, @ScriptName, "Record Saved.")
    EndSwitch
WEnd

Func _ExportData()
    If Not FileExists($sDataFilePath) Then
        FileWriteLine($sDataFilePath, "Name;ID;Phone No.;")
    EndIf
    For $i = $Input1 To $Input3
        FileWrite($sDataFilePath, GUICtrlRead($i) & ";")
    Next
    FileWriteLine($sDataFilePath, "")
EndFunc   ;==>_ExportData

Shoulda known csv is better for this. You are awesome! I will use this in my scipt! Thanks again ;)

Share this post


Link to post
Share on other sites

Posted

hehe nice replies. Im look for specifics with excel functions. My code:

This only works when the file is NOT existing. If the file is already there, nothing changes.. Any ideas? Also, how would I go around looping the cell numbers? Thanks!

So I don't see much wrong with your scripts Plus it will add to the bottom the new info:

#include <File.au3>
#include <Excel.au3>
#include <Array.au3>
$ID = InputBox("Employee ID", "Enter Your employee ID")
$name = InputBox("Employee ID", "Enter Your Name")
$sFilePath1 = @ScriptDir & "\SaveAsExample.xls"
If FileExists($sFilePath1) = 0 Then
    $oExcel = _ExcelBookNew(1)
    _ExcelBookSaveAs($oExcel, @ScriptDir & "\SaveAsExample.xls", "xls")
    Global $aLastRow = $oExcel.Cells.SpecialCells($xlCellTypeLastCell).Row;What is the last used row number?
Else
    $oExcel = $sFilePath1
    $oExcel = _ExcelBookOpen($oExcel, 1)
    Global $aLastRow = $oExcel.Cells.SpecialCells($xlCellTypeLastCell).Row;What is the last used row number?
EndIf
_ExcelWriteCell($oExcel, $ID, $aLastRow + 1, 1) ;Write to the Cell
_ExcelWriteCell($oExcel, $name, $aLastRow + 2, 1) ;Write to the Cell
_ExcelBookClose($oExcel, 1, 0) ;This method will save then Close the file, without any of the normal prompts, regardless of changes

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  
Followers 0

  • Recently Browsing   0 members

    No registered users viewing this page.