modify filenames in a folder using dlookup

[laffeg]

hi
my vb skills are very limited and I need help with a task to rename 1500 jpg files in a folder ( left hand side of attached screen grab )
I've got the data on the right hand side in an access table
i need code to loop thru the folder and rename such that in simple terms the new filename = left(filename, instr(" ")-1 & dlookup the 3 fields separated by underscores & .jpg

i'm happy to do the string manipulations

just need help with the declaring of variables, objects and loop

any help appreciated

 

[namliam]

dim rs as dao.recordset
set rs = currentdb.openrecordset("yourtable")
do while not rs.eof
    rename filename, rs!YourField &"_"& rs!YourOtherField 
    rs.movenext
loop

rs.close
set rs = nothing

Not sure about rename though, but these should be the bones I think.

 

[laffeg]

hi
would that not loop thru the lookup table ?
i need it to loop thru the filenames in the folder
thanks

 

[CJ_London]

something like

dim f as string
f=dir("path to folder")
While f<>""
    'rename your file - check the name and filecopy functions for doing this

     f=dir
wend

 

[vba_php]

i need it to loop thru the filenames in the folder

hey laffeg. I use allen browne's code *extensively* for this very thing you're trying to do. but his code just list files in the windows directory. for what you're doing, just use the NAME statement like CJ just said. CJ's code is a lot simpler than this, but this also works. all you have to do is copy all his code from here: http://allenbrowne.com/ser-59.html , and simply substitute the *name* statement into the section of code that lists the name of the files. so, this is one of the modified functions that is in my knowledgebase. This was used to rename hundreds of files on my local computer to index them on a pattern matching basis for an easy batch-upload process for my website. look for "".txt", ".py"" in the code to see the manipulated line.

Public Function ListFiles(strPath As String, Optional strFileSpec As String, _
    Optional bIncludeSubfolders As Boolean, Optional lst As ListBox)
On Error GoTo Err_Handler
    'Purpose:   List the files in the path.
    'Arguments: strPath = the path to search.
    '           strFileSpec = "*.*" unless you specify differently.
    '           bIncludeSubfolders: If True, returns results from subdirectories of strPath as well.
    '           lst: if you pass in a list box, items are added to it. If not, files are listed to immediate window.
    '               The list box must have its Row Source Type property set to Value List.
    'Method:    FilDir() adds items to a collection, calling itself recursively for subfolders.
    Dim colDirList As New Collection
    Dim varItem As Variant
   
    Call FillDir(colDirList, strPath, strFileSpec, bIncludeSubfolders)
   
    'Add the files to a list box if one was passed in. Otherwise list to the Immediate Window.
    If lst Is Nothing Then
        For Each varItem In colDirList
            Name CStr(varItem) As Replace(CStr(varItem), ".txt", ".py") 'this is the only line from Browne's code that needs to change
        Next
    Else
        For Each varItem In colDirList
        lst.AddItem varItem
        Next
    End If

Exit_Handler:
    Exit Function

Err_Handler:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Exit_Handler
End Function

you can use CJ's KB, or this one in conjunction with both mailman's stuff and CJ's. if you do use Allen Browne's code on that page I referenced, you have to copy all of it into a module, then write this in the immediate window:

?ListFiles("directory where files are","*.fileExtensionHere",True)

 

[laffeg]

so i've copied the code into a module and modified the Name line as below

theory being replace from first space onwards for 16 characters with result from lookup field

Name CStr(varItem) As Replace(CStr(varItem), mid(varitem,instr(varitem," "),16), dlookup("[pddescription]","products","[pdmn]=left(varitem,4)")

however i get expected: list separator or )

am i on the right track ? or way of course

 

[vba_php]

Name CStr(varItem) As Replace(CStr(varItem), mid(varitem,instr(varitem," "),16), dlookup("[pddescription]","products","[pdmn]=left(varitem,4)")

however i get expected: list separator or )

ok, so if you're using Browne's code good for you. you're probably getting that error because you're not concatenating out your 3rd argument of dlookup. try this instead:

mid(cstr(varitem),instr(cstr(varitem)," "),16), dlookup("[pddescription]","products","[pdmn]='" & left(cstr(varitem),4) & "')"

make sure you take that code and ensure that all the "(" and ")" markings are in the right place, cuz I did not test it! you've got plenty of nestings in there. you should also do this before you even get that line of code that uses NAME, so you don't confuse yourself with a million string conversion functions:

dim strFile as string
strFile = cstr(varItem)

 

[namliam]

hi
would that not loop thru the lookup table ?
i need it to loop thru the filenames in the folder
thanks

Yes but considering the filename (excluding extension) is one of the columns I dont really see the problem?

 

[laffeg]

thanks for all the advice, i've learned a few things for future tasks.
I was on the road a lot yesterday and my client beat me to it by using this Excel suggestion
https://benholland.me/tutorials/2011/11/11/rename-multiple-files-using-excel-in-windows.html

 

[vba_php]

thanks for all the advice, i've learned a few things for future tasks.
I was on the road a lot yesterday and my client beat me to it by using this Excel suggestion
https://benholland.me/tutorials/2011/11/11/rename-multiple-files-using-excel-in-windows.html

so is the issue solved?

 

[laffeg]

yes thanks. i've not tried finishing the access vba code but client is sorted.

 

[vba_php]

so what else do you have to do? the issue isn't resolved then?

 

[laffeg]

the client renamed the files using the excel method i posted. so job done. doesn't need me after all
i've designed a few access databases for them and normally get by with my limited vb skills.

 

[vba_php]

the client renamed the files using the excel method i posted. so job done. doesn't need me after all
i've designed a few access databases for them and normally get by with my limited vb skills.

I've seen that happen a lot, laffeg. that's a HUGE problem with employers, and they all pull the same crap. they ask for help, but as they're waiting for assistance, they also ask a million other people at the same time and search the internet too. so if I were you, I'd try and detect who the illegimiate employers are that do that. there are MANY out there. I hope you can. you have to have a good radar.