Renaming Folders

[AccessHelp]

Hello,

I want to change the names of folders in a network drive. I have a table in
an Access database containing a column for current name and new name.

Can we do it in Access? For example, go through the folders in C: drive.
If the name of folder matches with the current name in the table, change the
folder name to the one in the new name column. Else do not change (and may
be, print a report containing the directories of those folders).

I think this can be done using a batch file. I am just wondering whether we
can do it in Access. If this can only be done in a batch file, can someone
point me out to a site or a site of discussion group where I can get helps?

Thanks.

 

[Granny Spitz via AccessMonster.com]

Can we do it in Access?

Yes, as long as you have permissions to change these directory names on the
network drive. The VBA syntax to rename directories and files is like this:

Name "C:\Test\OldDirectory" As "C:\Test\NewDirectory"

 

[AccessHelp]

Hi Granny Spitz...

Thanks for the message. I am incharge of this project.

I only need this one line of code? Where do I insert it? How is Access
going to know the new name without referencing the old name?

Please tell me more.

Thanks.

 

[Granny Spitz via AccessMonster.com]

I only need this one line of code?

Yes. This one line of code will rename exactly one directory. You'll have
to substitute my directory names for your directory names, though.

Where do I insert it?

In a procedure in a VBA module. That can be a form's module, standard module,
or class module.

How is Access
going to know the new name without referencing the old name?

The computer does *exactly* what you tell it to do, so you have to tell it
what the old name is in your VBA code. In my example, where I've placed C:\
Test\OldDirectory you need to replace this with your directory path and name
that you want to change. Where I've placed C:\Test\NewDirectory you need to
replace this with your new directory path and name that you want to change
the old one to.

 

[AccessHelp]

Good morning Granny Spitz...

Thanks for the information.

In my situation, I have a table with 2 columns. One is for the names of
current directory, and the other one is the names of new directory.

Can you help me with the code that would do the following:

If the name of a directory in C: drive matches with the name of current
directory in the table, rename that directory to a new one using the new name
from the new directory column. If the code can not find the old name in the
table, do not rename the directory.

Thanks.

 

[Douglas J. Steele]

Assuming your table is named MyTable, and the two fields are OldName and
NewName respectively, try:

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT OldName, NewName FROM MyTable"
Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset(strSQL)
Do While rsCurr.EOF = True
If Len(Dir(rsCurr!OldName, vbDirectory)) > 0 Then
Name OldName As NewName
End If
Loop
rsCurr.Close
Set rsCurr = Nothing
Set dbCurr = Nothing

 

[AccessHelp]

Douglas,

Thanks for the code. I have a couple of questions on your code.

1. Do I use your code for a command button?
2. Don't I need to insert a path (e.g. c:\test\) where the name changes
will take place? How does your code know which drive or directory that I
want to change the name? I am changing the names on directories in network.

Thanks.

 

[Douglas J. Steele]

Sure, the code can be used in a command button's Click event.

I assumed that the directory names in your table were complete paths (see
what happens when you don't give all the information you should?). If
they're only folders, then yes, you'll need to append the rest of the path
in the code.

If Len(Dir("c:\test\" & rsCurr!OldName, vbDirectory)) > 0 Then
Name "c:\test\" & OldName As "c:\test\" & NewName
End If

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Douglas,

Thanks for the code. I have a couple of questions on your code.

1. Do I use your code for a command button?
2. Don't I need to insert a path (e.g. c:\test\) where the name changes
will take place? How does your code know which drive or directory that I
want to change the name? I am changing the names on directories in
network.

Thanks.

 

[AccessHelp]

Hi Douglas,

After I sent you the message, I figured that I have to put a path in. So I
put these paths for old name and new name "c:\test\OldName\" and
"c:\test\NewName\", respectively. (By the way, I use your code in a command
button.)

I clicked on the button, and it didn't happen anything. Below is the code.
Am I missing something? Thanks.

Private Sub Command0_Click()
Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT OldName, NewName FROM tblClient_Code"
Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset(strSQL)
Do While rsCurr.EOF = True
If Len(Dir(rsCurr!OldName, vbDirectory)) > 0 Then
Name OldName As NewName
End If
Loop
rsCurr.Close
Set rsCurr = Nothing
Set dbCurr = Nothing

End Sub

 

[Douglas J. Steele]

You're not necessarily missing anything, but I did. I not only forgot to put
the MoveNext statement in the loop, but I forgot to reference the recordset
in the Name statement AND my While condition was wrong (fortunately, so you
never did get the infinite loop)!

Do While rsCurr.EOF = False
If Len(Dir(rsCurr!OldName, vbDirectory)) > 0 Then
Name rsCurr!OldName As rsCurr!NewName
End If
rsCurr.MoveNext
Loop

Sorry about that.

 

[AccessHelp]

Douglas,

Thanks very much for your helps. Sorry for taking so long to reply your
message.

I have not used it to rename the folders; however, I did a test on the code.
Based on the testing, the code worked well. I will let you know the outcome
after I have renamed the folders.

Thanks again.

 

[AccessHelp]

Hi Douglas,

I just tried to run the code to rename the folders in a drive. I got the
following error message:

Path/File access error (Error 75)

Is it because someone is in the directory? It made changes to some but not
every folder. Are you familiar with the error message?

Thanks.

 

[Douglas J. Steele]

Sorry, I'm not sure what would be causing that error.

 

[AccessHelp]

Good morning Douglas,

I am just writing to let you know that your code worked. We have
sucessfully renamed the folders. Also we found the error that caused us not
to rename the folders.

Thanks again for your help.

 

[Douglas J. Steele]

Care to share what the error was?

 

[AccessHelp]

I am happy to share that with you.

We have some copiers that allow us to scan the documents into those
directories. Therefore, the copiers and folders are connected. For some
reason, a few folders were still attached to the copiers when we ran the code
(even though we had them turned off). That was the reason we got the error
message.

Thanks again for the code.

 

[Douglas J. Steele]

Makes sense. You can't rename a folder when Windows thinks it's in use.