rename a windows files with vba without moving it

[habiler]

Hello everybody

i have a directory c:\

123 - AAAAA
45678 - BBBBB

How can i rename the files in

AAAAA - 123
BBBBB - 45678

without touching the subdirectories
Thanks for all and happy new year

 

[vba_php]

I have this stored in my knowledge base from years ago:

Public Function DOScommand(comName As String, _
                           strArg As String)

'******************************************************************************
'                                                                             *
'Author: Adam Evanovich                                                       *
'Date: 10/22/2010                                                             *
'Purpose: To execute a single DOS command.                                    *
'                                                                             *
'Arguments:                                                                   *
'comName > Name of the DOS command.                                           *
'strArg > Argument syntax required for the DOS command.                       *
'         Example of both arguments are below:                                *
'                                                                             *
'         comName = "XCOPY"                                                   *
'         strArg = "c:\myTextFile.txt c:\myNewFolder\MyTextFile.txt           *
'                                                                             *
'******************************************************************************

Shell Environ("COMSPEC") & " /C " & comName & " " & strArg & ", 1"

End Function '//LL

if you want a list of DOS functions and what statements/arguments you use to execute each one, look at this website:

https://www.computerhope.com/msdos.htm

 

[isladogs]

There's no need to shell out to DOS to rename files or folders.
This is easily done using the Name statement:
https://www.devhut.net/2010/09/27/ms-access-vba-rename-file/
https://www.fmsinc.com/MicrosoftAccess/modules/examples/rename_file_folder.htm

 

[vba_php]

I do remember using NAME a couple of times, now that you mentioned it. thanks Colin.

 

[The_Doc_Man]

I'll toss in a side comment.

You can use the Name As syntax to rename a file. But file protections will apply to the folder where the files reside. You will need to be able to Write Attributes for the file and Write for the folder. If you have the broad-brush MODIFY permission set for that folder, you will probably be OK. But if the folder is NOT the one where the DB itself resides, this level of permission is not guaranteed.

Further note that it IS possible, based on your site's Group Policy settings, to have the ability to create a file that you then cannot rename or delete. (Happens if the GP starts messing with the fine-grained policies.) So bottom line? Test it first by hand. THEN if it works, you are good to go.

 

[habiler]

ok bUT i HAVE 900 FILES TO RENAME

 

[theDBguy]

ok bUT i HAVE 900 FILES TO RENAME

Hi. You could try looping through the files in your folder.

 

[Gasman]

ok bUT i HAVE 900 FILES TO RENAME

So I would name them to a different folder, else you could be renaming ones you already renamed unless you check the first part is numeric.

Use the DIR() function to get a file name, split it by the hyphen "-", construct the new file name, then issue the name command.

Repeat until no files left.?

https://www.excelfunctions.net/vba-dir-function.html

HTH

 

[vba_php]

Hi. You could try looping through the files in your folder.

based on the level of response from him guy, I would say he would need a bit more of a push than that.

 

[The_Doc_Man]

ok bUT i HAVE 900 FILES TO RENAME

There are ways to do this but they require some VBA programming and perhaps some use of the File System Object (FSO).

In brief, you use the FSO's Dir method to create a list of files to be renamed, one name at a time, each filename added to a recordset that was empty when you started, with the names being added in a loop.

Once you have that done, go back to the top of that recordset and start doing the renaming one file at a time in another loop. Doing it this way prevents you from accidentally double-dipping on a file if it happens that the new name accidentally moves the file past the current point of interest in the directory cursor. If you make the list first rather than catching the names on the fly, no chance of double dipping.

The problem is whether you feel comfortable in attempting such a thing in VBA. I can tell you that a query won't do this job at all. Queries don't have the ability to diddle with a file's name. Or at least they didn't last time I looked.

 

[theDBguy]

based on the level of response from him guy, I would say he would need a bit more of a push than that.

Luckily, Gasman and Doc came to my rescue. Thanks!

 

[Gasman]

If this is a one off job, which I would hope it was, you could just try a Batch Renamer program?

https://www.bulkrenameutility.co.uk/

 

[Cronk]

Use something like

dim strFileName as string, strNewName as string
dim str() as string

strFileName = dir("C:\temp\*.*")
do while strFileName <> ""
   str = split(strFileName)
   strNewName = "C:\temp\" & str(2) & " - " & str(0)
   name ("C:\temp\" & strFileName) as strNewName
   strFileName = dir
loop

 

[Lightwave]

This is the vba I use to get lists of files from a directory so swapping out the rs. stuff with some kind of name code as per Cronk should automatically work through a whole directory although a function like that is pretty powerful make sure you make a backup of that directory before you do it. Picking the wrong directory might be an issue!!!

First
create a table called 'tblfiles'
and ensure it has a field called 'Filename'

Public Function WriteFiles2Table()
Dim fs, f, f1, fc, s
Dim rs As DAO.Recordset
Dim folderspec
 
Set rs = CurrentDb.OpenRecordset("tblFiles")
 
folderspec = "C:\DATAFILES"
 
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(folderspec)
Set fc = f.Files
 
For Each f1 In fc
    rs.AddNew
    rs.Fields("FileName") = f1.Name
    rs.Update
Next
 
Set rs = Nothing
 
MsgBox "Finished"
 
End Function