I am a little trouble with understanding the various filename commands and what I should do to open a workbook, work on it, and then save it, and close it. It seems to get stuck in the various layers of the tell blocks, and you seem to have to be very specific with saving a worksheet, rather than just saving the whole workbook at the end of the activity.
Also, on a separate note, I suspect that I am doing something wrong if somewhere in my process I get a warning message in Excel that ‘Data Connections have been disabled’. Can someone explain what this means?
kindest regards,
DDHawk
tell application "Microsoft Excel"
open workbook workbook file name "Macintosh HD:Users:Fred:Documents:LessonInfo.xlsx"
activate
tell worksheet "Lessons" of active workbook
activate
repeat with oneEvent in myChoice
set eventID to text -36 thru -1 of oneEvent
try
set rangeToSearchA to get range "TableHawk"
set cellChoice to (find rangeToSearchA what eventID)
set myIndex to first row index of cellChoice
delete row myIndex
end try
end repeat
sort range "TableHawk" key1 (range "B1") order1 sort ascending key2 (range "C1") order2 sort ascending header header yes
save in "Macintosh HD:Users:Fred:Documents:LessonInfo.xlsx"
end tell
close workbook "LessonInfo.xlsx" saving in "Macintosh HD:Users:Fred:Documents:LessonInfo.xlsx"
end tell
If you want to save it to a different location (or with a different name) the Save Workbook As command should be used.
Note that this command closes the original workbook and leaves the workbook with the saved-as name open.
tell application "Microsoft Excel"
set myWorkbook to open workbook workbook file name "Macintosh HD:Users:merickson:Desktop:Workbook1.xlsm"
-- do some stuff
set value of range "B2" of sheet 1 of myWorkbook to (get value of range "B2" of sheet 1 of myWorkbook) + 1
-- save to the same location
save myWorkbook
-- do more stuff
set value of range "B1" of sheet 1 of myWorkbook to "Hello"
-- close the workbook, saving contents
close myWorkbook saving yes
end tell
I have gone through my whole script and altered the open/save/close statements as you suggested, and this makes perfect sense.
However, I am still left with two questions from my script:
Firstly, I still do not understand what is the issue over the ‘Data Connections have been disabled’ message in Excel?
Secondly, I am having a problem with the sort areas of my code that it is not actually sorting by the second column any more - is there something obvious that is wrong with the code??? At the moment, I am selecting the range before and after the insertion of the new data rows.
regards, DDHawk
else if lessonNumber = "2" then
tell worksheet "Lessons" of active workbook
select range "TableHawk"
insert into range range "a3:h3" shift shift down
set value of cell "a3" to (theNickname)
set value of cell "b3" to (dateString2)
set value of cell "c3" to (timeString)
set value of cell "d3" to (myNewDate45)
set value of cell "e3" to (myNewTime45)
set value of cell "f3" to (theAnswer2)
set value of cell "g3" to (eventID1)
set value of cell "h3" to (finalPrice)
insert into range range "a3:h3" shift shift down
set value of cell "a3" to (theNickname)
set value of cell "b3" to (myNewDate45)
set value of cell "c3" to (myNewTime45)
set value of cell "d3" to (myNewDate90)
set value of cell "e3" to (myNewTime90)
set value of cell "f3" to (theAnswer2a)
set value of cell "g3" to (eventID2)
set value of cell "h3" to (finalPrice)
select range "TableHawk"
sort range "TableHawk" key1 (range "B1") order1 sort ascending key2 (range "C1") order2 sort ascending header header yes
save myWorkbook
end tell
close myWorkbook saving yes
I’m not sure what the Data Connection issue might be.
are there any other (open) workbooks that refer to the file being saved?
Does the saved file conntain any Querries or other approaches to fetching data from other files?
If you are just trying to aviod seeing the warning, you could set the Display Alerts property of the Applicaton to false.
NOTE: this persists, so it MUST be set back to true after you’ve set it false.