Export Cell Range As JPEG and Save As

[GCLIV91]

Hi All,

I have a sheet in which I have 2 images within a cell to the left (A4) and a product code in the next cell (C4). I have to screenshot cell A4 and save the file as the product code in C4 (another program uses this data so they have to be saved in this format). I’m currently using the snipping tool but it’s taking a long time because there’s about 150 products a week within this sheet. Is there VBA that would save the contents of A4 as a JPEG with the file name of the contents of C4 within a specific folder?

Thank you in advance for any help!

 

[John_w]

Welcome to MrExcel forums.

Try this macro, changing the saveInFolder string as required.

Public Sub Save_Cell_Images()
    
    Dim saveInFolder As String
    Dim imageFile As String
    
    saveInFolder = "C:\Path\to\folder\"
    
    If Right(saveInFolder, 1) <> "\" Then saveInFolder = saveInFolder & "\"
    
    imageFile = saveInFolder & ActiveSheet.Range("C4").Value & ".jpg"
    Save_Object_As_Picture ActiveSheet.Range("A4"), imageFile

End Sub


Private Sub Save_Object_As_Picture(saveObject As Object, imageFileName As String)

    'Save a picture of an object as a JPG/JPEG file
    
    'Arguments
    'saveObject     - any object in the CopyPicture method's 'Applies To' list, for example a Range or Shape
    'imageFileName  - the .jpg or .jpeg file name (including folder path if required) the picture will be saved as
    
    Dim temporaryChart As ChartObject
     
    Application.ScreenUpdating = False
    saveObject.CopyPicture xlScreen, xlPicture
    Set temporaryChart = ActiveSheet.ChartObjects.Add(0, 0, saveObject.Width, saveObject.Height)
    With temporaryChart
        .Activate
        .Border.LineStyle = xlLineStyleNone      'No border
        .Chart.Paste
        .Chart.Export imageFileName
        .Delete
    End With
    Application.ScreenUpdating = True
    Set temporaryChart = Nothing
    
End Sub

 

[GCLIV91]

Thank you John. It seems to be working in part as it is saving a jpeg in the designated folder but the image is blank. Can you assist any further? Many thanks.

 

[John_w]

A blank image can occur when the .Activate line is missing, but that's not the case here. Try adding these lines immediately after the .Activate:

DoEvents
Application.Wait DateAdd("s", 1, Now)

 

[tomtownson]

Hello, I have a similar situation, and this code has worked perfectly, thank you. Is there a way to adapt the code so that will repeat the script for the other items in the columns below? At present we have approx 20 lines but this will soon be increasing. Thanks in advance

 

[John_w]

It's not clear what you mean by 'the other items in the columns below', but it should be a case of repeating the lines which define imageFile and call Save_Object_As_Picture, something like this:

Public Sub Save_Cell_Images()
    
    Dim saveInFolder As String
    Dim imageFile As String
    
    saveInFolder = "C:\Path\to\folder\"
    
    If Right(saveInFolder, 1) <> "\" Then saveInFolder = saveInFolder & "\"
    
    imageFile = saveInFolder & ActiveSheet.Range("C4").Value & ".jpg"
    Save_Object_As_Picture ActiveSheet.Range("A4"), imageFile

    imageFile = saveInFolder & ActiveSheet.Range("C5").Value & ".jpg"
    Save_Object_As_Picture ActiveSheet.Range("A5"), imageFile

    imageFile = saveInFolder & ActiveSheet.Range("C6").Value & ".jpg"
    Save_Object_As_Picture ActiveSheet.Range("A6"), imageFile

End Sub

Please start a new thread if you need more help, and describe in more detail exactly what you require.

 

[tomtownson]

Hi John, Thanks for your reply. I'm just after completing the same process (in my case saving a picture in column I with a name from column A), but for a list of a few hundred items. Rather than typing out the code over again as you suggest (which would work perfectly), is there a quicker way of repeating the process for all rows of the sheet please?

 

[John_w]

The previous code using a loop is:

Public Sub Save_Cell_Images()
    
    Dim saveInFolder As String
    Dim imageFile As String
    Dim r As Long
    
    saveInFolder = "C:\Path\to\folder\"
    
    If Right(saveInFolder, 1) <> "\" Then saveInFolder = saveInFolder & "\"
    
    For r = 4 To 6
        imageFile = saveInFolder & ActiveSheet.Range("C" & r).Value & ".jpg"
        Save_Object_As_Picture ActiveSheet.Range("A" & r), imageFile
    Next

End Sub

The code is an extension of the OP's specific request and the code for your situation would be different.

 

[tomtownson]

Thanks for your prompt assistance, I have changed the values to suit my sheet and it's working perfectly.

 

[jolivanes]

I've gone away from using a chart to save ranges or existing pictures on a sheet.
I use IrfanView, which is free, now and no need anymore to "wait" or use "Do Events"
In my case, I use the same macro for my (old) desktop and my (also old) laptop.

Sub Save_Range_Or_Picture()
    Dim cv As String
    Dim myPath As String
    Dim sh As Worksheet
    Set sh = ActiveSheet


    Select Case Environ("computername")
        Case "DESKTOP"
            cv = "C:\Program Files\IrfanView\i_view64.exe"
        Case "LAPTOP"
            cv = "C:\Program Files (x86)\IrfanView\i_view32.exe"
    End Select
    myPath = "C:\Folder Name Here\Saved_With_Irfanview.jpg"

    Selection.Copy
    Shell cv & " ""/clippaste /convert=" & myPath & """", vbNormalFocus
    Unload Me

End Sub

Should be easy to adapt for multiple ranges or objects.