Export selected range as picture? 2

[YoungTurk]

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

 

[handleman]

Do you want to do this with VBA, or do you want to do it once and be done with it?

 

[YoungTurk]

I need to do this with VBA, because the footer information will be different each time the spreadsheet is used.

 

[joerd]

You can copy a range as a picture to the clipboard by:

Range("A1:B4").CopyPicture Appearance:=xlScreen, Format:=xlPicture

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[handleman]

Copying the picture onto the clipboard is easy. Putting a picture in the footer is easy. The trick is getting the picture from the clipboard to the footer.

 

[YoungTurk]

Well said, handleman. Although, copying the picture to the clipboard may be a start. When I do this manually, I create a picture file in the spreadsheet's directory. I can then insert that picture file into the footer.

So, how do I create/save a picture file from the contents of the clipboard? Can this even be done in VBA?

 

[handleman]

Nearly anything is possible. How easy it is is a different matter. Check out:

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.

 

[YoungTurk]

For General Info, here is my solution:

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

 

[handleman]

Click the little link below the message entry area titled "Process TGML". It will show you all the tags you can use to format your posts. The code window

MsgBox "Hello World"

is produced thusly:

[ignore]

MsgBox "Hello World"

[/ignore]

Enjoy!