Export pictures from excel file into jpg using VBA

excel, export, image, vba

Solution

If i remember correctly, you need to use the "Shapes" property of your sheet.

Each Shape object has a TopLeftCell and BottomRightCell attributes that tell you the position of the image.

Here's a piece of code i used a while ago, roughly adapted to your needs. I don't remember the specifics about all those ChartObjects and whatnot, but here it is:

For Each oShape In ActiveSheet.Shapes
    strImageName = ActiveSheet.Cells(oShape.TopLeftCell.Row, 1).Value
    oShape.Select
    'Picture format initialization
    Selection.ShapeRange.PictureFormat.Contrast = 0.5: Selection.ShapeRange.PictureFormat.Brightness = 0.5: Selection.ShapeRange.PictureFormat.ColorType = msoPictureAutomatic: Selection.ShapeRange.PictureFormat.TransparentBackground = msoFalse: Selection.ShapeRange.Fill.Visible = msoFalse: Selection.ShapeRange.Line.Visible = msoFalse: Selection.ShapeRange.Rotation = 0#: Selection.ShapeRange.PictureFormat.CropLeft = 0#: Selection.ShapeRange.PictureFormat.CropRight = 0#: Selection.ShapeRange.PictureFormat.CropTop = 0#: Selection.ShapeRange.PictureFormat.CropBottom = 0#: Selection.ShapeRange.ScaleHeight 1#, msoTrue, msoScaleFromTopLeft: Selection.ShapeRange.ScaleWidth 1#, msoTrue, msoScaleFromTopLeft
    '/Picture format initialization
    Application.Selection.CopyPicture
    Set oDia = ActiveSheet.ChartObjects.Add(0, 0, oShape.Width, oShape.Height)
    Set oChartArea = oDia.Chart
    oDia.Activate
    With oChartArea
        .ChartArea.Select
        .Paste
        .Export ("H:\Webshop_Zpider\Strukturbildene\" & strImageName & ".jpg")
    End With
    oDia.Delete 'oChartArea.Delete
Next

Problem

I have an Excel file which includes pictures in column B and I want like to export them into several files as .jpg (or any other picture file format). The name of the file should be generated from text in column A. I tried following VBA macro: ``` Private Sub CommandButton1_Click() Dim oTxt As Object For Each cell In Ark1.Range("A1:A" & Ark1.UsedRange.Rows.Count) ' you can change the sheet1 to your own choice saveText = cell.Text Open "H:\Webshop_Zpider\Strukturbildene\" & saveText & ".jpg" For Output As #1 Print #1, cell.Offset(0, 1).text Close #1 Next cell End Sub ``` The result is that it generates files (jpg), without any content. I assume the line `Print #1, cell.Offset(0, 1).text.` is wrong. I don't know what I need to change it into, `cell.Offset(0, 1).pix`? Can anybody help me? Thanks!

Original source

Related problems