Rename files based on recordset names (1 Viewer)

nortonm

Registered User.
Local time
Today, 13:45
Joined
Feb 11, 2016
Messages
49
Hi, I'm struggling to concoct some vba to rename some files in a folder. The problem came about because a web portfolio system at work exports files for students with a SQL UUID - meaningless string - instead of the Title. So they have a folder full of files they don't recognise without opening up each one and renaming it, too much esp. when lots of files. I can access the web system table from Access, and I can get the windows folder path (using 'FileDialog) into a text box called txtFolderPath. But the next bit is foxing me. I can get a recordset/query with the UUID and the 'Title' in the same record (along with the UserID of the student), thereby matching the correct 'Title' to the UUID. So I want to: Rename windows file to Recordset.Title where windows file = Recordset.UUID, so I guess the vba is going to have to loop through until each matching record is renamed to 'Title'.

I'm a long way off, but I've started with
Dim strFile As String
Dim strPath As String

strFile = Dir(strPath & "*.*")
strPath = Me.txtFolderPath

then a looping function is needed and i can't figure it out, followed by something like

Name strPath & strFile As strPath & Recordset.Title

But I get no action happening, or loop errors. I've probably tried too many variants to post here, I may just may make it sound more confusing than I already have!

Does anything about this make sense?
 
Last edited:

Ranman256

Well-known member
Local time
Today, 09:45
Joined
Apr 9, 2015
Messages
4,339
when it loops thru the files in the folder,
it gets the source name,
but where is this target name: Recordset.title?

theres no rst needed for file rename.
is it the current recordset you are using?
1. recordsets dont have names.
2 if so, every file in the folder is renamed to that rst?

(queries have names)
confused.
 

nortonm

Registered User.
Local time
Today, 13:45
Joined
Feb 11, 2016
Messages
49
Thanks for prompt reply! I have to nip to dentist now, but I'll tidy up what I wrote when I get back.

By 'Recordset' I meant the name of my recordset, which is actually 'frm_qryChangeFNameRS_subform'.

Many Thanks, I'll post again shortly.
 

nortonm

Registered User.
Local time
Today, 13:45
Joined
Feb 11, 2016
Messages
49
Hi,

I got the below code to work. But it didn't loop automatically, I had to keep pressing the command button to make it find the next match and change the name.

Also, After changing the file name fine the first 5 times, it gives the error the 6th time: "Run-time error '53': File not found". There are 61 records in the Recordset, but only 59 in the windows folder, maybe it's erroring out when it doesn't find a match in the windows folder? Can I put instruction in to disregard no match and just keep going?

Also, the reason for adding the last 10 characters on the UUID is because it's random and helps overcome the problem of users giving duplicate titles to their files, and it also pulls in the file extension, which the Title field doesnt have.

Private Sub cmdRenameTEST_Click()
Dim strFile As String
Dim strPath As String
Dim MyDB As Database
Dim MyRS As Recordset

strPath = Me.txtFolderPath
strFile = Dir(strPath & "*.*")
Set MyDB = CurrentDb()
Set MyRS = Me.frm_qryChangeFNameRS_subform.Form.RecordsetClone
MyRS.MoveFirst
Do Until MyRS.EOF
'PROCESS SECTION****************************************************
If strFile = MyRS!ModifiedName Then
Name strFile As strPath & MyRS!Title & "_" & Right([strFile], 10)
End If
'END OF PROCESS SECTION**********************************************
MyRS.MoveNext
Loop
End Sub

For info, an example of the 'Title' and 'ModifiedName' (UUID) data is:
"Evidence for Clinical Liason Meeting" and "0b1bd0fe-2cd6-4c42-a16c-230d6ff3fb65.docx"

Many Thanks for reading.
 

Ranman256

Well-known member
Local time
Today, 09:45
Joined
Apr 9, 2015
Messages
4,339
the name of the QUERY,
or the name of the FORM?
(still not a recordset)

you use both nomenclatures in your reply.
 

nortonm

Registered User.
Local time
Today, 13:45
Joined
Feb 11, 2016
Messages
49
Sorry - yes, the form is actually the query dragged onto the form, so it functions like a recordset - so the recordset is referenced as "frm_qryChangeFNameRS_subform", but that subform's underlying query is actually "qryChangeFNameRS".

The Subform is on the main form frmRenameExportedFiles, which is where I put the userid in to filter the query, and also put the filedialog command to get the windows folder path.
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:45
Joined
Sep 21, 2011
Messages
14,052
You are looping through the recordset, but not the files in the folder?

https://www.techonthenet.com/excel/formulas/dir.php

Hi,

I got the below code to work. But it didn't loop automatically, I had to keep pressing the command button to make it find the next match and change the name.

Also, After changing the file name fine the first 5 times, it gives the error the 6th time: "Run-time error '53': File not found". There are 61 records in the Recordset, but only 59 in the windows folder, maybe it's erroring out when it doesn't find a match in the windows folder? Can I put instruction in to disregard no match and just keep going?

Also, the reason for adding the last 10 characters on the UUID is because it's random and helps overcome the problem of users giving duplicate titles to their files, and it also pulls in the file extension, which the Title field doesnt have.

Private Sub cmdRenameTEST_Click()
Dim strFile As String
Dim strPath As String
Dim MyDB As Database
Dim MyRS As Recordset

strPath = Me.txtFolderPath
strFile = Dir(strPath & "*.*")
Set MyDB = CurrentDb()
Set MyRS = Me.frm_qryChangeFNameRS_subform.Form.RecordsetClone
MyRS.MoveFirst
Do Until MyRS.EOF
'PROCESS SECTION****************************************************
If strFile = MyRS!ModifiedName Then
Name strFile As strPath & MyRS!Title & "_" & Right([strFile], 10)
End If
'END OF PROCESS SECTION**********************************************
MyRS.MoveNext
Loop
End Sub

For info, an example of the 'Title' and 'ModifiedName' (UUID) data is:
"Evidence for Clinical Liason Meeting" and "0b1bd0fe-2cd6-4c42-a16c-230d6ff3fb65.docx"

Many Thanks for reading.
 

nortonm

Registered User.
Local time
Today, 13:45
Joined
Feb 11, 2016
Messages
49
Thanks Gasman...it does appear to loop through both sets of records as it successfully matches the File name in Windows with the UUID in the recordset, then replaces the File name in windows with the more friendly 'Title' field in the recordset. It just stops after replacing 5 filenames successfully with the error "Run-time error '53': File not found". Also, rather than looping through the records, it does one, then you have to press the command button again and it does another one - etc

Thanks
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:45
Joined
Sep 21, 2011
Messages
14,052
Am I missing something then.?

You get a file name, which will be the first in the folder and then loop through the whole recordset looking for that name and if a match then rename.

That is it. One file renamed. Then you repeat the procedure by running the sub again.?

Why not read the recordset, then look for a file by that name, if found rename, and repeat.?

Thanks Gasman...it does appear to loop through both sets of records as it successfully matches the File name in Windows with the UUID in the recordset, then replaces the File name in windows with the more friendly 'Title' field in the recordset. It just stops after replacing 5 filenames successfully with the error "Run-time error '53': File not found". Also, rather than looping through the records, it does one, then you have to press the command button again and it does another one - etc

Thanks
 

Cronk

Registered User.
Local time
Tomorrow, 00:45
Joined
Jul 4, 2013
Messages
2,770
I agree with Gasman. A lot more simpler to search for the filename in the recordset eg
Code:
myRS.findfirst "ModifiedName='" & strFile & "'"
if myRS.noMatch then
  Msgbox "not found"
else
  name ...
endif
But you did notice Gasman's other comment that you are not going through all files in the folder, just the first file. To do so you will need another loop outside the loop going through the recordset.
 

nortonm

Registered User.
Local time
Today, 13:45
Joined
Feb 11, 2016
Messages
49
Hi - Thanks Gasman and Cronk for your contributions

I have amended the script in line with your recommendations - posted below. I'm not sure how to get it to loop through all files though as you suggetsed, My understanding of looping needs work - I'm reading up on it at the mo'...Any hints as to what I would need to do for the script to loop and check all the files instead of just the first?

I also think I know why that error "Run-time error '53': File not found" comes up now - it changes 5 file names fine, but on the 6th one (out of 60 files) the error comes up, and the File Name (Title) is in the recordset multiple times - it's when it finds a duplicate 'Title'. The users do this often. I would need to get the script to somehow accept/ignore duplicate filenames (in the recordset as 'Title' ) and just change the Windows filename anyway (as Title plus last 10 characters of ModifiedName).

Dim strFile As String
Dim strPath As String
Dim MyDB As Database
Dim MyRS As Recordset


strPath = Me.txtFolderPath
strFile = Dir(strPath & "*.*")

Set MyDB = CurrentDb()
Set MyRS = Me.frm_qryChangeFNameRS_subform.Form.RecordsetClone


MyRS.FindFirst "ModifiedName= '" & strFile & "'"

If strFile = MyRS!ModifiedName Then
Name strFile As strPath & MyRS!Title & "_" & Right([strFile], 10)
End If

Do Until MyRS.EOF
MyRS.MoveNext
Loop
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:45
Joined
Sep 21, 2011
Messages
14,052
Firstly, why are you not using the .Nomatch property as Cronk showed you.

My thoughts were to read through the recordset and then look for the file in the folder.
Cronk's approach was to read through the folder and look for the file in the recordset as you appear to be doing now.

So you start the loop as you have done with a
Code:
strFile = Dir(strPath & "*.*")

Do While Len(strFile) > 0
    MyRS.FindFirst "ModifiedName='" & strFile & "'"
    If Not MyRS.NoMatch Then
        Name strFile As strPath & MyRS!Title & "_" & Right([strFile], 10)
    End If
    strFile = Dir()
Loop

http://codevba.com/office/loop_files_in_folder.htm

HTH
 

nortonm

Registered User.
Local time
Today, 13:45
Joined
Feb 11, 2016
Messages
49
Ah, brilliant, thankyou Gasman and Cronk. I couldn't get the nomatch to work, but then I think i may have missed off the Do While...I'll have a good look at the link you gave me as well, and bottom out this looping function.

All I have to do now is get it stop giving me the 'Run-time error '53': File not found' error, and the rename loop halts at that point. It's when it finds a duplicate Title, this user has 6 files all with the same name, they were in different folders, but they've all had to be extracted to a zip file. However, I'll try and deal with that separately, you've helped my greatly there, thanks.
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:45
Joined
Sep 21, 2011
Messages
14,052
I am not sure which would be the most efficient.
Reading recordset and look for file in folder
or
Read folder look for file in recordset.

I do not know what would happen with you renaming the files in the folder if you took the second approach.

Perhaps a better approach would be to put the files in a common folder, process that and then copy the renamed file to the correct folder and then delete it from the process folder?
 

Users who are viewing this thread

Top Bottom