Export selected range as picture?
Export selected range as picture?
(OP)
I'm trying to use the contents of a particular range to create my custom footer. Because the footer is highly formatted (company dictated report footer) the only programmatic way I can think to do this is to export the selection as a picture and then insert the picture in the footer.
I know this can be done, since ASAP utiliites, which claims to be written in VBA, can do it; but I can't seem to figure out how to export the selected range as a picture. HELP!!!!
I'd also be interested in alternative methods. Thanks
I know this can be done, since ASAP utiliites, which claims to be written in VBA, can do it; but I can't seem to figure out how to export the selected range as a picture. HELP!!!!
I'd also be interested in alternative methods. Thanks





RE: Export selected range as picture?
RE: Export selected range as picture?
RE: Export selected range as picture?
CODE
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
RE: Export selected range as picture?
RE: Export selected range as picture?
So, how do I create/save a picture file from the contents of the clipboard? Can this even be done in VBA?
RE: Export selected range as picture?
http://www.mvps.org/dmcritchie/excel/xl2gif.htm
It basically involves pasting the picture from the clipboard onto a chart and then exporting the chart as a picture. It was written for Excel 8/9, but doesn't work in 2003. It will give you a start, though.
RE: Export selected range as picture?
'Activate the footer sheet and update the page number
Worksheets("Footer").Select
Range("H6") = SxnPage
'Copy the footer area
Range("B2:I6").CopyPicture Appearance:=xlScreen, Format:=xlPicture
'Create the path to save the picture to later
TheFile = ThePath & "\Footer.png"
'Create a chart to paste the _now picture_ footer into
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("Footer").Range("B2:I6"), PlotBy _
:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:="Footer"
ActiveChart.ChartArea.ClearContents
'Size the chart to accept the footer *note - this is double actual size to increase the resolution
With ActiveChart.Parent
.Width = 970
.Height = 160
End With
'Turn off the border/outline
ActiveChart.ChartArea.Select
With Selection.Border
.Weight = 1
.LineStyle = 0
End With
ActiveChart.HasLegend = False
'Paste the copied selection into the chart as a picture
ActiveChart.Paste
'Scale the picture footer inside the chart
ActiveChart.Shapes("Picture 1").Select
Selection.ShapeRange.ScaleWidth 2, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 2, msoFalse, msoScaleFromTopLeft
'Export the sized picture and delete the created chart
ActiveChart.Export Filename:=TheFile, FilterName:="PNG"
ActiveChart.ChartArea.Select
ActiveWindow.Visible = False
Selection.Delete
'Insert the footer picture in the current sheet
Sheets(i).Activate
With ActiveSheet.PageSetup
.CenterFooterPicture.Filename = TheFile
.CenterFooter = "&G"
.FirstPageNumber = FirstPage
.CenterFooterPicture.Width = Application.InchesToPoints(6.7)
.FitToPagesWide = 1
.FitToPagesTall = 1
.CenterHorizontally = True
.CenterVertically = False
End With
Next i
**Can't seem to figure out how to post into those neat little code windows...
RE: Export selected range as picture?
CODE
is produced thusly:
[code]
MsgBox "Hello World"
[/code]
Enjoy!