Changing the orientation (portrait or landscape) o

问题:

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

回答1:

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] 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.

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

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.

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.

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.