richietheprogrammer Posted July 12, 2011 Share Posted July 12, 2011 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 More sharing options...
kaotkbliss Posted July 12, 2011 Share Posted July 12, 2011 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 gamehttps://play.google.com/store/apps/details?id=com.KaosVisions.WhiskersNSqueek We're gonna need another Timmy! Link to comment Share on other sites More sharing options...
smartee Posted July 12, 2011 Share Posted July 12, 2011 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? Link to comment Share on other sites More sharing options...
richietheprogrammer Posted July 12, 2011 Author Share Posted July 12, 2011 (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 July 12, 2011 by richietheprogrammer Link to comment Share on other sites More sharing options...
smartee Posted July 12, 2011 Share Posted July 12, 2011 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 More sharing options...
richietheprogrammer Posted July 12, 2011 Author Share Posted July 12, 2011 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 More sharing options...
JoHanatCent Posted July 13, 2011 Share Posted July 13, 2011 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 More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now