rename a windows files with vba without moving it (1 Viewer)
- Thread starter habiler
- Start date
vba_php
Forum Troll
- Local time
- Today, 01:17
- Joined
- Oct 6, 2019
- Messages
- 2,880
I have this stored in my knowledge base from years ago:
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
Code:
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 '//LLhttps://www.computerhope.com/msdos.htm
isladogs
MVP / VIP
- Local time
- Today, 07:17
- Joined
- Jan 14, 2017
- Messages
- 18,218
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
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
- Local time
- Today, 01:17
- Joined
- Feb 28, 2001
- Messages
- 27,179
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.
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.
- Local time
- Yesterday, 23:17
- Joined
- Oct 29, 2018
- Messages
- 21,471
Hi. You could try looping through the files in your folder.
Gasman
Enthusiastic Amateur
- Local time
- Today, 07:17
- Joined
- Sep 21, 2011
- Messages
- 14,287
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
Forum Troll
- Local time
- Today, 01:17
- Joined
- Oct 6, 2019
- Messages
- 2,880
based on the level of response from him guy, I would say he would need a bit more of a push than that.
- Local time
- Today, 01:17
- Joined
- Feb 28, 2001
- Messages
- 27,179
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.
- Local time
- Yesterday, 23:17
- Joined
- Oct 29, 2018
- Messages
- 21,471
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
Enthusiastic Amateur
- Local time
- Today, 07:17
- Joined
- Sep 21, 2011
- Messages
- 14,287
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/
https://www.bulkrenameutility.co.uk/
Cronk
Registered User.
- Local time
- Today, 16:17
- Joined
- Jul 4, 2013
- Messages
- 2,772
Use something like
Code:
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
Ad astra
- Local time
- Today, 07:17
- Joined
- Sep 27, 2004
- Messages
- 1,521
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'
First
create a table called 'tblfiles'
and ensure it has a field called 'Filename'
Code:
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
Last edited:
