Need help coding macro to format page size

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.

Sub PDFActiveSheet()

    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


   Dim ThisWorkbookName As String
   Dim ThisWorkbookPath As String
   Dim PDFFile As String
   
   On Error GoTo ErrHandler
   
    frmCertOptions.Show
    If frmCertOptions.mCancelled = True Then Exit Sub
    
    ThisWorkbookName = Replace(Excel.ActiveWorkbook.Name, ".xlsm", ".pdf")
    ThisWorkbookPath = Excel.ActiveWorkbook.Path
    PDFFile = ThisWorkbookPath & "\" & ThisWorkbookName
  
    Application.ScreenUpdating = False
    Rows("1:5").Select
    Selection.EntireRow.Hidden = True
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        PDFFile, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        True
    
    ActiveWindow.SmallScroll Down:=-3
    Cells.Select
    Range("A6").Activate
    Selection.EntireRow.Hidden = False
    Range("B17:D17").Select
    
    Application.ScreenUpdating = True


Exit Sub


ErrHandler:
    Application.ScreenUpdating = True
    MsgBox "An error occured while attempting to create file: " & PDFFile & ". Make sure the file is not already open."


End Sub

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.

You could try

Rows("1:5").EntireRow.Hidden = True
With ActiveSheet.PageSetup
           .PaperSize = xlPaperA4
End With
    ActiveSheet.ExportAsFixedFormat

and see if this helps.

Alf

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

Public Sub MAKE_PDF()

   Dim ThisWorkbookName As String
   Dim ThisWorkbookPath As String
   Dim PDFFile As String
   
    On Error GoTo ErrHandler
    
    ThisWorkbookName = Replace(Excel.ActiveWorkbook.Name, ".xlsm", ".pdf")
    PDFFile = Application.GetSaveAsFilename(InitialFileName:=ThisWorkbookName, FileFilter:="adobe (*.pdf), *.pdf")
 
    Application.ScreenUpdating = False
    With ActiveSheet.PageSetup.PaperSize = xlPaperA4
    End With
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        PDFFile, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        True
    
    Cells.Select
    Range("A1:R62").Activate
    Selection.EntireRow.Hidden = False
    Range("A1:R62").Select
    
    Application.ScreenUpdating = True

Exit Sub

ErrHandler:
    Application.ScreenUpdating = True
    MsgBox "An error occured while attempting to create file: " & PDFFile & ". Make sure the file is not already open."

End Sub

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

With ActiveSheet.PageSetup
      .PaperSize = xlPaperA4
End With

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

Alf

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

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?

With ActiveSheet.PageSetup
	.Zoom = False
	.FitToPagesWide = 1
	.FitToPagesTall = 1
End With

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

Alf

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

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

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

VBA_Help.xlsm

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

Alf

I really appreciate the help!

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

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

Capture_3.PNG

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

With ActiveSheet.PageSetup
	.Zoom = True
	.FitToPagesWide = 8.3
	.FitToPagesTall = 11.7
End With

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

Alf

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

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

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

Thank you for helping. I appreciate your time.

You are welcome.

Thanks for feedback and rep

Alf

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!

Public Sub MAKE_PDF()

    Application.Dialogs(xlDialogPrinterSetup).Show

   Dim ThisWorkbookName As String
   Dim ThisWorkbookPath As String
   Dim PDFFile As String
    
    ThisWorkbookName = Replace(Excel.ActiveWorkbook.Name, ".xlsm", ".pdf")
    PDFFile = Application.GetSaveAsFilename(InitialFileName:=ThisWorkbookName, FileFilter:="adobe (*.pdf), *.pdf")
 
    Application.ScreenUpdating = False
    With ActiveSheet.PageSetup
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
    End With
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        PDFFile, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        True
    
    Cells.Select
    Range("A1:R62").Activate
    Range("A1:R62").Select
    
    Application.ScreenUpdating = True


End Sub

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

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

Alf