Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: Export range of cells as jpg

  1. #1
    Board Regular JenniferMurphy's Avatar
    Join Date
    Jul 2011
    Location
    Silicon Valley, CA, USA
    Posts
    895
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Export range of cells as jpg

    According to the Microsoft website, there is no way in Excel to export a range of cells as a jpeg file. It seems to me that this would be a pretty basic function, but then what do I know.

    My current solution is to copy the cells to a graphics program (Visio or Paint) and save it from there. That works, but is a bit of a nuisance.

    One website suggested installing a graphics printer, such as SnagIt. I happen to have SnagIt already installed, so I tested that method. It works, but I could not figure out a way to get just the selected cells. It adds the worksheet header and footer as if I were printing it on a real printer. I guess that makes sense, but it is not what I want.

    I see that there are several third party programs that say they will do the job. Can anyone vouch for any of them? I really don't need another piece of software, but if it works and has no bad side effects (such as spam from the vendor), it might be worth it.

    Any suggestions appreciated.
    Using Office 2007 Pro on Win XP Pro

  2. #2
    MrExcel MVP
    Moderator
    Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    8,756
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Export range of cells as jpg

    Hi Jennifer,

    My first thought was to save the range as HTML (as is often done for send range by email code), then convert the HTML to jpg.

    While looking for some free code that might do that conversion, I stumbled across a code example that exploits Excel's ability to export a Chart to jpg.

    Visual Basic - VBA: Export excel range as image and save as file. | emoticode

    I've modified that example to make it more interactive (using the Selected Range, and prompting for a filename).
    Please let me know if this does what you wanted.

    Code:
    Sub ExportRangeToJPG()
     '--exports selected range to jpg file
     '  default filename is address of selected range
     '  based on code example posted at:
     '     http://www.emoticode.net/visual-basic/vba-export-excel-range-as-image-and-save-as-file.html
    
     Dim vFilePath As Variant
     Dim sDefaultName As String
     
     If TypeName(Selection) <> "Range" Then
       MsgBox "Selection is not a range of cells."
       Exit Sub
     End If
    
     With Selection
       sDefaultName = Replace(.Address(0, 0, xlA1, 1), ":", "to") & ".jpg"
       vFilePath = Application.GetSaveAsFilename( _
        InitialFileName:=sDefaultName, _
        FileFilter:="JPEG File Interchange Format (*.jpg), *.jpg", _
        Title:="Save As")
        
     '--exit if cancelled by user,
     If vFilePath = False Then Exit Sub
       '--Make picture of selection and copy to clipboard
       .CopyPicture Appearance:=xlScreen, Format:=xlBitmap
       
       '--Create an empty chart with exact size of range copied
       With ActiveSheet.ChartObjects.Add( _
            Left:=.Left, Top:=.Top, Width:=.Width, Height:=.Height)
         .Name = "TempChart"
         .Activate
       End With
     End With
    
     '--Paste into chart area, export to file, delete chart.
     ActiveChart.Paste
     With ActiveSheet.ChartObjects("TempChart")
      .Chart.Export CStr(vFilePath)
      .Delete
     End With
    End Sub
    Using Excel 2016

  3. #3
    Board Regular JenniferMurphy's Avatar
    Join Date
    Jul 2011
    Location
    Silicon Valley, CA, USA
    Posts
    895
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Export range of cells as jpg

    Wow, thanks for doing that. It works perfectly. Amazing. I would have never figured that out.

    As a test, I exported a range of cells that I had previously exported by pasting them into Visio and then saving from there. Visio allows me to set the output resolution. I usually choose either the default "screen" resolution (96x96) or, if I am going to print it, the default "printer" resolution (600x600). Here are the resulting file sizes:

    Method Resolution File Size Print Crispness
    Visio 96 x 96 128 KB Somewhat fuzzy
    Visio 600 x 600 1.5 MB Crystal clear
    Visio 1200 x 1200 3.5 MB Crystal clear (same as 600x600)
    Sub ??? 38 KB Somewhat fuzzy (same as 96x96)

    Is there a way to set the output resolution in the Sub?
    Using Office 2007 Pro on Win XP Pro

  4. #4
    Board Regular JenniferMurphy's Avatar
    Join Date
    Jul 2011
    Location
    Silicon Valley, CA, USA
    Posts
    895
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Export range of cells as jpg

    PS: I forgot to mention that the Visio JPG Output Options dialog provides for both the resolution and the size to be specified. I've uploaded a screen shot to this DropBox folder:

    https://www.dropbox.com/sh/9x43p6tk29yuu4k/rGZSBvpO_S

    That probably makes it more complicated, but it is necessary.

    PPS: I don't know why this forum won't allow uploads like almost every other forum (sigh).
    Using Office 2007 Pro on Win XP Pro

  5. #5
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,909
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    28 Thread(s)

    Default Re: Export range of cells as jpg

    Quote Originally Posted by Jerry Sullivan View Post
    Hi Jennifer,

    My first thought was to save the range as HTML (as is often done for send range by email code), then convert the HTML to jpg.

    While looking for some free code that might do that conversion, I stumbled across a code example that exploits Excel's ability to export a Chart to jpg.

    Visual Basic - VBA: Export excel range as image and save as file. | emoticode

    I've modified that example to make it more interactive (using the Selected Range, and prompting for a filename).
    Please let me know if this does what you wanted.

    Code:
    Sub ExportRangeToJPG()
      '--exports selected range to jpg file
      '  default filename is address of selected range
      '  based on code example posted at:
      '     http://www.emoticode.net/visual-basic/vba-export-excel-range-as-image-and-save-as-file.html
    
      Dim vFilePath As Variant
      Dim sDefaultName As String
      
      If TypeName(Selection) <> "Range" Then
        MsgBox "Selection is not a range of cells."
        Exit Sub
      End If
    
      With Selection
        sDefaultName = Replace(.Address(0, 0, xlA1, 1), ":", "to") & ".jpg"
        vFilePath = Application.GetSaveAsFilename( _
         InitialFileName:=sDefaultName, _
         FileFilter:="JPEG File Interchange Format (*.jpg), *.jpg", _
         Title:="Save As")
         
      '--exit if cancelled by user,
      If vFilePath = False Then Exit Sub
        '--Make picture of selection and copy to clipboard
        .CopyPicture Appearance:=xlScreen, Format:=xlBitmap
        
        '--Create an empty chart with exact size of range copied
        With ActiveSheet.ChartObjects.Add( _
             Left:=.Left, Top:=.Top, Width:=.Width, Height:=.Height)
          .Name = "TempChart"
          .Activate
        End With
      End With
    
      '--Paste into chart area, export to file, delete chart.
      ActiveChart.Paste
      With ActiveSheet.ChartObjects("TempChart")
       .Chart.Export CStr(vFilePath)
       .Delete
      End With
     End Sub
    Quote Originally Posted by JenniferMurphy View Post
    Is there a way to set the output resolution in the Sub?
    This is just a guess on my part as I do not know for sure, but try replacing the keyword I highlighted in red in Jerry's posted code with xlPrinter and see that makes a difference.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  6. #6
    Board Regular JenniferMurphy's Avatar
    Join Date
    Jul 2011
    Location
    Silicon Valley, CA, USA
    Posts
    895
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Export range of cells as jpg

    Quote Originally Posted by Rick Rothstein View Post
    This is just a guess on my part as I do not know for sure, but try replacing the keyword I highlighted in red in Jerry's posted code with xlPrinter and see that makes a difference.
    Nope, that gets: "Run time error '1004': CopyPicture method of Range class failed
    Using Office 2007 Pro on Win XP Pro

  7. #7
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,909
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    28 Thread(s)

    Default Re: Export range of cells as jpg

    Quote Originally Posted by JenniferMurphy View Post
    Nope, that gets: "Run time error '1004': CopyPicture method of Range class failed
    Okay, what happens if you remove the second argument and just use this...

    .CopyPicture Appearance:=xlPrinter
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  8. #8
    Board Regular JenniferMurphy's Avatar
    Join Date
    Jul 2011
    Location
    Silicon Valley, CA, USA
    Posts
    895
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Export range of cells as jpg

    Quote Originally Posted by Rick Rothstein View Post
    Okay, what happens if you remove the second argument and just use this...

    .CopyPicture Appearance:=xlPrinter
    That works, but it's about the same as before -- slightly fuzzy. Filesize = 35 KB.

    PS: I think we need a way to specify both the resolution and the dimensions. Clearly, the image that gets copied to the clipboard from Excel has enough resolution. It just gets discarded. In a general solution, the user could chose these, but I'd be happy with the resolution being fixed at 300x300 and the dimensions equal to the source. That's what I usually choose in Visio.
    Last edited by JenniferMurphy; Jan 20th, 2014 at 03:23 PM. Reason: Add PS
    Using Office 2007 Pro on Win XP Pro

  9. #9
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,909
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    28 Thread(s)

    Default Re: Export range of cells as jpg

    Quote Originally Posted by JenniferMurphy View Post
    That works, but it's about the same as before -- slightly fuzzy. Filesize = 35 KB.

    PS: I think we need a way to specify both the resolution and the dimensions.
    I was hoping that xlPrinter would do that... I guess not. Sorry.

    Edit Note: Just a thought... what happens if you zoom the worksheet before copying the picture... do you get more pixels in the image?
    Last edited by Rick Rothstein; Jan 20th, 2014 at 03:37 PM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  10. #10
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Export range of cells as jpg

    Hello Jennifer,

    One easy way to save a clip of a range of cells as a picture is to use the built-in Camera tool. To use this tool you must add the Camera button to your Quick Launch toolbar (Excel 2007 and later) or to the Tools menu (Excel 2003 and earlier). Assuming you are using Excel 2007 or later click the Office button and Excel Options, then the Customize button. In the Choose Commands from dropdown select Commands Not in the Ribbon. Select the Camera button and click Add >> to add it to the Quick Access toolbar.

    Once added to the QAT, simply select the range of cells you want to convert to a picture and click the Camera button. Then click on the sheet where you would like the picture inserted. To save it as a jpg, simply copy it and paste (or import clipboard) into any picture editor (I highly recommend XnView, which is a free download from XnView.com. It is very fast, easy, and lots of functionality), then Save as jpeg file type.

    Note that the picture the Camera button creates is linked to the cells, so it updates automatically when you change any of the cells in the range. Nevertheless you can use the picture editing features in Excel to modify it (e.g., change the contrast, brightness).

    I hope you find this helpful.

    Damon
    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •