Exported Images From Multiple sheet Overload (Occurring Duplicate)

[muhammad susanto]

hello all..

Below code will generate a folder at the file's location, named by [filename]_Pictures with sub folders are named by sheet name inside, inside each subfolder is all pictures on the sheet.

Sub ExtractPictures()
Dim FSO As Object, sFolder As String, sTmpFolder As String, WB As Workbook, WS As Worksheet, i As Long
Set FSO = VBA.CreateObject("Scripting.FileSystemObject")
Set WB = ActiveWorkbook
sFolder = WB.Path & "\" & WB.Name & "_Pictures"
sTmpFolder = sFolder & "\TmpFolder"
If FSO.FolderExists(sFolder) Then
    FSO.DeleteFolder sFolder
End If
FSO.CreateFolder sFolder
FSO.CreateFolder sTmpFolder
Application.ScreenUpdating = False
For Each WS In WB.Worksheets
    If WS.Pictures.Count > 0 Then
        WS.Copy
        i = i + 1
        ActiveWorkbook.SaveAs Filename:=sTmpFolder & "\s" & i & ".htm", FileFormat:=xlHtml
        FSO.CreateFolder sFolder & "\" & WS.Name
        FSO.CopyFile sTmpFolder & "\s" & i & "_files\*.png", sFolder & "\" & WS.Name
        ActiveWorkbook.Close False
    End If
Next
Application.ScreenUpdating = True
FSO.DeleteFolder sTmpFolder
Shell "Explorer.exe /Open,""" & sFolder & """", 1
End Sub

across post from Export Images From Multiple Sheet Into a Folder [SOLVED]

this macro working not properly..after run macro code , picture automatic create 1 duplicate for each sheet..i don't want that..
for example..i have several sheets e.g. 5 sheets and every one sheet contains 3 picture so total pictures in 5 sheets = 15.
after run macro code above, total picture success exported is 30 that is overload, should be keep 15.

any body would help me, how to solve or modify that code

.sst

 

[yky]

Can you step through the code line by line to see which line is causing the duplicate?

 

[muhammad susanto]

sorry yky..i'm newbie about macro..

 

[yky]

In your case, it is not difficult. You can do it. Click anywhere inside, not outside, the sub then hit F8. You'll see one line of code being highlighted in yellow. Hi F8 again and that line of code will be executed and the next line of code will be highlighted in yellow. This way, you know exactly which line is executed. Keep doing this while monitoring the change of the content of the directory sFolder & "\" & WS.Name. You'll see when the number of pictures changes from 0 to 15 then 30.

 

[muhammad susanto]

hi yky..
i was doing what your suggestion but still not change/work..
the line code highlighted in line i = i + 1, i was change with i=i+0 or deleted still duplicate occurred..
would you help me

 

[muhammad susanto]

Extract_Photos

this my attachment file

 

[yky]

hi yky..
i was doing what your suggestion but still not change/work..
the line code highlighted in line i = i + 1, i was change with i=i+0 or deleted still duplicate occurred..
would you help me

Do not change anything at this stage. Just go through the code line by line and see when the folders are created, when the files are copied, and when the duplicates appear. This way, you'll see which line copies the first 15 pictures and which line is responsible for the duplicates.

 

[muhammad susanto]

hi yky..i have trying edited for every line code but not work still duplicate occurred.
I'M STUCK!!

 

[yky]

I found that when Excel saves files in html format, for every picture, two are saved. This, however, happens on Excel 2016, not 2010. Excel 2010 only saves one copy of the picture.

If your goal is to export pictures in the file, you want to use other methods. Another method of saving pictures is to first paste the pictures to charts then save the charts.

 

[muhammad susanto]

I found that when Excel saves files in html format, for every picture, two are saved. This, however, happens on Excel 2016, not 2010. Excel 2010 only saves one copy of the picture.

If your goal is to export pictures in the file, you want to use other methods. Another method of saving pictures is to first paste the pictures to charts then save the charts.

i have trying in Excel 2010, actually the picture still duplicated saves (occurring duplicate)