Monday, March 1, 2021

#1 2021-02-17 05:36:07 pm

MitchBVI
Member
Registered: 2006-02-14
Posts: 235

Clumsy code for Excel

I use the "page setup" object to determine print instructions  but I cannot get a couple of instructions to work and have a clumsy workaround. Wondered if anyone had any idea on what I am doing incorrectly.

This code works.
[applescript]tell application "System Events"
    tell process "Excel"
        click menu item "Page Setup..." of menu 1 of menu bar item "File" of menu bar 1
        tell application "Microsoft Excel" to activate
        click radio button "Landscape" of tab group 1 of window "Page Setup"
        click radio button "Fit to:" of radio group 1 of tab group 1 of window "Page Setup"
        set value of text field 2 of tab group 1 of window "Page Setup" to "1"
        set value of text field 3 of tab group 1 of window "Page Setup" to "99"
        click button "OK" of window "Page Setup"
    end tell
end tell
[/AppleScript]

This changes the orientation to landscape but does not change the the pages to wide or high.

[applescript]tell application "Microsoft Excel"
    activate
    tell page setup object of active sheet of active workbook
        set page orientation to landscape
        set fit to pages wide to 1
        set fit to pages tall to 99
    end tell
end tell
[/AppleScript]

Thanks Peter

Online

 

#2 2021-02-18 02:01:30 pm

roosterboy
Member
Registered: 2010-04-19
Posts: 33

Re: Clumsy code for Excel

You need to set the zoom property to tell Excel that you want to use the two fit to pages properties.

Applescript:


tell application "Microsoft Excel"
   activate
   tell page setup object of active sheet of active workbook
       set page orientation to landscape
       set zoom to false
       set fit to pages wide to 1
       set fit to pages tall to 99
   end tell
end tell

Last edited by roosterboy (2021-02-18 02:03:20 pm)

Offline

 

#3 2021-02-19 03:31:00 pm

MitchBVI
Member
Registered: 2006-02-14
Posts: 235

Re: Clumsy code for Excel

Thanks so simple I had deleted zoom instead of setting it to false, it takes a while for a slow learner.

Forgive me for asking another question same subject different part. Can I set the print area to used range . Sometimes I have to print a subset of the used range and then the used range. What happens is that having set the area to print it remains. At present the only way I can find of clearing the previous section is

[AppleScript]
[tell application "System Events"
        tell process "Excel"
            click menu item "Clear Print Area" of menu 1 of menu item "Print Area" of menu 1 of menu bar item "File" of menu bar 1
        end tell
    end tell
[/AppleScript]

Peter

Online

 

#4 2021-02-19 04:11:27 pm

MitchBVI
Member
Registered: 2006-02-14
Posts: 235

Re: Clumsy code for Excel

Sorry too quick "set print area to """  does the job under page object setup.

However if I may can I set the rows and columns to be repeated  on  page object setup?

Thank you

Online

 

#5 2021-02-20 09:49:00 pm

roosterboy
Member
Registered: 2010-04-19
Posts: 33

Re: Clumsy code for Excel

You can do it like this:

Applescript:


tell application "Microsoft Excel"
   
   tell page setup object of active sheet of active workbook
       
       set print title rows to "$A$1:$Q$1" -- set to "" to clear
       set print title columns to "$A:$A" -- set to "" to clear
       
   end tell
   
end tell

Offline

 

#6 2021-02-21 03:14:42 am

Nigel Garvey
Moderator
From:: Warwickshire, England
Registered: 2002-11-20
Posts: 5357

Re: Clumsy code for Excel

Hi MitchBVI.

Don't forget that MacScripter's [applescript] and [/applescript] tags are lower case. You can see that mixed case doesn't work when you read your own posts.

https://macscripter.net/viewtopic.php?p … 31#p205231


NG

Offline

 

#7 2021-02-21 11:13:26 am

MitchBVI
Member
Registered: 2006-02-14
Posts: 235

Re: Clumsy code for Excel

Thank you both very much.

Online

 

Board footer

Powered by FluxBB

RSS (new topics) RSS (active topics)