跳到主要內容

如何從Excel一次導出所有圖像?

假設您的工作簿中有很多圖片,並且想一次將它們保存到特定文件夾中,通常您可以手動將圖片一張一張復制粘貼保存,但複製粘貼很耗時反复這樣。 在這裡,我將向您介紹一些解決此工作的技巧。


使用“另存為”功能一次從Excel導出所有圖像

內置 Excel 另存為 功能,您可以將工作簿另存為 網頁 將所有圖片(包括圖表、形狀和其他對象)保存在文件夾中。 請執行以下操作。

1. 打開包含要保存的圖片的工作簿。 點擊 文件 > 另存為 > 瀏覽。

2。 在裡面 另存為 對話框,您需要:

2.1)選擇一個文件夾來保存導出的圖像;
2.2)選擇 網頁 從另存為類型下拉列表中;
2.3)點擊 節省 按鈕。

3.在彈出 Microsoft Excel中 對話框中,單擊 按鈕。

4.打開保存導出文件的文件夾,可以看到裡面列出了一個文件夾和一個HTML格式的文件。 工作簿中的所有圖片,包括圖表、形狀或對像都保存在此文件夾中。

5.可以將需要的圖片保留在文件夾中,不需要的可以刪除


使用出色的工具輕鬆從 Excel 一次導出所有圖像

上述方法將從工作簿中提取所有對象,也許其中一些您不需要。 如果你有 Excel的Kutools 安裝在Excel中 導出圖形 功能可以幫助您只保存您需要的圖片。

安裝後 Excel的Kutools,請執行以下操作:

1。 點擊 Kutools 加 > 進出口 > 導出圖形:

2。 在裡面 導出圖形 對話框,請進行如下配置:

2.1)選擇 圖片 來自 類型 下拉列表;
提示: 默認情況下,列出當前工作簿中的所有圖形,包括圖表、圖片和形狀。
2.2)在 保存目錄 部分,點擊 按鈕選擇一個文件夾來保存您導出的圖片;
2.3)在 導出格式 下拉列表,選擇要導出為的圖像類型;
2.4)點擊 確定。

3.然後會彈出一個對話框告訴你已經成功導出了多少張圖片,點擊 OK 按鈕。

現在您可以轉到目標文件夾並檢查導出的圖像。

請點擊 導出圖形 進一步了解此功能。

申請前 Excel的Kutools首先下載並安裝.


導出圖像並使用相鄰單元格值重命名它們

如下圖所示,有兩列,一列包含水果名稱列表,另一列包含水果的相應圖片。 要導出這些圖像並用相應的水果名稱自動命名它們,您可以嘗試本節中的 VBA 代碼。

1. 在要導出圖片的工作表中,按 其他 + F11 鍵打開 Microsoft Visual Basic for Applications 窗口。

2。 在裡面 Microsoft Visual Basic for Applications 窗口中,單擊 插入 > 模塊。 然後將以下 VBA 複製到模塊窗口中。

VBA代碼:導出一列圖片並自動命名

Sub ExportImages_ExtendOffice()
'Updated by Extendoffice 20220308
    Dim xStrPath As String
    Dim xStrImgName As String
    Dim xImg As Shape
    Dim xObjChar As ChartObject
    Dim xFD As FileDialog
    Set xFD = Application.FileDialog(msoFileDialogFolderPicker)
    xFD.Title = "Please select a folder to save the pictures" & " - ExtendOffice"
    If xFD.Show = -1 Then
       xStrPath = xFD.SelectedItems.Item(1) & "\"
    Else
        Exit Sub
    End If
    
    On Error Resume Next
    For Each xImg In ActiveSheet.Shapes
        If xImg.TopLeftCell.Column = 2 Then
        xStrImgName = xImg.TopLeftCell.Offset(0, -1).Value
        If xStrImgName <> "" Then
            xImg.Select
            
            Selection.Copy
            Set xObjChar = ActiveSheet.ChartObjects.Add(0, 0, xImg.Width, xImg.Height)
            With xObjChar
                .Border.LineStyle = xlLineStyleNone
                .Activate
                ActiveChart.Paste
                .Chart.Export xStrPath & xStrImgName & ".png"
                .Delete
            End With
        End If
        End If
    Next
End Sub

筆記:

1) 號碼 2 在行“如果 xImg.TopLeftCell.Column = 2 那麼” 表示要從中導出圖片的列號;
2) 號碼 -1 在行“xStrImgName = xImg.TopLeftCell.Offset(0, -1).Value” 表示您將使用相鄰左側單元格的值命名圖片。

3。 按 F5 運行代碼的關鍵。 在打開的窗口中,選擇一個文件夾來保存圖片,然後單擊 確定。

然後導出指定列中的所有圖片並自動使用您指定的相鄰單元格值命名。


相關文章:

如何在Excel中將圖表導出為圖形?

最佳辦公生產力工具

熱門特色: 尋找、突出顯示或識別重複項   |  刪除空白行   |  合併列或儲存格而不遺失數據   |   沒有公式的回合 ...
超級查詢: 多條件VLookup    多值VLookup  |   跨多個工作表的 VLookup   |   模糊查詢 ....
高級下拉列表: 快速建立下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  |  比較範圍和列 ...
特色功能: 網格焦點   |  設計圖   |   大方程式酒吧    工作簿和工作表管理器   |  資源庫 (自動文字)   |  日期選擇器   |  合併工作表   |  加密/解密單元格    按清單發送電子郵件   |  超級濾鏡   |   特殊過濾器 (過濾粗體/斜體/刪除線...)...
前 15 個工具集12 文本 工具 (添加文本, 刪除字符,...)   |   50+ 圖表 類型 (甘特圖,...)   |   40+ 實用 公式 (根據生日計算年齡,...)   |   19 插入 工具 (插入二維碼, 從路徑插入圖片,...)   |   12 轉化 工具 (數字到單詞, 貨幣兌換,...)   |   7 合併與拆分 工具 (高級合併行, 分裂細胞,...)   |   ... 和更多

使用 Kutools for Excel 增強您的 Excel 技能,體驗前所未有的效率。 Kutools for Excel 提供了 300 多種進階功能來提高生產力並節省時間。  點擊此處獲取您最需要的功能...

kte選項卡201905


Office選項卡為Office帶來了選項卡式界面,使您的工作更加輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%,每天為您減少數百次鼠標點擊!
Comments (24)
Rated 5 out of 5 · 1 ratings
This comment was minimized by the moderator on the site
WaW Amazing ! Thanks a lot ! It works perfect for me !
I couldn't insert Milan's code inside the first one, so maybe you could update with the two codes combined ?
It is because my pictures are so small...
Rated 5 out of 5
This comment was minimized by the moderator on the site
Thank you! The code does the job. The only downside is it exports images in displayed size, not in original size. And it's often the case, when an otherwise large image is scaled down to fit in the cell. Is there a way to tweak this code to export original size images?

Sub ExportImages_ExtendOffice()
'Updated by Extendoffice 20220308
    Dim xStrPath As String
    Dim xStrImgName As String
    Dim xImg As Shape
    Dim xObjChar As ChartObject
    Dim xFD As FileDialog
    Set xFD = Application.FileDialog(msoFileDialogFolderPicker)
    xFD.Title = "Please select a folder to save the pictures" & " - ExtendOffice"
    If xFD.Show = -1 Then
       xStrPath = xFD.SelectedItems.Item(1) & "\"
    Else
        Exit Sub
    End If
    
    On Error Resume Next
    For Each xImg In ActiveSheet.Shapes
        If xImg.TopLeftCell.Column = 2 Then
        xStrImgName = xImg.TopLeftCell.Offset(0, -1).Value
        If xStrImgName <> "" Then
            xImg.Select
            
            Selection.Copy
            Set xObjChar = ActiveSheet.ChartObjects.Add(0, 0, xImg.Width, xImg.Height)
            With xObjChar
                .Border.LineStyle = xlLineStyleNone
                .Activate
                ActiveChart.Paste
                .Chart.Export xStrPath & xStrImgName & ".png"
                .Delete
            End With
        End If
        End If
    Next
End Sub
This comment was minimized by the moderator on the site
same issue...
just a quick edit:
If xStrImgName <> "" Then
            xImg.Select
          [b]  xImg.ScaleHeight 1#, True, msoScaleFromTopLeft
            xImg.ScaleWidth 1#, True, msoScaleFromTopLeft[/b]


this will resize all images, so you have to close the file afterwards without saving to preserve the initial vie, but the pictures will go out in their original size.
This comment was minimized by the moderator on the site
fixed, thank you anyway:

Sub ExportImages_ExtendOffice()
'Updated by Extendoffice 20220308
    Dim xStrPath As String
    Dim xStrImgName As String
    Dim xImg As Shape
    Dim xObjChar As ChartObject
    Dim xFD As FileDialog
    Set xFD = Application.FileDialog(msoFileDialogFolderPicker)
    xFD.Title = "Please select a folder to save the pictures" & " - ExtendOffice"
    If xFD.Show = -1 Then
       xStrPath = xFD.SelectedItems.Item(1) & "\"
    Else
        Exit Sub
    End If
    
    On Error Resume Next
    For Each xImg In ActiveSheet.Shapes
        If xImg.TopLeftCell.Column = 2 Then
        xStrImgName = xImg.TopLeftCell.Offset(0, -1).Value
        If xStrImgName <> "" Then
            xImg.Select
'            Selection.Copy
            Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture
            Set xObjChar = ActiveSheet.ChartObjects.Add(0, 0, xImg.Width, xImg.Height)
            With xObjChar
                .Parent.ShapeRange.Line.Visible = msoFalse
                .Border.LineStyle = xlLineStyleNone
'                .Activate
                .Select
                ActiveChart.Paste
                .Chart.Export xStrPath & xStrImgName & ".png"
                .Delete
            End With
        End If
        End If
    Next
End Sub
This comment was minimized by the moderator on the site
Thank you for this great tool, It saved me a ton of time. But I've ran into an issue that most of the photos are saved as blank photos as shown in the attached screenshot. Appreciate the help. Thank you
This comment was minimized by the moderator on the site
Hello, Mohamed
Did your problem occur when using Kutools for Excel? If so, could you upload your workbook file here if you don't mind?
So that, we can check where the problem is?
Thank you!
This comment was minimized by the moderator on the site
I am using the code given in the " Export images and rename them with the adjacent cell values" part of the article. When I run this code I get the following error: "Run-time error '91': Object variable or With block variable not set."

When I click "debug" it points me to line 9 of the code :
xFD.Title = "Please select a folder to save the pictures" & " - ExtendOffice"

Do you have a solution to this? Your article is the only solution I can find.

I am using Excel for Mac.
This comment was minimized by the moderator on the site
Hello, Dylan,

Sorry, our code is only applicable to Microsoft Excel, maybe you can try the code in Microsoft Excel.
Thank you!
This comment was minimized by the moderator on the site
I am using Microsoft Excel on Mac OSX.
This comment was minimized by the moderator on the site
I am also getting this issue using a Mac - Dylan were you able to resolve?

Are we meant to change something with the below that it is picking up as a bug?

xFD.Title = "Please select a folder to save the pictures" & " - ExtendOffice"
This comment was minimized by the moderator on the site
Hello, thank you for this article. When I execute the code above from Export images and rename them with the adjacent cell values, I get the following error message: Run-time error '91': Object variable or With block variable not set. When I click debug, it highlights line 9 of the code. Do you know why this is happening? FYI I am using Excel for Mac OSX. Thank you
This comment was minimized by the moderator on the site
Τέλειο εργαλείο! Όμως το μέγεθος-ανάλυση της εικόνας μικραίνει . Υπάρχει τρόπος να διατηρηθεί η αρχική ανάλυση ?
This comment was minimized by the moderator on the site
Bonjour le code VBA m'enregistre des images blanches je ne comprends pas pourquoi. quelqu'un a une solution ?
This comment was minimized by the moderator on the site
Hello, Sophie,
The code in this article can work well in my Excel, could you upload your Excel file here if you don't mind? So we can help to check the problem for you.
Or you can describe your problem more clear and detailed.
Thank you!
This comment was minimized by the moderator on the site
hallo skyyang, ich habe seit neulich das gleiche problem, viele leere *.png dateien.
Your text to link
bei jedem exportversuch werden andere bilder nicht richtig erfasst.
mfg und danke
This comment was minimized by the moderator on the site
Hello, milan bojic
Which Excel version do you use?
I have tried the code in your workbook, it works well, and all the images in your workbook are exported in the folder and renamed based on the cell value, see screenshot:
https://www.extendoffice.com/images/stories/comments/comment-skyyang/doc-export-images-1.png
This comment was minimized by the moderator on the site
Hello skyyang, it's office 365. your code was working fine until 2 weeks ago, not anymore, see screenshot. meanwhile i found something similar that works, (combined with yours), maybe you can use that for your code.

screenshot:
[img]https://drive.google.com/file/d/1vfRlhpyzqg7QNFeYl53mTvNON3gIrFwv/view?usp=sharing[/img]


Sub BilderExportieren()
    Dim shaBild As Shape
    Dim strZielpfad As String
    strZielpfad = "c:\Tuerliste\img3\" '<== Zielpfad entsprechend anpassen!!
    For Each shaBild In ActiveSheet.Shapes
        BildExportShape shaBild, strZielpfad
    Next shaBild
End Sub

Sub BildExportShape(shaBild As Shape, strZiel As String)
    Dim xStrImgName As String
    Dim chDiagramm As ChartObject
    xStrImgName = shaBild.TopLeftCell.Offset(0, -1).Value
    Application.ScreenUpdating = False
    shaBild.CopyPicture Appearance:=xlScreen, Format:=xlPicture
    Set chDiagramm = ActiveSheet.ChartObjects.Add(0, 0, shaBild.Width, shaBild.Height)
    With chDiagramm.Chart
        ' erforderlich bei Excel2010, da Diagrammfläche automatisch mit Rahmen erstellt wird
        .Parent.ShapeRange.Line.Visible = msoFalse
        ' bei Excel2016 muss die Diagrammfläche selektiert vor .Paste werden - andernfalls ist das Bild leer
        If Val(Application.Version) = 16 Then .ChartArea.Select
        .Paste
        .Export Filename:=strZiel & xStrImgName & ".png", FilterName:="png"
    End With
    chDiagramm.Delete
    Set chDiagramm = Nothing
    Set shaBild = Nothing
    Application.ScreenUpdating = True
End Sub


Thank you anyway
Milan Bojic
This comment was minimized by the moderator on the site
Hello, milan bojic,
Thanks for your code, maybe it can help others in the future. 🙂
This comment was minimized by the moderator on the site
This is great, if I wanted to grab the background colour of a cell (instead of an image) and save that as a image named with the adjacent cell how would that be an easy change to the code?
This comment was minimized by the moderator on the site
Hi Tom,Do you mean saving the background color of the cell as an image and named it with the adjacent cell value?Sorry can't help you with that.
This comment was minimized by the moderator on the site
thanks a lot it helped me a lot
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations