excel vba不正确地将页面导出为pdf [英] excel vba not exporting pagesetup to pdf correctly

问题描述

我有一个代码，将工作表格式化到所需的设置和布局（一页宽，高景深）。当我运行代码（长宏的一部分）时，它正确地格式化pagesetup。

如果我手动导出并将其保存为pdf，那么它将使用正确的页面设置，生成一个横向的PDF页面。但是，由于VBA的出口量相同，所以产生的PDF格式是几页长的，并且是纵向的。

我不知道为什么这样做这个。我已经尝试过各种解决方案，例如在导出工作表之前选择工作表，但都无济于事。

任何帮助都不胜感激。

代码如下：

  Sub SaveAsPDF（）
表格（ReportWsName）.ExportAsFixedFormat类型：= xlTypePDF，文件名：= _ 
 [SaveFolderPath]& \& ReportWsName，Quality：= xlQualityStandard，_ 
 IncludeDocProperties：= True，IgnorePrintAreas：= False，OpenAfterPublish：= _ 
 False 
 End Sub 
  pre> 
 
 
更新：
 
 
 
用于格式化pageetup的代码（由于相当长的时间，我只添加了相关的私人子创建新报表（ProvisionCode As String，TimeFrom As Date，TimeTo As Date）
 
 ...其他代码在这里... 
 
'创建所需布局的格式报告
使用工作表（ReportWsName）
'删除不必要的数据并格式化其余
 .Range（A：B，D：D，F：G，J：M，O：O，Q：S）删除Shift：= xlToLeft 
 .Range（A：F ）.EntireColumn.AutoFit 
 .Range（C：C，E：F）。ColumnWidth = 30 
带.Range（G：G）
 .ColumnWidth = 100 
 .WrapText = True 
结束
'插入标准格式标题表单报告模板
 .Rows（1：2）。插入
 wsReportTemplate.Range（1：3）。复制.Range（A1）
 .Range（A2）=Notes Report for& ProvisionCode& （& TimeFrom&& TimeTo&）
'插入标准格式页脚表单报告模板
 wsReportTemplate.Range（A6：G7）。复制.Range A& .UsedRange.Rows.Count + 2）
'确保所有数据都是硬编码
 .UsedRange.Value = .UsedRange.Value 
'将打印区域格式化为一页
 With ActiveSheet.PageSetup 
 .PrintArea = Worksheets（ReportWsName）.UsedRange 
 .Orientation = xlLandscape 
 .FitToPagesWide = 1 
结束
结束
 
 End Sub 
  

解决方案

什么似乎是解决方案：

  Application.PrintCommunication = False 
 With ActiveSheet.PageSetup 
 .Orientation = xlLandscape 
 .Zoom = False 
'.PrintArea = Worksheets（ReportWsName）.UsedRange 
 .FitToPagesWide = 1 
'.FitToPagesTall = 1 
结束
 Application.PrintCommunication = True 
  

我需要将Application.PrintCommunication部分添加到方程式中。无论什么原因Excel会覆盖我没有运行代码的设置。

I have code which formats a worksheet to the desired setup and layout (one page wide and tall in landscape). When I run the code (part of a long macro) it formats the pagesetup correctly.

If I manually export and save it as a pdf, then it uses the correct page setup, producing a one page PDF that is in landscape. However, the same export done by VBA produces a PDF that is severalpages long and in portrait.

i can't figure out why it's doing this. i've tried various solutions such as selecting the worksheet before exporting it, but all to no avail.

Any help is appreciated.

Code looks like this:

Sub SaveAsPDF()
Sheets(ReportWsName).ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        [SaveFolderPath] & "\" & ReportWsName, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        False
End Sub

UPDATE:

Code used to format the pagesetup (since it's rather long I am only adding the relevant section of that sub)

Private Sub CreateNewReport(ProvisionCode As String, TimeFrom As Date, TimeTo As Date)

... other code here...

'Format report to create the desired layout
With Worksheets(ReportWsName)
    'Delete unnecessary data and format the rest
    .Range("A:B,D:D,F:G,J:M,O:O,Q:S").Delete Shift:=xlToLeft
    .Range("A:F").EntireColumn.AutoFit
    .Range("C:C, E:F").ColumnWidth = 30
    With .Range("G:G")
        .ColumnWidth = 100
        .WrapText = True
    End With
    'Insert standard formating header form Reporting template
    .Rows("1:2").Insert
    wsReportTemplate.Range("1:3").Copy .Range("A1")
    .Range("A2") = "Notes Report for " & ProvisionCode & " (" & TimeFrom & " - " & TimeTo & ")"
    'Insert standard formating footer form Reporting template
    wsReportTemplate.Range("A6:G7").Copy .Range("A" & .UsedRange.Rows.Count + 2)
    'Ensure all data is hard coded
    .UsedRange.Value = .UsedRange.Value
    'Format Print Area to one Page
    With ActiveSheet.PageSetup
        .PrintArea = Worksheets(ReportWsName).UsedRange
        .Orientation = xlLandscape
        .FitToPagesWide = 1
    End With
End With

End Sub

解决方案

I have found what seems to be the solution:

Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .Orientation = xlLandscape
        .Zoom = False
        '.PrintArea = Worksheets(ReportWsName).UsedRange
        .FitToPagesWide = 1
        '.FitToPagesTall = 1
    End With
Application.PrintCommunication = True

I needed to add the Application.PrintCommunication part to the equation. For whatever reason Excel would overwrite the settings I was putting if I ran the code without it.

这篇关于excel vba不正确地将页面导出为pdf的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！