Databases
StackOverflow2013
Postsdbo
POVBA Sendkeys loop for print options
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POVBA Sendkeys loop for print options
    primarykey
    data
    text
    <p>Update: @Blackhawk provided me with the piece of information I needed to answer the question - scroll to the bottom for a working solution.</p> <p>We've had some annoying new print software installed on our systems which requires me to select each worksheet in an Excel workbook and set the print options (i.e. landscape, page size, collate preference, colour etc) on it seperately. Support seem to think this is perfectly acceptable, and aren't much use. There are two work arounds that I've found, one is to save the selected sheets as a .pdf and then print (only needing to select print options once) and the other is to use sendkeys. From reading I can see that sendkeys are not a good choice, so if I have to go the .pdf route I will but this definately isn't ideal as my end users prefer .xls files because it means they can filter data etc. Because of that, I've written some VBA that will currently allow me to quickly set print options for one worksheet in excel. </p> <pre><code>Sub A00_Sendkeystest() ' Application.Wait (Now() + TimeValue("00:00:01")) Application.SendKeys ("%p"), True 'Selects Page Layout Application.SendKeys ("%i"), True 'Selects Print Titles Application.SendKeys ("%o"), True 'Selects Options Application.SendKeys ("%f"), True 'Selects profile Application.SendKeys ("l"), True 'Selects 'Landscape' default (this needs to be set up initially) Application.SendKeys "{TAB 14}", True 'Tabs to OK Application.SendKeys "~", True 'Hits enter to close screen Application.SendKeys "{TAB 11}", True 'Tabs to OK Application.SendKeys "~", True 'Hits enter to close screen End Sub </code></pre> <p>This runs quite nicely when assigned to a shortcut, so I can run it quite quickly against one sheet in a workbook and it works as required. However, what I'd really like to do is write the vba to loop through the sheets I've selected for printing and run the sendkeys code against each worksheet. I've tried the following:</p> <pre><code>Sub cycle() Dim ws As Worksheet For Each ws In ActiveWindow.SelectedSheets ws.Activate 'Application.Wait (Now() + TimeValue("00:00:10")) Application.SendKeys ("%p") ', True 'Selects Page Layout Application.SendKeys ("%i") ', True 'Selects Print Titles Application.SendKeys ("%o") ', True 'Selects Options Application.SendKeys ("%f") ', True 'Selects profile Application.SendKeys ("l") ', True 'Selects 'Landscape' default (this needs to be set up initially) Application.SendKeys "{TAB 14}" ', True 'Tabs to OK Application.SendKeys "~" ', True 'Hits enter to close screen Application.SendKeys "{TAB 11}" ', True 'Tabs to OK Application.SendKeys "~" ', True 'Hits enter to close screen Application.Wait (Now() + TimeValue("00:00:03")) Next End Sub </code></pre> <p>However, when watching the macro run, it cycles through each page and then just as the macro ends it appears to try to execute the sendkeys section 4 times (which doesn't work of course). I've tried building in a delay but it's literally like the application.sendkeys part of the code only executes just before the macro ends. I'm using Office 2010 with Windows 7, but any suggestions on how to get this to work (or any alternative ideas!) would be much appreciated. </p> <p>Thanks</p> <p>Final code:</p> <pre><code>Sub cycle() 'Macro will cycle through selected sheets and select landscape_printing profile in print options. Application.ScreenUpdating = False Dim ws As Worksheet For Each ws In ActiveWindow.SelectedSheets ws.Activate Application.SendKeys ("%p"), True 'Selects Page Layout Application.SendKeys ("%i"), True 'Selects Print Titles Application.SendKeys ("%o"), True 'Selects Options Application.SendKeys ("%f"), True 'Selects profile Application.SendKeys ("l"), True 'Selects 'Landscape' default (this needs to be set up initially) Application.SendKeys "{TAB 14}", True 'Tabs to OK Application.Wait (Now() + TimeValue("00:00:01")) Application.SendKeys "~", True 'Hits enter to close screen Application.Wait (Now() + TimeValue("00:00:01")) Application.SendKeys "~", True 'Hits enter to close screen DoEvents Next Application.ScreenUpdating = True MsgBox "Completed." End Sub </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USbawpie
    1. USbawpie
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COAlso the first two sendkeys lines can be replaced by this single line `Application.CommandBars.ExecuteMso ("PrintTitles")`
      singulars
      1. POVBA Sendkeys loop for print options
      1. USSiddharth Rout
    2. CO@SiddharthRout thanks for this, I can see it works, but for some reason when using this the rest of my macro doesn't quite run in the manner I want it to, so I've left it out for now.
      singulars
      1. POVBA Sendkeys loop for print options
      1. USbawpie
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload