Dear All,
I seem to be having another problem with toggling between two different open Excel workbooks. I have identified the worksheets, and they have both opened early in the AppleScript that I have written. When I am coming to toggle from one to the other, the log shows that it has happened, but the new worksheet which I am creating is being placed in the wrong workbook. I cannot fathom why this is happening at all.
The following AppleScript is working…
property myWorkbook1 : "Macintosh HD:Users:.......:ChrisJoBusiness160131.xlsb"
property myWorkbook : "Macintosh HD:Users:.........:LessonInfo160131.xlsx"
tell application "Microsoft Excel"
set tmpList to name of every document
if tmpList does not contain myWorkbook1 then
open myWorkbook1
end if
if tmpList does not contain myWorkbook then
open myWorkbook
end if
end tell
The next AppleScript is showing the subsequent log:
.......
end tell
save myWorkbook
set v to 1
tell workbook myWorkbook1
activate
repeat until v is ((count of every item of choiceSet) + 1)
set mon1 to monValue
set year1 to yearValue
set mySampleDate to dateString
if ((theLocation = "Woking") or (theLocation = "Solihull")) then
if (exists (worksheet ("UK" & mon1 & year1 & ""))) is false then
copy worksheet sheet "Template" after last sheet
set name of last sheet to ("UK" & mon1 & year1 & "")
end if
activate object worksheet ("UK" & mon1 & year1 & "")
Log…
save "Macintosh HD:Users:.....:[b]LessonInfo160131.xlsx[/b]"
activate
exists worksheet "UKSep17" of workbook "Macintosh HD:Users:......:[b]ChrisJoBusiness160131.xlsb[/b]"
copy worksheet sheet "Template" of workbook "Macintosh HD:Users:.....:[b]ChrisJoBusiness160131.xlsb[/b]" after last sheet of workbook "Macintosh HD:Users:.......:[b]ChrisJoBusiness160131.xlsb[/b]"
set name of last sheet of workbook "Macintosh HD:Users:......:ChrisJoBusiness160131.xlsb" to "UKSep17"
activate object worksheet "UKSep17" of workbook "Macintosh HD:Users:......:ChrisJoBusiness160131.xlsb"
This log seems clear, except that the new worksheet is being opened and place inside of the LessonInfo workbook instead.
What is happening here???
Kindest regards…DDHawk
Model: MacBook Pro
AppleScript: 2.9
Browser: Safari 602.3.12
Operating System: Mac OS X (10.10)
I have managed to get the AppleScript to work, but I am not sure about the logic behind this. If anyone can explain precisely, it would be appreciated.
I formed to explicit references to workbook addresses. In the words, I used the full Macintosh HD:Users:…:Workbook.xlsb kind of URL to refer to the workbook with the logical thought process that if I told the AppleScript to open it, or activate it at any point, I could just point to this URL. This did not work. It seemed that if the application is already open, and the workbook is already open, you only need the workbook name -
i.e
tell workbook “Workbook.xlsb”
Why is this the case? Why will it not accept the full URL in these circumstances?
All the best,
DDHawk
Model: MacBook Pro
AppleScript: 2.9
Browser: Safari 602.3.12
Operating System: Mac OS X (10.10)
If I remember well, for years Excel use “workbook file name” as the identifier of the full pathname of the file. Honestly I never understand why they choose this naming, “workbook file path” would have been better.
It use workbook name as an identifier of the open document which is in fact the name of the file.
“Macintosh HD:Users:…:ChrisJoBusiness160131.xlsb” is your workbook file name
“ChrisJoBusiness160131.xlsb” is your workbook name
Matter of portability, it would be fine to edit your first script as :
property myWorkbook1 : (path to desktop as text) & "ChrisJoBusiness160131.xlsb"
property myWorkbook : (path to desktop as text) & "LessonInfo160131.xlsx"
tell application "Microsoft Excel"
set tmpList to name of every document
if tmpList does not contain myWorkbook1 then
open myWorkbook1
end if
if tmpList does not contain myWorkbook then
open myWorkbook
end if
end tell
I’m not sure that defining myWorkbook1 and myWorkbook as properties is really useful.
.......
end tell
save myWorkbook
set v to 1
tell workbook myWorkbook1
activate
repeat until v is ((count of every item of choiceSet) + 1)
set mon1 to monValue
set year1 to yearValue
set mySampleDate to dateString
if ((theLocation = "Woking") or (theLocation = "Solihull")) then
--set myWorksheet to "UK" & mon1 & year1 & "" # In fact I guess that the (& "") part is useless # ADDED
set myWorksheet to "UK" & mon1 & year1 # ADDED
--if (exists (worksheet myWorksheet)) is false then
if not exists (worksheet myWorksheet) then # EDITED
copy worksheet sheet "Template" after last sheet
set name of last sheet to myWorksheet
end if
activate object worksheet myWorksheet
Yvan KOENIG running Sierra 10.12.2 in French (VALLAURIS, France) mardi 3 janvier 2017 10:51:47