Sign in to follow this  
Followers 0

Export To excel

7 posts in this topic

Posted · Report post

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 · Report post

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 · Report post

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) · Report post

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 · Report post

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 · Report post

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 · Report post

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.