Export Cell Range As JPEG and Save As

GCLIV91

New Member
Joined
Mar 6, 2020
Messages
2
Office Version
365
Platform
Windows
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!
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,276
Welcome to MrExcel forums.

Try this macro, changing the saveInFolder string as required.
VBA Code:
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

New Member
Joined
Mar 6, 2020
Messages
2
Office Version
365
Platform
Windows
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

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,276
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:

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

Watch MrExcel Video

Forum statistics

Threads
1,099,789
Messages
5,470,790
Members
406,723
Latest member
Fcolombo

This Week's Hot Topics

Top