Need help coding macro to format page size

[cobramonkey]

I'm a noob, in every way, and need some help!

I have a workbook that the different sheets each need to print in a different size format. One page (my certificate) needs to be printed in standard A4 format and saved as a PDF, but if I have printed another sheet (my valve tag) to our card printer, then try to save the certificate as a PDF, it saves it in the wrong size format. To manually fix this I must select the A4 printer in print screen, then save the PDF. So I'm attempting to cobble together a VBA script that will chose the correct printer, but not print, then prompt me which directory to save the PDF and then save it.

Please Login or Register  to view this content.

1.) It autosaves to the same directory as the .xlsm file it came from, I want to be able to manually designate a different folder at the point of PDF creation.


2.) The printer setup. One sheet (certificate) is printed A4 size, and another sheet is printed to the Zebra card printer, which is in a different size format. So when I print to the card printer, and then go to the certificate sheet and create a PDF, it's reformatting the size and layout to the dimensions for the card printer. The way to correct the layout is to select in the print screen the default/A4 size printer, cancel print, then save as PDF. So I added this code I found online:


Dim myprinter As String
Dim printer_name As String
printer_name = "HP LaserJet 400 M401 PCL 6"


myprinter = Application.ActivePrinter
Change_Form.PrintOut Preview:=False, ActivePrinter:=printer_name, PrintToFile:=True, PrToFileName:=PSFileName
Application.ActivePrinter = myprinter



And it errors out at the underlined line, saying "Run Time Error 424 Object Required"


What do I have wrong?


I also don't know if I combined those two codes correctly. I just pasted the above code before the PDF code.


So to recap, what I want the code to do is to format to the paper size of the HP LaserJet 400 M401 PCL 6 printer (format to that size but NOT PRINT), ask me where to save the PDF, generate the PDF and display it.



Thanks in advance for any help.

[Alf]

You could try

Please Login or Register  to view this content.

and see if this helps.

Alf

[cobramonkey]

Ok, I modified my code quite a bit. It generates the PDF successfully and asks me the directory where to save it. But the formatting still doesn't work. Here's my new code

Please Login or Register  to view this content.

[Alf]

You did not copy excactly what I wrote i.e.

Please Login or Register  to view this content.

so have another go and see if you get the A4 setting.

Alf

[cobramonkey]

I tried it again. Got my error message. So I removed my "ErrHandler" script so I could see exactly what is going wrong.
Error.PNG

[Alf]

Looking at the error report it seems to me that there is noting wrong with the syntax of the command but it can’t be executed.

I have no idea why as there is no printer involved in this, this is an Excel command that is “printed” to the pdf file.

Would you care to try another “PageSetup” command and tell me if this generate the same error?

Please Login or Register  to view this content.

This is the command for setting the print area to 1 page and should generate a pdf file of 1 page.

Alf

[cobramonkey]

So that modification did solve the VBA error, but it generated the PDF with the wrong size format. Here is an example of what I'm trying to solve.
Here is the proper size.
Capture_1.PNG

And here is what it looks like when Excel still has the incorrect printer (for that sheet) selected.
Capture_2.PNG

[Alf]

Hmmm, can you upload an excel file that have the two sheets that should be converted to pdf after removing sensitive information so I have something to run tests on?

Alf

[cobramonkey]

Here's my spreadsheet. Deleted a couple pages of sensitive info.

VBA_Help.xlsm

[Alf]

Ok, will have a go at it during the weekend.

Alf

[cobramonkey]

I really appreciate the help!

[Alf]

Ok, I had I quick go at it. I do believe you problem arise from merged cells. Since the "Certificate" has a fixed layout I just used the range A8 to S60 (just outside the merged range) for the fixed formate export to pdf.

To test go to sheet "Certificate" and run macro "ExcelToPdf", you may need to change the target folder of course. Check the result file "Cqtester.pdf" and see if this file has the right formate.

Alf

[cobramonkey]

It works until I chose the card printer then try to run the macro.

Capture_3.PNG

[Alf]

It works until I chose the card printer then try to run the macro

Just to clarify, when you run my macro as a stand alone macro works and you get the right format for the "Certificate.pdf" file??

But when you chose card printer then it don't work. I'm stil not sure how you set up things.

As the printer seems to be a problem can you not let macro create all the pdf files you need and then have a second macro that prints the hopefully properly created pdf files? Because I can't see how a printer could screw up a "finished" pdf file.

I guess I'm running out of ideas, but since the "FitToPagesWide" and "FitToPagesTall" commands did work (or seemed to work) you could check if these commands take "decimal" settings because then one could try to set the A4 size as 8.3 * 11.7

The macro part should look like this

Please Login or Register  to view this content.

but I have not much hope this would work, well at least it's worth a try.

Alf

[cobramonkey]

Yes, you are right. See these two images. When I select the default printer, I have all these paper sizes selection in the drop down menu.
A4 selection.jpg

But when I select the card printer, I have only one option for the page size.
Non A4.jpg

[cobramonkey]

Using the modified code you just suggested didn't fix it, btw.

[Alf]

just suggested didn't fix it, btw.

Well I really didn't exspect to work but

Hope springs eternal in the human breast;

As this is a printer problem and not an Excel / Pdf problem I don't think I can help you any more. Perhaps you could contact the maker of this printer and see if they have a solution for you.

Alf

[cobramonkey]

Thank you for helping. I appreciate your time.

[Alf]

You are welcome.

Thanks for feedback and rep

Alf

[cobramonkey]

Just in case anyone out there is ever trying to do this, I'm posting my code of how I fixed it. I made a printer selection popup before the PDF is generated. Works on multiple computers, I'm happy!

Please Login or Register  to view this content.

[Alf]

Such a simple solution to the problem! I'm impressed

Good of you to post the final solution, may come in handy later.

Alf