使用VBA将excel文件中的图片导出为jpg [英] Export pictures from excel file into jpg using VBA

问题描述

我有一个 Excel 文件，其中包含 B 列中的图片，我想将它们以 .jpg(或任何其他图片文件格式)导出到多个文件中.该文件的名称应从 A 列中的文本生成.我尝试遵循 VBA 宏:

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_ZpiderStrukturbildene" & saveText & ".jpg" For Output As #1
 Print #1, cell.Offset(0, 1).text
 Close #1
 Next cell
End Sub

结果是生成文件(jpg)，没有任何内容.我假设行 Print #1, cell.Offset(0, 1).text. 是错误的.不知道要改成什么，cell.Offset(0, 1).pix?

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!

推荐答案

如果我没记错的话，您需要使用工作表的Shapes"属性.

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

每个 Shape 对象都有一个 TopLeftCell 和 BottomRightCell 属性，用于告诉您图像的位置.

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

这是我前段时间使用的一段代码，大致适应了您的需求.我不记得所有这些 ChartObjects 和诸如此类的细节，但它是:

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_ZpiderStrukturbildene" & strImageName & ".jpg")
    End With
    oDia.Delete 'oChartArea.Delete
Next

这篇关于使用VBA将excel文件中的图片导出为jpg的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！