Changing names in a directory from data contained within a spreadsheet.
lamalatrin
photographed. There are 4 photos per home loaded in the directory in
the exact same sequence as the spreadsheet. I need the 4 photos in the
directory to have the name with the data contained in [ column B ].
Example photo 1, 2, 3, 4 to be renamed to 33704374.1, 33704374.2,
33704374.3 & 33704374.4 then 5, 6, 7, 8 to be named 33123918.1,
33123918.2, 33123918.3, 33123918.4.
If the program could ask for the spreadsheet name and the directory
name would be much appreciated.
A B
C D E F
33704374-1 4604 JENNIE AVENUE KEYES CA 95328
33123918-1 4301 SUNSET TER KEYES CA 95328
33162984-1 5317 CLARK STREET KEYES CA 95328
33336029-1 5237 CLARK ST KEYES CA 95328
33612380-1 4700 NORMA WAY KEYES CA 95328
33088836-1 5329 8TH.STREET KEYES CA 95328
33807154-1 5419 LEANDRA COURT KEYES CA 95328
33075556-1 5316 CLARK ST KEYES CA 95328
33531121-1 4217 WARDA AVENUE KEYES CA 95328
33497258-1 4733 ISABELLA AVE KEYES CA 95328
33602790-1 5504 8TH STREET KEYES CA 95328
33816210-1 5528 7TH STREET KEYES CA 95328
33296745-1 5400 CLARK STREET KEYES CA 95328
32938800-1 5117 GREENDALE DR KEYES CA 95328
33034851-1 5404 ROSELENA WAY KEYES CA 95328
Auric__
> I have a spreadsheet with information on homes that need to be
> photographed. There are 4 photos per home loaded in the directory in
> the exact same sequence as the spreadsheet. I need the 4 photos in the
> directory to have the name with the data contained in [ column B ].
> Example photo 1, 2, 3, 4 to be renamed to 33704374.1, 33704374.2,
> 33704374.3 & 33704374.4 then 5, 6, 7, 8 to be named 33123918.1,
> 33123918.2, 33123918.3, 33123918.4.
>
> If the program could ask for the spreadsheet name and the directory
> name would be much appreciated.
What's the format for the photo names *before* renaming? Sequential? Is it
literally "1.jpg", "2.jpg", etc.?
--
We are more familiar with wackos than I'd like to admit.
lamalatrin
The camera name them in sequential order. The renaming is determined
by the each number in colum b in the spreadsheet. The files are in
that order with 4 pictures per address.
100_1000
100_1001
100_1002
100_1003
100_1004
100_1005
100_1006
100_1007
100_1008
100_1009
Auric__
> On Dec 31, 6:37�am, "Auric__" <not.my.r...@email.address> wrote:
>> On Wed, 30 Dec 2009 21:11:11 GMT, lamalatrin wrote:
>> > I have a spreadsheet with information on homes that need to be
>> > photographed. There are 4 photos per home loaded in the directory in
>> > the exact same sequence as the spreadsheet. I need the 4 photos in
>> > the directory to have the name with the data contained in �[ column B
>> > ]. Example photo 1, 2, 3, 4 to be renamed to 33704374.1, 33704374.2,
>> > 33704374.3 & 33704374.4 then 5, 6, 7, 8 to be named 33123918.1,
>> > 33123918.2, 33123918.3, 33123918.4.
>>
>> > If the program could ask for the spreadsheet name and the directory
>> > name would be much appreciated.
>>
>> What's the format for the photo names *before* renaming? Sequential? Is
>> it literally "1.jpg", "2.jpg", etc.?
>
> The camera name them in sequential order. The renaming is determined
> by the each number in colum b in the spreadsheet. The files are in
> that order with 4 pictures per address.
>
> 100_1000
> 100_1001
> 100_1002
> 100_1003
> 100_1004
> 100_1005
> 100_1006
> 100_1007
> 100_1008
> 100_1009
I assume that it's actually "100_1000.jpg", etc.
This works from within Excel itself (i.e. not from standalone VB -- in the
Excel interface press Alt+F11, then in the VB editor add a new module to
the spreadsheet, then paste this in and save). To use it from within Excel,
select the row that you want to use for the renaming (i.e. the row with
"32938800-1" or whatever -- THIS STEP IS IMPORTANT!), then press Alt+F8,
select picRenamer, and click run. Note that there are some issues with this
code, such as the fact that you'll get an error if you try to rename a file
to one that already exists.
'-----begin-----'
Sub picRenamer()
Const picDir As String = "C:\Pics"
Const extension As String = ".jpg"
Dim newName As String, tmpStr As String, cDir As String
Dim fileCount As Long, L0 As Long, L1 As Long
ReDim oldFiles(0) As String
cDir = curDir$
newName = Cells(ActiveCell.Row, 1).Value
fileCount = -1
'build and sort array of the current names
ChDir picDir
tmpStr = Dir$("*" & extension)
Do While Len(tmpStr)
fileCount = fileCount + 1
ReDim Preserve oldFiles(fileCount)
For L0 = 0 To fileCount - 1 'last one is empty, don't need to check
'this is a very simple (and slow-ish) 1-at-a-time sort routine
If tmpStr < oldFiles(fileCount) Then
'insert tmpstr
For L1 = fileCount To L0 + 1 Step -1
oldFiles(L1) = oldFiles(L1 - 1)
Next
oldFiles(L0) = tmpStr
Exit For
End If
Next
If Len(oldFiles(fileCount)) < 1 Then oldFiles(fileCount) = tmpStr
tmpStr = Dir$
Loop
'magic here
For L0 = 0 To fileCount
Name oldFiles(L0) As newName & "." & CStr(L0 + 1) & extension
Next
ChDir cDir
End Sub
'-----end-----'
--
The plural of anecdote is not data.
-- Richard Brinner
Auric__
> Const picDir As String = "C:\Pics"
> Const extension As String = ".jpg"
Stupid swiss cheese memory... The above two MUST BE EDITED BY YOU to reflect
where the pictures are stored, and the correct extension. (The above is
normal for JPEGs; other formats use other extensions. If you use more than
one extension, my code won't work as written.)
--
This movie is awesome:
Val Kilmer contracts mumps and then a huge goitre appears on his neck.
Can Cuba Gooding reattach his hands in time to cure him?
Druidic Ranger
This is not what the group was chartered for. It says very plainly in
the
FAQ that this is a place for discussing the human genome.
--
Snake Eyes
Druidic Ranger
Oh I get it, these are DNA codekeys. Funny thing is I once saw a
33497258 in the supermarket checking me out.
--
Snake Eyes
Druidic Ranger
The colum? Is that the bone between the shoulder and the left
ventricle?
--
Snake Eyes
Druidic Ranger
Actors play different characters in movies.
Druidic Ranger
this is comp.lang.basic.visual.misc, not
comp.lang.visual_basic_for_applications...
--
Snake Eyes
Auric__
Yeah, funny thing about that. The "misc" in the name stands for
"miscellaneous". Look that word up in a dictionary.
--
This is why I have an email wall of shame.
The Ydesicant
What about classifying the human sense of taste as a visual basic
excerpt? Does that fall under miscellaneous too?
--
You float like a shame marshmallow.