PDA

View Full Version : [SOLVED] xl 2000 Page Setup User Defined Paper Size



borody
12-05-2004, 04:06 PM
Hi
User Defined Paper Size can be manually set up through
File PageSetup Options Paper Custom UserDefined Size Width Height

But when i use the Macro Recorder to record a macro doing the above actions, the part about the User Defined Paper size is not recorded in the macro.

In the xl 2000 help files under Papersize Property, xlPaperUser is shown as a User-defined xl Papersize constant. But there is no indication of how it can be used in a macro to set the paper size.

With ActiveSheet.PageSetup
Worksheets("Sheet1").PageSetup.PaperSize = xlpaperuser

How to set the paper size for xlPaperUser ?

Can anyone give me an idea how to do set the page width and page height for xlpaperuser or any link to find some info on how to do this?

Thanks
borody

Killian
12-06-2004, 10:00 AM
I can't find any reference to this but the way I understand it, the reason the macro recorder doesn't record the paper setting is because they are not part of the Excel object model. The furthest extent of Excel's control is to set the paper size to xlUser, meaning that it will use the Custom setting you last enter in the printer's control panel.
The custom paper size itself is a system setting (specifically of the print driver).
I think it should be possible to use a Windows API call to do this... I'll have to check that out later since I'm on a client's site and can't access system settings with their set up.
Maybe you could search around for Windows API printer calls from VBA and I'll check in again tomorrow...

Best of luck
K :-)

borody
12-06-2004, 04:49 PM
when i used xlpaperuser in the macro, the macro stops with an error at the line with xlpaperuser.

in ms word there is the page setup with paper size and custom size for page width and page length setting which gets recorded in the macro recorder and works well. for some reason this option is missing in excel.

i did find a post by someone with the same exact problem as mine. am not yet sure how it works.



http://www.mrexcel.com/board2/viewtopic.php?t=2781&highlight=xlpaperuser
Thanks so much for trying to help me, i still didnt get it to work with this method. I do have the paper type defined and it is called "Epson Dot Matrix" and i tried xlPaperUser and that doesnt work either. I did some extensive searching and found this method:
it uses the sendkeys to simulate a user doing it! heres the code:
Sub paperchange()
xSource = "ep"
SendKeys "%fu{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}" & xSource & "~~"
End Sub

about the windows api calls i dont have much bbackground. but any suggestions are welcome

thanks

borody

Ken Puls
12-06-2004, 06:02 PM
Hi Borody,

Just to let you know, I edited your QUOTE keys so that they render properly. In future, if you surround that word with [], and [/ ] they should work correctly.

Cheers,

Killian
12-07-2004, 02:47 AM
Hi again,
you can change the page setup in Word because word docs have a physical page size. Execl doesn't. When you print in Excel, you send whatever is in the contents of your print area, which you can set in VBA, to fit in the page size set at the printer, which you can't. I would guess though, that if you set your custom size in your printer settings first (maybe print a pge using them too), then use xlpaperuser from your macro, it should work. If not, what is the error message you're getting?
The code you found is an interesting solution, which you could use. Get the printer dialog up with VBA and use sendkeys to TAB to the right place on the form and enter your custom sizes.
Not the last word in elegant code but if it gets the job done...