Saving worksheets as CSV---FRUSTRATING!

[dg]

Hello,
I just spent the better part of two hours trying to figure out
something that ought to be very simple, but, in Excel, isn't.

I have an Excel file with several worksheets. What I want is to save
some of the worksheets as separate CSV files in the same directory as
the source file. Each CSV file should be named as the original source
file plus an underscore plus the worksheet name.

Example: Excel file called Source1.xls contains 3 worksheets called
WS1, WS2, and WS3. What I want is to have three additional files with
names Source1_WS1.csv, Source1_WS2.csv, and Source1_WS3.csv in the
same directory as Source1.xls containing its respective worksheets (if
the name of the worksheet would be preserved it would be better, but
not essential).

Sounds simple, right? Well, no.

Here's what I came up with. To simplify, in a first step I will
attempt to save all worksheets as CSV files.

set theFile to choose file

tell application "Microsoft Excel"
activate
open theFile
set sheet_count to the count of sheets
repeat with i from 1 to sheet_count
my save_sheet(i)
end repeat
close active workbook without saving
end tell

on save_sheet(i)
tell application "Microsoft Excel"
set theSheet to worksheet i of active workbook
set sheet_name to name of sheet i
set fname to (full name of active workbook)
set fname to text 1 thru -5 of fname --remove file extension
set tname to fname & "_" & sheet_name & ".csv"
activate object sheet i
save as (sheet i) filename tname file format CSV
end tell
end save_sheet



Well, what happens is that Excel CHANGES THE NAME OF THE ENTIRE
WORKBOOK upon saving the first worksheet (the first worksheet also
gets RENAMED). WHY OH WHY????

Therefore, the name of the second worksheet also contains the first,
etc.

What I get is:
- files Source1_WS1.csv, Source1_WS1_WS2.csv, and
Source1_WS1_WS2_WS3.csv are created
- the first (and only the first) worksheet in the original file is
renamed to Source1_WD1 (with the name truncated after a certain number
of characters)
- the original file name is changed to Source1_WS1_WS2_WS3.csv
(the last two changes are undone by the "close without saving"
statement, so the original file is preserved)

Why does Excel rename the whole file when I ask to save a
worksheet????!? That is nonsense.
Any idea on how to achieve what I want?
Thanks.

 

[JE McGimpsey]

Why does Excel rename the whole file when I ask to save a
worksheet????!? That is nonsense.

The reason it's not nonsense is because XL is doing exactly what you
tell it to, of course <g>...

FWIW, it's somewhat confusing in VBA, too. The important thing is that
XL can only save workBOOKs, not workSHEETs. So your script (using the
workSHEET save as method) saves the workbook with the new filename and
renames the worksheet, makes the new workbook the active workbook and
closes the original workbook. Then when your loop repeats, it operates
on the new active workbook (with the new name), and appends the new
worksheet name onto the current one.

The cleanest solution is to copy the worksheet to a new workbook (which
then becomes the active workbook), save that workbook, and close it.
Perhaps something like:

on save_sheet(i)
tell application "Microsoft Excel"
set theSheet to worksheet i of active workbook
set sheet_name to name of sheet i
set fname to (full name of active workbook)
set fname to text 1 through -5 of fname
set tname to fname & "_" & sheet_name & ".csv"
copy worksheet sheet i
save workbook as active workbook filename tname file format CSV
close active workbook
end tell
end save_sheet

 

[dg]

JE, thank you for your prompt answer.

Meanwhile, I found a solution myself, saving the original file name
and removing the subroutine:

set theFile to choose file

tell application "Microsoft Excel"
activate
open theFile
set fname to (full name of active workbook)
set fname to text 1 thru -5 of fname --remove file extension
set sheet_count to the count of sheets
repeat with i from 1 to sheet_count
set sheet_name to name of sheet i
set tname to fname & "_" & sheet_name & ".csv"
save as (sheet i) filename tname file format CSV
end repeat
close active workbook without saving
end tell


This works, except that the sheet names in the .CSV files are renamed.

Your solution also work, and the sheets retain their correct names in
the CSV files.

I still think that it's a shame that Excel does not allow a simple
save operation on a worksheet. Waiting for the port of OpenOffice with
Aqua GUI to completely ditch M$ Office from my computer.

 

[JE McGimpsey]

I still think that it's a shame that Excel does not allow a simple
save operation on a worksheet.

While I can understand your desire, it wouldn't make a lot of sense to
do so. Worksheets can only exist as part of workbooks, so saving a
worksheet necessarily requires saving its parent workbook. It's not that
difficult to copy the sheet to a new workbook and save it.

While some folks might use a shortcut that combines those functions, I
suspect there's not a whole lot of them.

Waiting for the port of OpenOffice with Aqua GUI to completely ditch
M$ Office from my computer.

NeoOffice has had an Aqua GUI for a long time:

http://www.neooffice.org/

OpenOffice has an alpha available

http://porting.openoffice.org/mac/download/aqua.html

However, IIRC, both mimic XL's Save As behavior, and neither have
AppleScript support, so I'm not sure what you're expecting to be
better...

 

[dg]

Correction: Your solution DOES NOT WORK: The individual worksheets do
NOT get saved as CSV, they still get saved as .XLS workbooks. I think
that's because of the use of "save workbook" instead of "save as
(sheet)".

So, here is my final solution, for the benefit of others. I have now
added the ability for the user to enter which sheets get saved as CSV.

set theFile to choose file

display dialog "Enter sheets to save (comma-delimited)" default answer
"1"
set theList to text returned of the result
try
set oldDelims to AppleScript's text item delimiters -- save their
current state
set AppleScript's text item delimiters to {","} -- declare new
delimiters
set sheet_list to every text item of theList
set AppleScript's text item delimiters to oldDelims -- restore them
on error
set AppleScript's text item delimiters to oldDelims -- restore them
in case something went wrong
end try

tell application "Microsoft Excel"
activate
open theFile
repeat with i in sheet_list
my save_sheet(i as integer)
end repeat
close active workbook without saving
end tell

on save_sheet(i)
tell application "Microsoft Excel"
set sheet_name to name of sheet i
set fname to (full name of active workbook)
set fname to text 1 thru -5 of fname --remove file extension
set tname to fname & "_" & sheet_name & ".csv"
copy worksheet sheet i
--save workbook as active workbook filename tname file format CSV
save as (sheet 1) filename tname file format CSV
close active workbook without saving
end tell
end save_sheet

While I can understand your desire, it wouldn't make a lot of sense to
do so. Worksheets can only exist as part of workbooks, so saving a
worksheet necessarily requires saving its parent workbook. It's not that
difficult to copy the sheet to a new workbook and save it.

I strongly disagree. Why would exporting a worksheet "necessarily
require" saving its parent workbook? Incidentally, I am going through
all of this because, unfortunately, people keep sending me files in
proprietary Office formats. I really need Excel only to export these
files so I can use the program I WANT to process my files. I would be
much better off if people would use only ASCII text files in this
case.

Also, sometimes I get really large files. Having to copy them to a new
workbook is very inefficient and a waste of resource and processing
time. I guess it's kind of the "Microsoft Office" way of doing things.
No thanks.

NeoOffice has had an Aqua GUI for a long time:

http://www.neooffice.org/

I know, but that is excruciatingly slow (maybe because of its use of
Java).

OpenOffice has an alpha available

http://porting.openoffice.org/mac/download/aqua.html

Yes, but after reading their warning about not using the software on a
production machine, I prefer to hold off...

However, IIRC, both mimic XL's Save As behavior, and neither have
AppleScript support, so I'm not sure what you're expecting to be
better...

Well, for one, it will be FREE.
In time, OpenOffice will completely replace Office. Or at least,
that's what I hope.