Jump to content

Export To excel


Recommended Posts

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!

Link to comment
Share on other sites

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

010101000110100001101001011100110010000001101001011100110010000

001101101011110010010000001110011011010010110011100100001

My Android cat and mouse game
https://play.google.com/store/apps/details?id=com.KaosVisions.WhiskersNSqueek

We're gonna need another Timmy!

Link to comment
Share on other sites

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

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

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

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
Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...