Solved

rename in batch ,many photos in a dir , from a xls list column data ?

Posted on 2013-01-14
3
239 Views
Last Modified: 2013-01-14
i have a dir   "Photos_ID1"  in desktop , with photos named  all ready from a list of column ''B''  in sheet  "Prod_Photos" also there is a photo called "No_photo_yet.jpg"


   " A "                                         " B "          Dir  "Photos_ID1"      final result on dir
Su10000-M95-C413               156-0099          156-0099.jpg          Su10000-M95-C413.jpg
Su10001-M520-C41           140-1829          140-1829.jpg         Su10001-M520-C41.jpg                
Su10002-M95-C413               156-0145           156-0145.jpg       Su10002-M95-C413.jpg

1.         i want to compare the "B"  list with dir /photos and if i dont have photo with the selected value ,  i want  in  "B" column  to replace the cell  with value "No_photo_yet"

2nd ....i want to rename  the photos in dir ,with the "A" column

Do this for 6000 values in "B" or "A"
   

many thanks , to everyone that helps.
0
Comment
Question by:tonyantony
  • 2
3 Comments
 
LVL 24

Accepted Solution

by:
Steve earned 250 total points
ID: 38774116
with values listed in A & B (nothing in Columns C and D)...

Save the attached file in the "Photos_ID1" folder and run the following macro...

Sub Change_JPEG()

strPath = ThisWorkbook.Path
Dim xx As String
Dim arr

LastDataRow = Cells(Rows.Count, 1).End(xlUp).Row

arr = Range("A1:D" & LastDataRow).Value

For x = 1 To UBound(arr)
If Len(Dir(strPath & "\" & arr(x, 2) & ".jpg")) <> 0 Then
    Name strPath & "\" & arr(x, 2) & ".jpg" As strPath & "\" & arr(x, 1) & ".jpg"
    arr(x, 3) = strPath & "\" & arr(x, 2) & ".jpg"
    arr(x, 4) = strPath & "\" & arr(x, 1) & ".jpg"
Else
    arr(x, 3) = "No_photo_yet"
    arr(x, 4) = ""
End If
Next x
Range("A1:D" & LastDataRow).Value = arr

End Sub

Open in new window


Option 2 columns:
Sub Change_JPEG()

strPath = ThisWorkbook.Path
Dim xx As String
Dim arr

LastDataRow = Cells(Rows.Count, 1).End(xlUp).Row

arr = Range("A1:B" & LastDataRow).Value

For x = 1 To UBound(arr)
If Len(Dir(strPath & "\" & arr(x, 2) & ".jpg")) <> 0 Then
    Name strPath & "\" & arr(x, 2) & ".jpg" As strPath & "\" & arr(x, 1) & ".jpg"
Else
    arr(x, 2) = "No_photo_yet"
End If
Next x
Range("A1:B" & LastDataRow).Value = arr
End Sub

Open in new window

Dir-change.xlsm
Dir-change.xlsm
0
 

Author Closing Comment

by:tonyantony
ID: 38775738
i tested 1st macro ,  no need for the other, works fine many thanks , good job
0
 
LVL 24

Expert Comment

by:Steve
ID: 38775758
Glad to help, I just did the second as it was what you asked for, but I thought leaving the original data alone was a cleaner result.

ATB
Steve.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now