×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Export selected range as picture?
2

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

RE: Export selected range as picture?

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

RE: Export selected range as picture?

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

RE: Export selected range as picture?

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

CODE

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.

RE: Export selected range as picture?

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.  

RE: Export selected range as picture?

(OP)
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?

RE: Export selected range as picture?

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.

RE: Export selected range as picture?

(OP)
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...

RE: Export selected range as picture?

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

CODE

MsgBox "Hello World"

is produced thusly:

[code]
MsgBox "Hello World"
[/code]

Enjoy!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources