使用 VBA 更改 Excel 图表的方向（纵向或横向）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9298476/
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
Changing the orientation (portrait or landscape) of an Excel chart using VBA
提问by Mike Conley
I am trying to write a macro to automatically print all the charts I have created in a workbook using another macro. (literally hundreds) The problem I'm having is that I cannot figure out how to change the graph from a portrait layout to a landscape layout using VBA. I was wondering if anyone could help me out. I tried the code bellow but it gives me an error at the line " .ChartObjects(x).PageSetup.Orientation = xlLandscape " I understand that for a chart object that this isn't the correct property but I can't figure out what else it is.
我正在尝试编写一个宏来自动打印我使用另一个宏在工作簿中创建的所有图表。（字面上数百）我遇到的问题是我无法弄清楚如何使用 VBA 将图形从纵向布局更改为横向布局。我想知道是否有人可以帮助我。我尝试了下面的代码，但它在“.ChartObjects(x).PageSetup.Orientation = xlLandscape”行给了我一个错误否则就是。
Any help would be appreciated!
任何帮助，将不胜感激！
Option Explicit
Sub Print_All_Charts()
Dim szASheet As String
szASheet = ActiveSheet.Name
Dim lChartObjCount As Long
lChartObjCount = ActiveSheet.ChartObjects.Count
With Application
.ScreenUpdating = False
.ActivePrinter = "HP Color LaserJet 5550 PS on Ne08:"
'On Error Resume Next
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
Dim x As Long
For x = 1 To lChartObjCount
With wks
.ChartObjects(x).PageSetup.Orientation = xlLandscape
.ChartObjects(x).Select
.ChartObjects(x).Activate
.PrintOut , , 1
End With
Next x
Next wks
ActiveChart.Deselect
With Sheets(szASheet)
.Select
.Range("A1").Select
End With
.ScreenUpdating = True
End With
End Sub
回答by Jean-Fran?ois Corbett
Manipulating Excel charts using VBA is always a bit confusing, because there are ChartObjectobjects and then there are Chartobjects. Each ChartObjectobject has a child Chartobject. It isn't always very intuitive which properties and methods belong to the Chartand which are to be found on its parent ChartObject. Quoting VBA help:
使用 VBA 操作 Excel 图表总是有点令人困惑，因为有ChartObject对象就有Chart对象。每个ChartObject对象都有一个子Chart对象。哪些属性和方法属于 以及哪些属性和方法Chart可以在其父级上找到并不总是很直观ChartObject。引用 VBA 帮助：
[ChartObject] represents an embedded chart on a worksheet. The ChartObjectobject acts as a container for a Chartobject. Properties and methods for the ChartObjectobject control the appearance and size of the embedded chart on the worksheet.
[ ChartObject] 表示工作表上的嵌入图表。所述ChartObject对象充当一个容器图表对象。ChartObject对象的属性和方法控制工作表上嵌入图表的外观和大小。
Reading VBA help can drive you nuts if you don't have your glasses on, because ChartObjectmeans something different than Chartobject!
如果您没有戴眼镜，阅读 VBA 帮助可能会让您发疯，因为这ChartObject意味着与Chart对象不同的东西！
Anyhow, as it turns out, .PageSetup.Orientationsits on Chartand not ChartObjectas you were inferring.
无论如何，事实证明，.PageSetup.Orientation坐下来Chart而不是ChartObject像你推断的那样。
Dim wks As Worksheet
Dim chartObject As ChartObject
For Each wks In ActiveWorkbook.Worksheets
For Each chartObject In wks.ChartObjects
.Chart.PageSetup.Orientation = xlLandscape ' or xlPortrait
.Chart.PrintOut Preview:=True, ActivePrinter:="PDFCreator"
Next
Next
This assumes that you want to print each chart on a separate page. What you were doing in your code was printing out each entire worksheet at once, but that doesn't seem to square with the rest of your question.
这假设您要在单独的页面上打印每个图表。您在代码中所做的是一次打印出整个工作表，但这似乎与您的其余问题不符。
Here I used PDFCreator as my printer, because I didn't want to waste a bunch of paper while testing this code. You can of course adjust this as you see fit.
这里我使用 PDFCreator 作为我的打印机，因为我不想在测试这段代码时浪费一堆纸。您当然可以根据需要调整它。
Also I set the active printer at the time of printing. Of course you can also use Application.ActivePrinterinstead, but that will affect the active printer even when the macro is done running.
我还在打印时设置了活动打印机。当然，您也可以Application.ActivePrinter改用，但即使宏已完成运行，这也会影响活动打印机。
