Saving Excel 2008 files using AppleScript

Hello!

I teach journalism at a university and keep my course grades in an Excel spreadsheet. I had a macro in Excel 2004 (Mac) that would save the worksheet in a tab delimited text file then move that file to a secure server where a perl script would allow the students to type in their passwords and see their assignments grades.

Of course, that macro died the moment the university upgraded (if you want to call it that) to Excel 2008 (Mac).

I stumbled into putting together an Applescript that accomplishes much of what I need. There are a couple of areas I am still having trouble with and would greatly appreciate your help.

I share this script with other faculty members and try to make it as easy to use as possible. I have an Excel spreadsheet that contains the names of the files being used this term. That makes it easier to update every term.

The script first opens the spreadsheet holding the names of the courses then uses that info to open the actual files.

In the part of the script shown below, variable “whereisgb” is the path statement and “gbNames” is the variable holding the name of the spreadsheet holding the names of the individual course gradebook spreadsheets for the current term.

Cell A7 is where we pick up the name of the first gradebook spreadsheet and place that in variable in01.

Cell C7 is where we pick up the name of that gradebook that we want to use as it is being published to the Web server.

Variable userdt is the path to the user’s desktop.

I am trying to save the file storing the grades to the desktop in a Mac text format. While this is working, it stops on each file asking me if I want to save the changes to the text version of the file. I thought the “overwrite” command would take care of that. It doesn’t seem to be working.


tell application "Microsoft Excel"
	open workbook workbook file name whereisgb & gbNames
	if cell "A7" is not "" then
		set in01 to value of cell "A7"
		set file01 to value of cell "C7"
		open workbook workbook file name whereisgb & in01
		set fileout01 to userdt & file01
		save active workbook in (fileout01) as text Mac file format with overwrite
		close active workbook with overwrite
	end if
end tell

Am I doing something wrong or is this something I will have to live with. (The 2004 macro would run without presenting the “Do you want to save your changes” window.)

Here is a copy of the entire script. I set it up to work with ten classes, way more that we should ever need, but I have only two classes in this script now because I am trying to get it working:


global file01, file02, files03, file04, file05, file06, file07, file08, file09, file10
global in01, in02, in03, in04, in05, in06, in07, in08, in09, in10
global userhome, whereisgb
global fileout01, fileout02, fileout03, fileout04, fileout05, fileout06, fileout07, fileout08, fileout09, fileout10

-- 
-- Update this file each term by placing your current classes in the 
-- file numbers below. File numbers not in use should be set to "r"
-- Save by APPLE-S first then SAVE AS changing 
-- FILE FORMAT to APPLICATION and WHERE to DESKTOP.
-- make sure that STARTUP SCREEN is NOT checked.
-- 
set userdt to "Dr. xxxx's HD:Users:faculty:Desktop:"
set whereisgb to "Dr. xxxx's HD:Users:faculty:Documents:Gradebook:"
set gbNames to "Post GB3.xls"
set file01 to "r"
set file02 to "r"
set file03 to "r"
set file04 to "r"
set file05 to "r"
set file06 to "r"
set file07 to "r"
set file08 to "r"
set file09 to "r"
set file10 to "r"
set in01 to "r"
set in02 to "r"
set in03 to "r"
set in04 to "r"
set in05 to "r"
set in06 to "r"
set in07 to "r"
set in08 to "r"
set in09 to "r"
set in10 to "r"

tell application "Microsoft Excel"
	open workbook workbook file name whereisgb & gbNames
	if cell "A7" is not "" then
		set in01 to value of cell "A7"
		set file01 to value of cell "C7"
		open workbook workbook file name whereisgb & in01
		set fileout01 to userdt & file01
		save active workbook in (fileout01) as text Mac file format with overwrite
		close active workbook with overwrite
	end if
	
	if cell "A8" is not "" then
		set in02 to value of cell "A8"
		set file02 to value of cell "C8"
		open workbook workbook file name whereisgb & in02
		set fileout02 to userdt & file02
		save active workbook in (fileout02) as text Mac file format with overwrite
		close active workbook with overwrite
	end if
	
	
	quit
end tell


tell application "Fetch"
	activate
	make new transfer window at beginning with properties {hostname:"ssss.xxxx.edu", username:"xxxxxxx", password:"xxxxxxx", initial folder:"/vault"}
	if file01 does not start with "r" then put into transfer window "ssss.xxxx.edu" item alias fileout01
	if file02 does not start with "r" then put into transfer window "ssss.xxxx.edu" item alias fileout02
	if file03 does not start with "r" then put into transfer window "ssss.xxxx.edu" item alias fileout03
	if file04 does not start with "r" then put into transfer window "ssss.xxxx.edu" item alias fileout04
	if file05 does not start with "r" then put into transfer window "ssss.xxxx.edu" item alias fileout05
	if file06 does not start with "r" then put into transfer window "ssss.xxxx.edu" item alias fileout06
	if file07 does not start with "r" then put into transfer window "ssss.xxxx.edu" item alias fileout07
	if file08 does not start with "r" then put into transfer window "ssss.xxxx.edu" item alias fileout08
	if file09 does not start with "r" then put into transfer window "ssss.xxxx.edu" item alias fileout09
	if file10 does not start with "r" then put into transfer window "sssss.xxxx.edu" item alias fileout10
	try
		quit
	end try
end tell
tell application "Finder"
	if file01 does not start with "r" then delete file fileout01
	if file02 does not start with "r" then delete file fileout02
	if file03 does not start with "r" then delete file fileout03
	if file04 does not start with "r" then delete file fileout04
	if file05 does not start with "r" then delete file fileout05
	if file06 does not start with "r" then delete file fileout06
	if file07 does not start with "r" then delete file fileout07
	if file08 does not start with "r" then delete file fileout08
	if file09 does not start with "r" then delete file fileout09
	if file10 does not start with "r" then delete file fileout10
end tell

Thanks for any ideas you have on how to force this script to stop asking if I want to save my text files.

//steve

Model: MacBook
AppleScript: 1.10.7
Browser: Firefox 3.0
Operating System: Mac OS X (10.4)

Thanks to the magic of Google and finding some solid ideas on this board, I have a working script. It is posted below and you will see it is NOTHING similar to the one I started above…

The working code for this script is posted below. While it may be possible to clean this up, I am happy to have something that actually works.

global file01, file02, files03, file04, file05, file06, file07, file08, file09, file10
global in01, in02, in03, in04, in05, in06, in07, in08, in09, in10
global userhome, whereisgb
global fileout01, fileout02, fileout03, fileout04, fileout05, fileout06, fileout07, fileout08, fileout09, fileout10


– Update this file each term by placing your current classes in the
– file numbers below. File numbers not in use should be set to “r”
– Save by APPLE-S first then SAVE AS changing
– FILE FORMAT to APPLICATION and WHERE to DESKTOP.
– make sure that STARTUP SCREEN is NOT checked.

set userdt to “Dr. xxxx’s HD:Users:faculty:Desktop:”
set whereisgb to “Dr. xxxxx’s HD:Users:faculty:Documents:Gradebook:”
set gbNames to “Post GB.xls”
set file01 to “r”
set file02 to “r”
set file03 to “r”
set file04 to “r”
set file05 to “r”
set file06 to “r”
set file07 to “r”
set file08 to “r”
set file09 to “r”
set file10 to “r”
set in01 to “r”
set in02 to “r”
set in03 to “r”
set in04 to “r”
set in05 to “r”
set in06 to “r”
set in07 to “r”
set in08 to “r”
set in09 to “r”
set in10 to “r”

tell application “Microsoft Excel”
open workbook workbook file name whereisgb & gbNames

if value of cell "A7" is not "" then
	set in01 to value of cell "A7"
	set file01 to value of cell "C7"
	set fileout01 to userdt & file01
	tell application "System Events"
		if (exists file fileout01) then
			delete file fileout01
		end if
	end tell
end if

if value of cell "A8" is not "" then
	set in02 to value of cell "A8"
	set file02 to value of cell "C8"
	set fileout02 to userdt & file02
	tell application "System Events"
		if (exists file fileout02) then
			delete file fileout02
		end if
	end tell
end if


if value of cell "A9" is not "" then
	set in03 to value of cell "A9"
	set file03 to value of cell "C9"
	set fileout03 to userdt & file03
	tell application "System Events"
		if (exists file fileout03) then
			delete file fileout03
		end if
	end tell
end if


if value of cell "A10" is not "" then
	set in04 to value of cell "A10"
	set file04 to value of cell "C10"
	set fileout04 to userdt & file04
	tell application "System Events"
		if (exists file fileout04) then
			delete file fileout04
		end if
	end tell
end if



if value of cell "A11" is not "" then
	set in05 to value of cell "A11"
	set file05 to value of cell "C11"
	set fileout05 to userdt & file05
	tell application "System Events"
		if (exists file fileout05) then
			delete file fileout05
		end if
	end tell
end if


if value of cell "A12" is not "" then
	set in06 to value of cell "A12"
	set file06 to value of cell "C12"
	set fileout06 to userdt & file06
	tell application "System Events"
		if (exists file fileout06) then
			delete file fileout06
		end if
	end tell
end if


if value of cell "A13" is not "" then
	set in07 to value of cell "A13"
	set file07 to value of cell "C13"
	set fileout07 to userdt & file07
	tell application "System Events"
		if (exists file fileout07) then
			delete file fileout07
		end if
	end tell
end if


if value of cell "A14" is not "" then
	set in08 to value of cell "A14"
	set file08 to value of cell "C14"
	set fileout08 to userdt & file08
	tell application "System Events"
		if (exists file fileout08) then
			delete file fileout08
		end if
	end tell
end if


if value of cell "A15" is not "" then
	set in09 to value of cell "A15"
	set file09 to value of cell "C15"
	set fileout09 to userdt & file09
	tell application "System Events"
		if (exists file fileout09) then
			delete file fileout09
		end if
	end tell
end if



if value of cell "A16" is not "" then
	set in10 to value of cell "A16"
	set file10 to value of cell "C16"
	set fileout10 to userdt & file10
	tell application "System Events"
		if (exists file fileout10) then
			delete file fileout10
		end if
	end tell
end if


close active workbook


if file01 does not start with "r" then
	open workbook workbook file name whereisgb & in01
	save active workbook in (fileout01) as text Mac file format with overwrite
	close active workbook saving no
end if


if file02 does not start with "r" then
	open workbook workbook file name whereisgb & in02
	save active workbook in (fileout02) as text Mac file format with overwrite
	close active workbook saving no
end if


if file03 does not start with "r" then
	open workbook workbook file name whereisgb & in03
	save active workbook in (fileout03) as text Mac file format with overwrite
	close active workbook saving no
end if


if file04 does not start with "r" then
	open workbook workbook file name whereisgb & in04
	save active workbook in (fileout04) as text Mac file format with overwrite
	close active workbook saving no
end if


if file05 does not start with "r" then
	open workbook workbook file name whereisgb & in05
	save active workbook in (fileout05) as text Mac file format with overwrite
	close active workbook saving no
end if


if file06 does not start with "r" then
	open workbook workbook file name whereisgb & in06
	save active workbook in (fileout06) as text Mac file format with overwrite
	close active workbook saving no
end if


if file07 does not start with "r" then
	open workbook workbook file name whereisgb & in07
	save active workbook in (fileout07) as text Mac file format with overwrite
	close active workbook saving no
end if


if file08 does not start with "r" then
	open workbook workbook file name whereisgb & in08
	save active workbook in (fileout08) as text Mac file format with overwrite
	close active workbook saving no
end if


if file09 does not start with "r" then
	open workbook workbook file name whereisgb & in09
	save active workbook in (fileout09) as text Mac file format with overwrite
	close active workbook saving no
end if


if file10 does not start with "r" then
	open workbook workbook file name whereisgb & in10
	save active workbook in (fileout10) as text Mac file format with overwrite
	close active workbook saving no
end if


quit

end tell

tell application “Fetch”
activate
make new transfer window at beginning with properties {hostname:“xxxx.xxxx.edu”, username:“xxxxxx”, password:“xxxxxxxx”, initial folder:“/vault”}
if file01 does not start with “r” then put into transfer window “xxxx.xxxx.edu” item alias fileout01
if file02 does not start with “r” then put into transfer window “xxxx.xxxx.edu” item alias fileout02
if file03 does not start with “r” then put into transfer window “xxxx.xxxx.edu” item alias fileout03
if file04 does not start with “r” then put into transfer window “xxxx.xxxx.edu” item alias fileout04
if file05 does not start with “r” then put into transfer window “xxxx.xxxx.edu” item alias fileout05
if file06 does not start with “r” then put into transfer window “xxxx.xxxx.edu” item alias fileout06
if file07 does not start with “r” then put into transfer window “xxxx.xxxx.edu” item alias fileout07
if file08 does not start with “r” then put into transfer window “xxxx.xxxx.edu” item alias fileout08
if file09 does not start with “r” then put into transfer window “xxxx.xxxx.edu” item alias fileout09
if file10 does not start with “r” then put into transfer window “xxxx.xxxx.edu” item alias fileout10
try
quit
end try
end tell
tell application “Finder”
if file01 does not start with “r” then delete file fileout01
if file02 does not start with “r” then delete file fileout02
if file03 does not start with “r” then delete file fileout03
if file04 does not start with “r” then delete file fileout04
if file05 does not start with “r” then delete file fileout05
if file06 does not start with “r” then delete file fileout06
if file07 does not start with “r” then delete file fileout07
if file08 does not start with “r” then delete file fileout08
if file09 does not start with “r” then delete file fileout09
if file10 does not start with “r” then delete file fileout10
end tell

Thanks for looking over this code.

//steve

Hi steve, here is an initial attempt to shorten your code up. Since I don’t have fetch or the spreadsheet in question though it’s a little tough to do any testing =)

set userdt to "Dr. xxxx's HD:Users:faculty:Desktop:"
set whereisgb to "Dr. xxxxx's HD:Users:faculty:Documents:Gradebook:"
set gbNames to "Post GB.xls"

set theSets to {¬
	{inRef:"r", fileRef:"r", fileout:""}, ¬
	{inRef:"r", fileRef:"r", fileout:""}, ¬
	{inRef:"r", fileRef:"r", fileout:""}, ¬
	{inRef:"r", fileRef:"r", fileout:""}, ¬
	{inRef:"r", fileRef:"r", fileout:""}, ¬
	{inRef:"r", fileRef:"r", fileout:""}, ¬
	{inRef:"r", fileRef:"r", fileout:""}, ¬
	{inRef:"r", fileRef:"r", fileout:""}, ¬
	{inRef:"r", fileRef:"r", fileout:""}, ¬
	{inRef:"r", fileRef:"r", fileout:""}}

set base to 6

tell application "Microsoft Excel"
	open workbook workbook file name whereisgb & gbNames
	
	repeat with i from 1 to (count theSets)
		set counter to base + i
		if value of cell ("A" & counter) is not "" then
			tell item i of theSets
				set inRef to value of cell ("A" & counter)
				set fileRef to value of cell ("C" & counter)
				set fileout to userdt & fileRef
				tell application "System Events"
					if (exists file fileout) then
						delete file fileout
					end if
				end tell
			end tell
		end if
	end repeat
	
	close active workbook
	
	repeat with i from 1 to (count theSets)
		tell item i of theSets
			if fileRef does not start with "r" then
				open workbook workbook file name whereisgb & inRef
				save active workbook in (fileout) as text Mac file format with overwrite
				close active workbook saving no
			end if
		end tell
	end repeat
	
	quit
end tell


tell application "Fetch"
	activate
	make new transfer window at beginning with properties {hostname:"xxxx.xxxx.edu", username:"xxxxxx", password:"xxxxxxxx", initial folder:"/vault"}
	repeat with i from 1 to (count theSets)
		tell item i of theSets
			if fileRef does not start with "r" then put into transfer window "xxxx.xxxx.edu" item alias fileout
		end tell
	end repeat
	try
		quit
	end try
end tell

tell application "Finder"
	repeat with i from 1 to (count theSets)
		tell item i of theSets
			if fileRef does not start with "r" then delete file fileout
		end tell
	end repeat
end tell