[RESOLVED] Display the File|Print Window

[MartinLiss]

I would like to use VBA to go to what's shown in the picture. Is it possible? I've tried both Application.Dialogs(xlDialogPrint).Show and Application.Dialogs(xlDialogPageSetup).Show but neither one does what I want.

[MartinLiss]

Sendkeys of ctrl+P should work but I haven't been successful yet.

[TnTinMN]

Try:

Application.CommandBars.FindControl(ID:=4).execute

See this post for sources for the ID value. http://www.vbforums.com/showthread.p...=1#post4624945

[MartinLiss]

Unfortunately that gets me this.

[MartinLiss]

OMG Application.SendKeys "^{P}" doesn't work but Application.SendKeys "^{p}" does!

I'll leave this open in the hope that there's a non-sendkeys option.

[TnTinMN]

Unfortunately that gets me this.

Well I just learned a bit about the newer office versions that I do not have (still on 2007). The menu that you wish to access is call "Backstage view" and it appears to be an extension of the ribbon. Using the references I pointed to previously, there are these controls that include the word "Print" and are listed belonging to Backstage view.

Control Name	ID
GroupPrintPreview	19140
GroupPrintProtectedView	19094
GroupPrintSettings	19139
TabPrint	18244

I would start with the "TabPrint" id first and try:

Application.CommandBars.FindControl(ID:=18244).Execute

[MartinLiss]

Sorry, no, it's out-of-the-box Excel 2010.

[MartinLiss]

End that code produces an Object variable or With block... error

[TnTinMN]

Sorry, no, it's out-of-the-box Excel 2010.

This is what I was writing about: http://www.excel-2010.com/backstage-...ft-excel-2010/

[MartinLiss]

OK, you learn something new every day, but as I mentioned the code produces an error.

[TnTinMN]

OK, you learn something new every day, but as I mentioned the code produces an error.

Well it was worth a try. It looks like this probably will require code to access the ribbon. I have never done that, but I just found this article (with code) on how to control the ribbon. It is for controlling the ribbon in MS Word, but I would assume that the same techniques will work in Excel.

No magic bullet, but it appears to be doable.

[TakneItEasy]

I found this in the Office Dev Center that may not show the window but changes the settings. Hopefully it can get you pointed in the right direction.

Excel 2010: Communicate with PageSetup Using the Excel.PrintCommunication Method

Also this on the office support page:

Office Excel 2010 crashes when you programmatically print or print-preview a file

I've seen the window referred to as "PrintPreview" and also "PageSetup" after snooping around.

Correction: PageSetup is a different window but I think PrintPreview is correct.

If you add a PrintPreviewAndPrint button to the QuickAccess tool bar, it gets you to that window directly so maybe you can find some hints by accessing that tool bar through TnTinMN's resource.

[MartinLiss]

Thank you but that's similar to the code you can get if you record a macro while printing and it's not flexible. The SendKeys code takes me directly to the window and that's what I want to be able to do. If there's no other solution then I'm happy enough with that.

[TakneItEasy]

Good to know, I tried recording a macro but I'm on 2010 so it didn't show anything.

BTW, with regards to using "^{P}" not working, I think sending capital letters is interpreted as adding a shift key.

[MartinLiss]

Yes, thank you I figured out that it was interpreting it as ctrl+shift+p.

[RobDog888]

Here you go Martin:

Application.CommandBars.ExecuteMso("PrintPreviewAndPrint")

[MartinLiss]

Thanks Rob, that works!

[MartinLiss]

Rob, while it works for Excel 2010, neither sendkeys nor your approach works in Excel 2007. Do you know a way to show the printpreview window in that version?

[RobDog888]

I dont have 2007 at work but I believe this was new for Excel 2010.

Edit: Yes for Excel its 2010+

[MartinLiss]

Thanks. TnTinMN's approach works for 2007.

[RobDog888]

No prob. But you actually see the backstage view in Excel 07?

http://books.google.com/books?id=qvz...202007&f=false

[MartinLiss]

No, I wasn't saying that. It just takes me to the screen he showed in post #6, which, for lack of anything better, is OK.