excel vba 未正确将页面设置导出为 pdf
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24862064/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
excel vba not exporting pagesetup to pdf correctly
提问by rohrl77
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.
如果我手动导出并将其另存为 pdf，那么它会使用正确的页面设置，生成横向的单页 PDF。但是，由 VBA 完成的相同导出会生成一个长达几页的纵向 PDF。
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
采纳答案by rohrl77
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.
我需要将 Application.PrintCommunication 部分添加到等式中。无论出于何种原因，如果我在没有它的情况下运行代码，Excel 都会覆盖我放置的设置。
回答by CaptainABC
I think the problem is that you need to add the .Zoom = Falseto your page setup code:
我认为问题在于您需要将.Zoom = False加到您的页面设置代码中：
'Format Print Area to one Page
With ActiveSheet.PageSetup
.PrintArea = Worksheets(ReportWsName).UsedRange
.Orientation = xlLandscape
.FitToPagesWide = 1
.Zoom = False 'I have added this line
End With
From what I have tried this should solve it for you.
根据我的尝试，这应该可以为您解决。
Let me know how it goes!
让我知道事情的后续！
EDIT:Maybe you need:
编辑：也许你需要：
'Format Print Area to one Page
With ActiveSheet.PageSetup
.PrintArea = Worksheets(ReportWsName).UsedRange
.Orientation = xlLandscape
.FitToPagesWide = 1
.FitToPagesTall = 1
.Zoom = False 'I have added this line
End With
EDIT2:What if you changed:
EDIT2：如果你改变了：
.PrintArea = Worksheets(ReportWsName).UsedRange
.PrintArea = Worksheets(ReportWsName).UsedRange
To
到
.PrintArea = Worksheets(ReportWsName).UsedRange.Address
.PrintArea = Worksheets(ReportWsName).UsedRange.Address
回答by Chema Vascuence
Yes!!!, I have had the same problem: I was not able to export a sheet with the page Setup settings already applied on it.
是的！！！，我遇到了同样的问题：我无法导出已应用页面设置设置的工作表。
Before trying the Application.PrintCommunication I tested Wait and Sleep commands without success. Finally I skipped this issue by using CopyPicture method, adding a chart page and then exporting it to pdf, but resolution in my pdf it was not fine and I was not able to play with margins.
在尝试 Application.PrintCommunication 之前，我测试了 Wait 和 Sleep 命令但没有成功。最后，我通过使用 CopyPicture 方法跳过了这个问题，添加了一个图表页面，然后将其导出为 pdf，但是我的 pdf 中的分辨率不好，我无法使用边距进行播放。
So just add Application.PrintCommunication=false before your code , on pagesetup settings like CaptainABC says and most important: close with Application.PrintCommunication=true after the code.
因此，只需在您的代码之前添加 Application.PrintCommunication=false ，在像 CaptainABC 所说的页面设置设置上，最重要的是：在代码之后使用 Application.PrintCommunication=true 关闭。
Thank you for this useful post.
谢谢你这篇有用的帖子。
