none
Page Breaks moving with Printer Selection RRS feed

  • Question

  • I've asked this question every time I move from one version of office to another and not had a solution in the past 8-10 years, but here goes again.

    If I want to format a page in Excel so that when I save as PDF it creates an 'almost' borderless output, I have always had to use VBA to temporarily swap out the users active printer, with whatever printable space it provides, with a 'known' printer.

    A few years ago now, I used to use the MODI printer, but that has been removed.

    I then started using the Microsoft XPS Document Writer as a pass-through but this has always been hit and miss from one user to another for some reason.

    Does anyone have a robust way of using the Save As PDF function within excel which doesn't pull the printer metrics off of the users default printer?

    I've not tried it, but what does Excel use if there is no system printer installed? Can it still produce a PDF without one?

    Any new solution would be a revelation to me, so please someone, surprise me.

    Monday, May 9, 2016 12:14 PM

Answers

  • Hi Antony J White,

    do you mean you are facing the problem that when you save as pdf it will not add border and you want some space.

    you can use the macro below to save your worksheet in PDF Format.

    Sub PDFActiveSheet()
    Dim ws As Worksheet
    Dim strPath As String
    Dim myFile As Variant
    Dim strFile As String
    On Error GoTo errHandler
    
    Set ws = ActiveSheet
    
    'enter name and select folder for file
    ' start in current workbook folder
    strFile = Replace(Replace(ws.Name, " ", ""), ".", "_") _
                & "_" _
                & Format(Now(), "yyyymmdd\_hhmm") _
                & ".pdf"
    strFile = ThisWorkbook.Path & "\" & strFile
    
    myFile = Application.GetSaveAsFilename _
        (InitialFileName:=strFile, _
            FileFilter:="PDF Files (*.pdf), *.pdf", _
            Title:="Select Folder and FileName to save")
    
    If myFile <> "False" Then
        ws.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=myFile, _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=False
    
        MsgBox "PDF file has been created."
    End If
    
    exitHandler:
        Exit Sub
    errHandler:
        MsgBox "Could not create PDF file"
        Resume exitHandler
    End Sub
    

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, May 10, 2016 8:05 AM
    Moderator

All replies

  • For Excel 2007 and later versions, the ExportAsFixedFormat method is available to save in PDF format...

    expression.ExportAsFixedFormat(Type, Filename, Quality, IncludeDocProperties, IgnorePrintAreas, From, To, OpenAfterPublish, FixedFormatExtClassPtr)
    
    expression   A variable that represents a Workbook, Sheet, Chart, or Range object.
    

    Have you tried it?


    Domenic Tamburino Microsoft MVP - Excel xl-central.com - "For Your Microsoft Excel Solutions"

    Monday, May 9, 2016 6:05 PM
  • Hi Antony J White,

    do you mean you are facing the problem that when you save as pdf it will not add border and you want some space.

    you can use the macro below to save your worksheet in PDF Format.

    Sub PDFActiveSheet()
    Dim ws As Worksheet
    Dim strPath As String
    Dim myFile As Variant
    Dim strFile As String
    On Error GoTo errHandler
    
    Set ws = ActiveSheet
    
    'enter name and select folder for file
    ' start in current workbook folder
    strFile = Replace(Replace(ws.Name, " ", ""), ".", "_") _
                & "_" _
                & Format(Now(), "yyyymmdd\_hhmm") _
                & ".pdf"
    strFile = ThisWorkbook.Path & "\" & strFile
    
    myFile = Application.GetSaveAsFilename _
        (InitialFileName:=strFile, _
            FileFilter:="PDF Files (*.pdf), *.pdf", _
            Title:="Select Folder and FileName to save")
    
    If myFile <> "False" Then
        ws.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=myFile, _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=False
    
        MsgBox "PDF file has been created."
    End If
    
    exitHandler:
        Exit Sub
    errHandler:
        MsgBox "Could not create PDF file"
        Resume exitHandler
    End Sub
    

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, May 10, 2016 8:05 AM
    Moderator