Change the orientation (portrait or landscape) of an Excel chart using VBA

Question

I am trying to write a macro to automatically print all the diagrams that I created in a book using a different macro. (literally hundreds) The problem I am facing is that I cannot figure out how to change the graph from portrait layout to landscape layout using VBA. I was wondering if anyone could help me. I tried the code below, but it gives me an error in the line ".ChartObjects (x) .PageSetup.Orientation = xlLandscape" I understand that for a chart object this is a wrong property, but I can’t understand that otherwise 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 

Answer 1

Managing Excel charts using VBA is always a bit confusing because there are ChartObject and then there are Chart objects. Each ChartObject has a child Chart . It is not always very intuitive which properties and methods belong to Chart and which should be found in its parent ChartObject . Quoting VBA Help:

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

Reading VBA help can get you nuts if you don’t have glasses, because ChartObject means something other than a Chart object!

Anyway, as it turned out, .PageSetup.Orientation is on Chart , not ChartObject , as you concluded.

  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 

It is assumed that you want to print each chart on a separate page. What you did in your code was to print each worksheet at once, but that doesn't look like the rest of your question.

Here I used PDFCreator as my printer because I didn’t want to spend a ton of paper while testing this code. You can, of course, customize this as you see fit.

I also install an active printer while printing. Of course, you can also use Application.ActivePrinter , but this will affect the active printer, even when the macro is run.