Create Image of selected cells and save outside of Excel

[feathers212]

I have a spreadsheet that once created and verified needs to be put into a totally non-editable format (to be accomplished automatically using VBA). My boss suggested using our network pdf printer to convert the excel file into a pdf. I was hoping that there could be another way.

I found this information in another post:

Select the range
Ctrl + C to copy
Select paste location
Shift +Alt +E
P to paste or N to paste link

And I came up with this code (would be tweaked to fit):

[VBA]Sub Macro2()
Workbooks.Add
With Cells.Interior
.ColorIndex = 2
.Pattern = xlSolid
End With

Windows("Job 123456 - Run Test - Press 1009 - 4.12.2007.xls").Activate 'This is the existing workbook
Range("A1:H37").Copy
Windows("Book5").Activate 'The workbook that was just added
ActiveSheet.Pictures.Paste.Select
End Sub[/VBA]

I was wondering if it was possible to create this picture in something other than Excel. Perhaps something like Paint, where I can save the file as a bitmap or a jpeg or whatever. Whatever I do, I need to accomplish it all with coding.

Thanks!
~Heather

[Simon Lloyd]

You could try saving it as a Single File Web document, its like a picture!
something like this but you can record it using the macro recorder, don't forget when it asks you to save to choose worksheet rather than workbook![VBA]
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\My Documents\test.mht", FileFormat:= _
xlWebArchive, CreateBackup:=False
With ActiveWorkbook.PublishObjects.Add(xlSourceSheet, _
"C:\Documents and Settings\My Documents\test.mht", "Sheet1", "", _
xlHtmlStatic, "Book1_2148", "")
.Publish (True)
.AutoRepublish = False
End With
[/VBA]Regards,
Simon

[mdmackillop]

[VBA]Sub Macro1()
Selection.Copy
Sheets("Sheet2").Pictures.Paste
Application.CutCopyMode = False
Sheets("Sheet2").Copy
ActiveWindow.DisplayGridlines = False

End Sub
[/VBA]

[jolivanes]

I use the following:
[vba]
Sub ExportCellsAsPicture()
Const FName As String = "D:\My Documents\My Pics\Pics.jpg"
Dim pic_rng As Range
Dim ShTemp As Worksheet
Dim ChTemp As Chart
Dim PicTemp As Picture
Application.ScreenUpdating = False
Set pic_rng = Worksheets("FileNameHere").Range("B1:F28") 'Set your range here
Set ShTemp = Worksheets.Add
Charts.Add
ActiveChart.Location Where:=xlLocationAsObject, Name:=ShTemp.Name
Set ChTemp = ActiveChart
pic_rng.CopyPicture Appearance:=xlScreen, Format:=xlPicture
ChTemp.Paste
Set PicTemp = Selection
With ChTemp.Parent
.Width = PicTemp.Width + 8
.Height = PicTemp.Height + 8
End With
ChTemp.Export Filename:="D:\My Documents\My Pics\Pics.jpg", FilterName:="jpg"
Application.DisplayAlerts = False
ShTemp.Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
[/vba]

Works like a charm for me.
You can substitude the .jpg to .gif if so desired.
It'll put a picture named "Pics.jpg" in the folder "D:\My Documents\My Pics of the range selected.
HTH
John

[Ivan F Moala]

You could also try something like.........

http://www.xcelfiles.com/EMF_Save.html

[feathers212]

You could try saving it as a Single File Web document, its like a picture!

I had thought about saving as the Single Web File, but my problem with it is that I several option button and text boxes on my form. Internet Explorer doesn't like to show these active controls right away (security reasons). I'd rather not make my users have to constantly grant access to this "blocked content". Plus, the values on the controls can be "changed" (I know the changes cannot actually be saved, but I don't want the appearance of editability.)

I'm thinking that I will stick with the paste as a picture. It meets my needs and greatly reduces the file size. Hopefully the boss finds it to be a good idea as well!

Thanks to everyone for your input.