View Single Post
  #11   Report Post  
Old February 11th 20, 08:50 AM posted to microsoft.public.excel.programming
Robert Baer Robert Baer is offline
external usenet poster
First recorded activity by ExcelBanter: Dec 2007
Posts: 93
Default Excel print object?

Claus Busch wrote:
Hi Robert,

Am Fri, 7 Feb 2020 00:01:05 -0800 schrieb Robert Baer:

Snippet of incomplete program:
ActivePrinter = "Acrobat PDFWriter on FILE:": PrintToFile = True
'note need KILL statement for PDFWriter
' Above sets printer in Excel 2003; WILL crash in Excel 2010.

only workbook, worksheets, sheet ranges and charts can be exported to


Sub PrintShapes()
Dim shp As Shape
Dim i As Integer
Dim varShp() As Variant

'Modify here the path to save the pdf
Const myPath = "C:\Users\claus\Desktop\"
With Application
.ScreenUpdating = False
.DisplayAlerts = False
End With
'Modify the sheet name
With Sheets("Sheet1")
For Each shp In .Shapes
ReDim Preserve varShp(i)
varShp(i) = shp.Name
i = i + 1

Sheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = "Temp"

For i = LBound(varShp) To UBound(varShp)
With Charts.Add
.SetSourceData Source:=Sheets("Temp").Range("A1")
.Location whe=xlLocationAsObject, Name:=Sheets("Temp").Name
End With
With Sheets("Temp").Shapes(1)
.Height = Sheets("Sheet1").Shapes(varShp(i)).Height + 1
.Width = Sheets("Sheet1").Shapes(varShp(i)).Width + 1
End With

Sheets("Temp").ChartObjects(1).Chart.ExportAsFixed Format Type:=xlTypePDF, Filename:= _
myPath & varShp(i) & ".pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
End With
With Application
.ScreenUpdating = True
.DisplayAlerts = True
End With
End Sub

Claus B.

(thinking out loud) iobj is IN a is NOT a ChartObject
or related to a chart in any way that i know of..maybe it can be
considered as a Shape.
So, perhaps there is a chance of part of above working..