Saturday, July 22, 2017

#1 2017-01-01 05:50:10 pm

DDHawk
Member
Registered: 2010-11-11
Posts: 50

Toggling between Excel workbooks problem!!

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....

Applescript:


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:

Applescript:


.......
               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:.....:LessonInfo160131.xlsx"

    activate

    exists worksheet "UKSep17" of workbook "Macintosh HD:Users:......:ChrisJoBusiness160131.xlsb"

    copy worksheet sheet "Template" of workbook "Macintosh HD:Users:.....:ChrisJoBusiness160131.xlsb" after last sheet of workbook "Macintosh HD:Users:.......:ChrisJoBusiness160131.xlsb"

    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)


Filed under: excel, workbook, Toggling

Offline

 

#2 2017-01-02 06:41:13 pm

DDHawk
Member
Registered: 2010-11-11
Posts: 50

Re: Toggling between Excel workbooks problem!!

Dear All,

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)

Offline

 

#3 2017-01-03 04:52:29 am

Yvan Koenig
Member
Registered: 2006-09-14
Posts: 3111

Re: Toggling between Excel workbooks problem!!

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 :

Applescript:

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.

Applescript:


.......
           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

Last edited by Yvan Koenig (2017-01-03 04:53:47 am)

Offline

 

Board footer

Powered by FluxBB

RSS (new topics) RSS (active topics)