Ted Wise

I recently had the need to build data for a client in an Excel spreadsheet.  Each tab in the spreadsheet had information that was easily editable by business staff.  But the data in each tab had to eventually be processed by a Ruby script for manipulation and eventual insertion into a database. 

I'm working on a Mac and wanted to learn Applescript in more depth so I decided to automate the creation of the CSV files - one for each tab.  The examples I found were sparse or incorrect so I'm providing the solution here.  In order to use this you'll need to copy the code below into the Script Editor and save the script.  I'm using Mac Office 2008 so I don't know if this works with previous versions or not.

 
set theFile to (choose file with prompt "Choose Excel file") as string
set value to ""
tell application "Microsoft Excel"
	activate
	open theFile
	set fileFormat to (class of (file format of active workbook))
	display dialog fileFormat
	set allSheets to (every worksheet in active workbook)
	repeat with i from 1 to (count allSheets)
		set theWorksheet to (item i of allSheets)
		activate object theWorksheet
		set sheetName to (name of theWorksheet as text)
		set csvName to sheetName & ".csv"
		save as active sheet filename csvName file format CSV file format
	end repeat
	close active workbook without saving
end tell
 

Related posts:

1. Creating a Hit List task from LaunchBar
2. Integrating The Hit List with Mail.app
3. Running Oracle for Development on the Mac
4. Geek Tool