Converting and Excel file to a .jpg file....

[giraffe]

I need help converting an excel spreadsheet into a .jpg file so that it can be viewed using Adobe Acrobat.

Can anyone help????

 

[Juan Pablo González]

Approach one.

The following code can, rather "painlessly", export an image to a BMP file.

It uses the PastePicture code from Stephen Bullen's site:

http://www.bmsltd.co.uk/Excel/Default.htm

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> SaveRngAsBMP(Rng <SPAN style="color:#00007F">As</SPAN> Range, FileName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>)
    Rng.CopyPicture xlScreen, xlBitmap
    SavePicture PastePicture(xlBitmap), FileName
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Sub</SPAN> TestIt()
    <SPAN style="color:#00007F">Dim</SPAN> Rng <SPAN style="color:#00007F">As</SPAN> Range, Fn <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> Rng = Range("A1:A5")
    Fn = "C:\MyFile.bmp"
    SaveRngAsBMP Rng, Fn
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Approach two.

The Chart object has a .Export method that enables to save directly to a JPG file... however, this method isn't very "pretty" because it must use that object as an intermediate, to get the final result. Don't need the PastePicture code from Stephen Bullen.

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> SaveRngAsJPG(Rng <SPAN style="color:#00007F">As</SPAN> Range, FileName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>)
    <SPAN style="color:#00007F">Dim</SPAN> Cht <SPAN style="color:#00007F">As</SPAN> Chart, bScreen <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>, Shp <SPAN style="color:#00007F">As</SPAN> Shape
    bScreen = Application.ScreenUpdating
    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> Cht = Workbooks.Add(xlChart).Charts(1)
    Cht.ChartArea.Clear
    Rng.CopyPicture xlScreen, xlPicture
    Cht.Paste
    <SPAN style="color:#00007F">With</SPAN> Cht.Shapes(1)
        .Left = 0
        .Top = 0
        .Width = Cht.ChartArea.Width
        .Height = Cht.ChartArea.Height
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    Cht.Export FileName, "JPEG", <SPAN style="color:#00007F">False</SPAN>
    Cht.Parent.Close <SPAN style="color:#00007F">False</SPAN>
    Application.ScreenUpdating = bScreen
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Sub</SPAN> TestIt2()
    <SPAN style="color:#00007F">Dim</SPAN> Rng <SPAN style="color:#00007F">As</SPAN> Range, Fn <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> Rng = Range("A1:H21")
    Fn = "C:\MyFile.jpg"
    SaveRngAsJPG Rng, Fn
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

 

[Zeaon]

This looks exactly like what I need, but I'm a noob when it comes to programming in Excel. How do I add the code above?

Thanks!

-Z

 

[Zeaon]

Ok.. I opened the Visual Basic editor (Alt F11) and added a new module. I clicked the run button and I had the .JPG file in c:\.

Is there an easier way to run this than opening the VB editor each time and clicking play?

Thanks!

-Z

 

[Juan Pablo González]

In Excel, press Alt F8, and you should see the macro in there.

 

[Trebor76]

Hi all,

I know this is a really old post but it helped me immensely so I though I'd post my final code without the tags that are on Juan's nifty post in thread 2 for anyone else who may need to save a range as a *.jpg or *.bmp image. I have made a few changes to for my own needs and to get it running seamlessly (needed to activate (select) the chart).

Regards,

Robert

Option Explicit
'https://www.mrexcel.com/board/threads/converting-and-excel-file-to-a-jpg-file.54296/

'The Chart object has a .Export method that enables to save directly to a JPG file...
'however, this method isn't very "pretty" because it must use that object as an intermediate, to get the final result.
'Don't need the PastePicture code from Stephen Bullen.

Private Sub SaveRngAsImage(Rng As Range, FileName As String, FileType As String)

    Dim Cht As Chart, Shp As Shape

    Set Cht = Workbooks.Add(xlChart).Charts(1)
    Cht.Activate 'Clunky but necessary
    Cht.ChartArea.Clear
    Rng.CopyPicture xlScreen, xlPicture
    Cht.Paste
    With Cht.Shapes(1)
        .Left = 0
        .Top = 0
        .Width = Cht.ChartArea.Width
        .Height = Cht.ChartArea.Height
    End With
    Cht.Export FileName, StrConv(FileType, vbUpperCase), False
    Cht.Parent.Close False

End Sub
Sub CreateRngAsImage()

    Dim Rng As Range, Fn As String
    Dim strFileType As String
   
    Application.ScreenUpdating = False
    Set Rng = Range("A1:H21") 'Change to suit.
    Fn = "C:\TestJpeg.bmp" 'Full path for the image to be saved. Change to suit.  Extension could be either "bmp" or "jpg"
    strFileType = Right(Fn, Len(Fn) - InStrRev(Fn, "."))
    Call SaveRngAsImage(Rng, Fn, strFileType)

End Sub

Regards,

Robert