使用VBA更改Excel图表的方向（纵向或横向） [英] Changing the orientation (portrait or landscape) of an Excel chart using VBA

问题描述

我想写一个宏来自动打印我使用另一个宏在工作簿中创建的所有图表。 （数百）我遇到的问题是，我不知道如何将图形从纵向布局更改为使用VBA的横向布局。我想知道是否有人可以帮助我。我试着下面的代码，但它给我一个错误在行.ChartObjects（x）.PageSetup.Orientation = xlLandscape我明白，对于图表对象，这是不正确的属性，但我不能弄清楚什么否则就是。

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.

任何帮助将不胜感激！

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

推荐答案

使用VBA操作Excel图表总是有点混乱，因为 ChartObject 对象，然后有 Chart 对象。每个 ChartObject 对象都有一个子图对象。并不总是非常直观的哪些属性和方法属于 Chart ，并且它的父 ChartObject 。引用VBA帮助：

Manipulating Excel charts using VBA is always a bit confusing, because there are ChartObject objects and then there are Chart objects. Each ChartObject object has a child Chart object. It isn't always very intuitive which properties and methods belong to the Chart and which are to be found on its parent ChartObject. Quoting VBA help:

[ ChartObject ]表示工作表上的嵌入图表。 ChartObject 对象充当图表对象的容器。 ChartObject 对象的属性和方法控制工作表上嵌入图表的外观和大小。

[ChartObject] represents an embedded chart on a worksheet. The ChartObject object acts as a container for a Chart object. Properties and methods for the ChartObject object control the appearance and size of the embedded chart on the worksheet.

如果没有眼镜，阅读VBA帮助可以驱动你，因为 ChartObject 表示不同于图对象的对象！

Reading VBA help can drive you nuts if you don't have your glasses on, because ChartObject means something different than Chart object!

无论如何， $ c> .PageSetup.Orientation 坐在图表而不是 ChartObject 。

Anyhow, as it turns out, .PageSetup.Orientation sits on Chart and not ChartObject as you were inferring.

    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.

这里我使用PDFCreator作为我的打印机，因为我不想浪费一大堆纸，而测试这个代码。你当然可以调整这个，你认为合适。

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.

此外，我在打印时设置了活动打印机。当然，您也可以使用 Application.ActivePrinter ，但这将影响活动打印机，即使宏已完成运行。

Also I set the active printer at the time of printing. Of course you can also use Application.ActivePrinter instead, but that will affect the active printer even when the macro is done running.

这篇关于使用VBA更改Excel图表的方向（纵向或横向）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！