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)