none
How can I export a range of pictures with names from excel using VBA RRS feed

  • Question

  • 
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    

    My boss recently order me to help her to name a list of photo into repesctive code(about 2000 photos)

    What I need to do is that I need to first rename all the photos in the database according their code.And then pick up the targets photo into a new folder.

    However, I have two question.

    The first one is how can I export a list of photos in a excel file with specific name?( The photo is in column A and  the name nof the file is in columnB.) In stead of copy and paste one by one. Can I do I through VBA?

    The second question is that, after I get all the photos.How can can I transfer the target photos(2000 photos) from a list in a excel file to a new file.

    Thank you so much if anyone can help me.

    Tuesday, December 8, 2015 6:18 AM

Answers

  • Hi JAYWONG1993,

    >> The method is I saved the excel file as html, then I can get all the photos in one file with name called imaged001 and so on.

    For this steps with vba, you could use Saveas method, there is a simple code:

    Sub test()
    Dim a As Workbook
    Set a = ActiveWorkbook
    a.SaveAs "C:\Users\test\Desktop\Test\test.html", FileFormat:=xlHtml ‘target html path
    End Sub

    >> However, I find that I can only copy the first photo and cannot do other photos

    This was caused by “dic.Exists(wbName)”, it will return true when you loop the third row. If you want to ensure that the file did not create before, you could check the file name. Here is a simple code:

        wbName = copyTo & OpCoCode & ".jpg"
        'Debug.Print dic.Exists(wbName)
        If (Not dic.Exists(wbName)) Then 'ensure that we have not created this file name before
          fso.copyFile copyFile, copyTo & OpCoCode & ".jpg" 'copy the file
          dic.Add wbName, vbNullString 'add to dictionary that we have created this file
        End If

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, December 9, 2015 2:35 AM
  • Hi JAYWONG1994,

    >> I have also another question is that the method I used will have error if there is code without photo.

    For empty value in column A, I suggest you use the code below:

        If (Not dic.Exists(wbName)) And (Not IsEmpty(Range("A" & x).Value)) Then 'ensure that we have not created this file name before
          fso.copyFile copyFile, copyTo & OpCoCode & ".jpg" 'copy the file
          dic.Add wbName, vbNullString 'add to dictionary that we have created this file
        End If

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, December 10, 2015 8:36 AM

All replies

  • 
    
    Option Explicit
    Sub Create()
    Application.ScreenUpdating = False
    
       Dim lRow As Integer
       Dim x As Integer
       Dim wbName As String
       Dim fso        As Variant
       Dim dic        As Variant
       Dim OpCoCode       As String
       Dim PhotoName       As String
       Dim OpcoItemCode As String
       Dim VendorItemCode As String
       Dim colSep     As String
       Dim copyFile   As String
       Dim copyTo     As String
       Dim PathFile   As String
       Dim Sheet1 As String
       Dim Sheet2 As String
       Dim Sheet3 As String
       Dim Pass As String
       Dim wb As Workbook
    
    
        
       
       
       Set dic = CreateObject("Scripting.Dictionary") 'dictionary to ensure that duplicates are not created
       Set fso = CreateObject("Scripting.FileSystemObject") 'file scripting object for fiile system manipulation
       
       colSep = "_" 'separater between values of col A and col B for file name
       dic.Add colSep, vbNullString ' ensuring that we never create a file when both columns are blank in between
       
       'get last used row in col A
       lRow = 10 'Range("A" & Rows.Count).End(xlUp).Row
       
       x = 1
       PathFile = "C:\Users\wongja\Desktop\ProductCatalog_Kitchen2015_files\"
       copyTo = "C:\Users\wongja\Documents\Photo\" 'location where copied files need to be copied
       
       Do
        x = x + 1
        
        OpCoCode = Range("A" & x).Value 'col a value
        
        PhotoName = Range("B" & x).Value ' col b value
        PhotoName = PhotoName & ".png"
        copyFile = PathFile & PhotoName
        
        
        
        If (Not dic.Exists(wbName)) Then 'ensure that we have not created this file name before
          fso.copyFile copyFile, copyTo & OpCoCode & ".jpg" 'copy the file
          dic.Add wbName, vbNullString 'add to dictionary that we have created this file
       End If
       
       
        
            
          
       
    Loop Until x = lRow
    
    Set dic = Nothing ' clean up
    Set fso = Nothing ' clean up
    
    Application.ScreenUpdating = True
    
    End Sub
    

    Actually I have this code made by my friend

    The method is I saved the excel file as html, then I can get all the photos in one file with name called imaged001 and so on. Then I apply this code. The first columns is the name that I need to change, the second column is the the photo current name( image001). However, I find that I can only copy the first photo and cannot do other photos. Is there any problem to my code??

    Thank you so much

    Tuesday, December 8, 2015 7:46 AM
  • Hi JAYWONG1993,

    >> The method is I saved the excel file as html, then I can get all the photos in one file with name called imaged001 and so on.

    For this steps with vba, you could use Saveas method, there is a simple code:

    Sub test()
    Dim a As Workbook
    Set a = ActiveWorkbook
    a.SaveAs "C:\Users\test\Desktop\Test\test.html", FileFormat:=xlHtml ‘target html path
    End Sub

    >> However, I find that I can only copy the first photo and cannot do other photos

    This was caused by “dic.Exists(wbName)”, it will return true when you loop the third row. If you want to ensure that the file did not create before, you could check the file name. Here is a simple code:

        wbName = copyTo & OpCoCode & ".jpg"
        'Debug.Print dic.Exists(wbName)
        If (Not dic.Exists(wbName)) Then 'ensure that we have not created this file name before
          fso.copyFile copyFile, copyTo & OpCoCode & ".jpg" 'copy the file
          dic.Add wbName, vbNullString 'add to dictionary that we have created this file
        End If

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, December 9, 2015 2:35 AM
  • Thank you so much for your helping

    I have also another question is that the method I used will have error if there is code without photo.

    Let's say I have 4 photos in columns A and 6codes in columnsB like the following figures.

    Photo     Code

    Photo       123

                   456

    Photo       789

                  54654

    Photo       4562

    Photo      456761

    The problem is when I saved as html file to get photo, the 4 photo will named as photo 1 to photo 4. However, what I need to do is to name these 4 photos with repestive order. I am wondering if I use my method , will I name the photo name wrongly. For example, the 2nd photo will named as 456 instead of 789.

    I have like 100 of such excel file to do the extraction with naming. If I need to fix this one by one. It take so much time. 

    Since the excel file download from database, some of them have only the code name without photo.So the photos may not be the same order as the code.

    Thank you so much

    Wednesday, December 9, 2015 5:26 AM
  • Hi JAYWONG1994,

    >> I have also another question is that the method I used will have error if there is code without photo.

    For empty value in column A, I suggest you use the code below:

        If (Not dic.Exists(wbName)) And (Not IsEmpty(Range("A" & x).Value)) Then 'ensure that we have not created this file name before
          fso.copyFile copyFile, copyTo & OpCoCode & ".jpg" 'copy the file
          dic.Add wbName, vbNullString 'add to dictionary that we have created this file
        End If

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, December 10, 2015 8:36 AM