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)