Jump to content

This site uses cookies. By continuing to browse the site you are agreeing to our use of cookies. Find out more here. X
X


Photo

Export To excel


  • Please log in to reply
6 replies to this topic

#1 richietheprogrammer

richietheprogrammer

    Prodigy

  • Active Members
  • PipPipPip
  • 157 posts

Posted 12 July 2011 - 07:08 PM

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!







#2 kaotkbliss

kaotkbliss

    Universalist

  • Active Members
  • PipPipPipPipPipPip
  • 2,440 posts

Posted 12 July 2011 - 07:15 PM

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

8yyxkx.jpg010101000110100001101001011100110010000001101001011100110010000

001101101011110010010000001110011011010010110011100100001

My first project  Allin1 Program launcher

Bill Calculator

Got <Insert problem here>? There's an AutoIt script for that!


#3 smartee

smartee

    Hippies.They're everywhere.

  • Active Members
  • PipPipPipPipPipPip
  • 661 posts

Posted 12 July 2011 - 07:24 PM

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?

#4 richietheprogrammer

richietheprogrammer

    Prodigy

  • Active Members
  • PipPipPip
  • 157 posts

Posted 12 July 2011 - 07:42 PM

hehe nice replies. Im look for specifics with excel functions. My code:
Plain Text         
    #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, 12 July 2011 - 07:54 PM.


#5 smartee

smartee

    Hippies.They're everywhere.

  • Active Members
  • PipPipPipPipPipPip
  • 661 posts

Posted 12 July 2011 - 08:15 PM

I offer you a light and simple solution, that uses CSV files compatible with excel :)
AutoIt         
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


#6 richietheprogrammer

richietheprogrammer

    Prodigy

  • Active Members
  • PipPipPip
  • 157 posts

Posted 12 July 2011 - 08:19 PM

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

AutoIt         
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 ;)

#7 JoHanatCent

JoHanatCent

    Just for Fun

  • Active Members
  • PipPipPipPipPipPip
  • 402 posts

Posted 13 July 2011 - 04:51 AM

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

Posted Image




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users