Hello folks,
I’ve been googling around all afternoon to see if what I would like to do is possible. I have a collection of Excel workbooks that have a random number of sheets within them. For example, file1.xls has 12 sheets, file2.xls has 34 sheets and file3.xls has 4 sheets. The number of sheets varies all the time.
I need to take each sheet in turn and export that to CSV with UTF8 encoding too (just remembered that!). I can’t see a way of doing this with Excel, does anyone know if it can be achieved?
Model: MacBook Pro
Browser: Firefox 4.0.1
Operating System: Mac OS X (10.6)
Moved to the appropriate forum ![:wink: :wink:](/images/emoji/apple/wink.png?v=12)
-- Paths to Excel file and output directory
set excelDirectory to "Macintosh HD:Users:scott:Downloads:Excel:"
set outputDirectory to "Macintosh HD:Users:scott:Downloads:Excel:converted"
tell application "Microsoft Excel"
activate
set theWorkbook to open workbook workbook file name excelDirectory & "excel.xls"
set maxCount to count of worksheets of theWorkbook
repeat with i from 1 to maxCount
set theWorkbook to open workbook workbook file name excelDirectory & "excel.xls"
set theWorksheet to worksheet i of theWorkbook
set worksheetName to name of theWorksheet
activate object worksheet worksheetName
save as theWorksheet filename (outputDirectory & ":" & worksheetName & ".csv") file format CSV file format
close active workbook
end repeat
end tell
Is what I’ve got so far. I’d also like to export as UTF8 and automatically overwrite files to prevent the save changes dialog popping up.
try this…
set f to choose file
set outputDirectory to (path to downloads folder as text) & "Excel:converted:"
-- make sure the outputDirectory exists and if not create it
if outputDirectory ends with ":" then set outputDirectory to text 1 thru -2 of outputDirectory
do shell script "mkdir -p " & quoted form of POSIX path of outputDirectory
-- save all worksheets as csv files
set csvPaths to {}
tell application "Microsoft Excel"
open f
set theSheets to worksheets of active workbook
set workbookName to name of active workbook
if workbookName ends with ".xls" then set workbookName to text 1 thru -5 of workbookName
repeat with aSheet in theSheets
set thisPath to outputDirectory & ":" & workbookName & "." & name of aSheet & ".csv"
set end of csvPaths to thisPath
save aSheet in thisPath as CSV file format
end repeat
close active workbook without saving
end tell
-- convert csv files to UTF8 format
repeat with csvPath in csvPaths
try
set utf8Path to text 1 thru -4 of csvPath & "utf8.csv"
set csvText to read file csvPath
writeTo_UTF8(utf8Path, csvText, true)
end try
tell application "Finder" to delete file csvPath
end repeat
(*============= SUBROUTINES ================*)
on writeTo_UTF8(targetFile, theText, appendText)
try
set targetFile to targetFile as text
set openFile to open for access file targetFile with write permission
if appendText is false then
set eof of openFile to 0
write «data rdatEFBBBF» to openFile starting at eof -- UTF-8 BOM
else
tell application "Finder" to set fileExists to exists file targetFile
if fileExists is false then
set eof of openFile to 0
write «data rdatEFBBBF» to openFile starting at eof -- UTF-8 BOM
end if
end if
write theText as «class utf8» to openFile starting at eof
close access openFile
return true
on error theError
try
close access file targetFile
end try
return theError
end try
end writeTo_UTF8