none
Wrong margin when convert a workbook to pdf using ExportAsFixedFormat RRS feed

  • Question

  • I have a workbook with 4 worksheets. The first three worksheets have a landscape orientation, and the last one has a portrait orientation. All of the 4 worksheets have a left margin of 0.4 inch. As I convert this workbook to pdf using ExportAsFixedFormat in c#, the pdf page of the last worksheet has a wrong left margin. I find the left margin in the pdf file varies as I change my PC's default printer. Is it true that ExportAsFixedFormat is affected by the default printer and how can i fix it to generate the correct margin? Thanks.
    Friday, August 7, 2015 3:21 AM

All replies

  • Since no one has jumped on this, I'll take a crack at it.  Please run this macro on your workbook.  Make sure it is in the proper/correct path/folder.

    Regards.

    Sub Convert_Excel_To_PDF()
        Dim MyPath As String, FilesInPath As String
        Dim MyFiles() As String, Fnum As Long
        Dim mybook As Workbook
        Dim CalcMode As Long
        Dim sh As Worksheet
        Dim ErrorYes As Boolean
        Dim LPosition As Integer
     
        'Fill in the path\folder where the Excel files are
        MyPath = "c:\Documents and Settings\shuerya\Desktop\ExcelFiles\"
     
        FilesInPath = Dir(MyPath & "*.xl*")
        If FilesInPath = "" Then
            MsgBox "No files found"
            Exit Sub
        End If
     
        Fnum = 0
        Do While FilesInPath <> ""
            Fnum = Fnum + 1
            ReDim Preserve MyFiles(1 To Fnum)
            MyFiles(Fnum) = FilesInPath
            FilesInPath = Dir()
        Loop
     
        With Application
            CalcMode = .Calculation
            .Calculation = xlCalculationManual
            .ScreenUpdating = False
            .EnableEvents = False
        End With
     
        If Fnum > 0 Then
            For Fnum = LBound(MyFiles) To UBound(MyFiles)
                Set mybook = Nothing
                On Error Resume Next
                Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum))
                On Error GoTo 0
     
                If Not mybook Is Nothing Then
     
                        
                        LPosition = InStr(1, mybook.Name, ".") - 1
                        mybookname = Left(mybook.Name, LPosition)
                        mybook.Activate
                        'All PDF Files get saved in the directory below:
                        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                            "C:\Documents and Settings\shuerya\Desktop\PDFFiles\" & mybookname & ".pdf", _
                            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
                            :=False, OpenAfterPublish:=False
                            
                End If
                
                mybook.Close SaveChanges:=False
     
            Next Fnum
        End If
     
        If ErrorYes = True Then
            MsgBox "There are problems in one or more files, possible problem:" _
                 & vbNewLine & "protected workbook/sheet or a sheet/range that not exist"
        End If
     
        With Application
            .ScreenUpdating = True
            .EnableEvents = True
            .Calculation = CalcMode
        End With
    End Sub
    


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Saturday, August 8, 2015 2:25 AM
  • Hi FredFu2015,

    First, please refer to ryguy72’s reply.

    Secondly, what’s the result if you export workbook manually?

    Regards

    Starain


    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.

    Monday, August 10, 2015 8:35 AM
    Moderator
  • Thanks for your reply ryguy72.

    I tried to convert the workbook (i.e. 4 worksheets all together), but not the active worksheet. Yep, the margin is correct when i convert a single worksheet with your code, but it gets wrong when convert the whole wookbook.

    Could you replace "ActiveSheet.ExportAsFixedFormat" with "mybook.ExportAsFixedFormat" and try it again. Thanks.

    Monday, August 31, 2015 8:33 AM
  • Thanks for your reply Starain. I can reproduce the bug when manually export the workbook.

    Say, select the 4 worksheets at first, then go to the export menu.

    So sorry that i cannot submit the screen shots. My account has not been verified.

    Monday, August 31, 2015 8:40 AM
  • Hi,

    You could share the picture on the OneDrive, then post the link here.

    Regards

    Starain


    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, September 1, 2015 1:15 AM
    Moderator