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
Top = Range(“D5”). Top – The cell references within the Range objects (“D5”) specify the top-left corner of your chart. So, all you need to do is to change “D5” to any worksheet cell where you want your chart to be placed.
On the Page Layout tab, in the Page Setup group, click Orientation, and then click Portrait or Landscape.
Select the worksheet with your chart for printing. Go to the Page Layout tab and click on the arrow under the Orientation icon. Pick the Landscape option.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With