PDA

View Full Version : VBA Code to Rename Folders



jflst
06-19-2015, 07:42 AM
Hi All,

I'm working on a code to rename 1000s of folders and I'm afraid that I've hit a dead end. I'm not entirely sure how to proceed. I have an excel document set up like this:

Column B: Original File Source
Column C: New File Source

How do I code somethng that renames every folder in a parent folder? Any help would be much appreciated. My code is below:


Sub FolderRename()
'Declaring variables
Dim complete_pathof_folder As String, state As String
For i = 2 To Sheets("Sheet1").Range("B2").End(x1Down).Row

'Variable values
filesource = Cells(i, 2)
newfilesource = Cells(i, 6)

'Renames Original Folder Name
Name completepathof As newfolderpath
Next i
End Sub

excelliot
06-19-2015, 08:29 AM
what error you are getting using this code?

jflst
06-19-2015, 08:31 AM
I get "Run-time error '1004': Application-defined or object-defined error" message when I run the code--it highlights "For i=2 to Sheets..." when I try to debug it. Any thoughts?

excelliot
06-19-2015, 08:50 AM
did you got succes in renaming any of folder? i guess may be folder is not there coz u already renamed it ??

jflst
06-19-2015, 08:54 AM
not with this code, no, but the folder names are not changed

snb
06-19-2015, 09:00 AM
Sub M_snb()
sn = Cells(1, 2).Resize(Cells(Rows.Count, 2).End(xlUp).Row, 2)

For j = 1 To UBound(sn)
If Dir(sn(j, 1), 16) <> "" and Dir(sn(j, 2), 16) = "" Then Name sn(j, 1) As sn(j, 2)
Next
End Sub

excelliot
06-19-2015, 09:04 AM
not with this code, no, but the folder names are not changed
check for special characters in string name..i dont see any error in code...

jflst
06-19-2015, 10:11 AM
For i = 2 To Sheets("Rename File").Range("B2").End(x1Down).Row

I need to debug this. Any thoughts?

SamT
06-19-2015, 10:38 AM
Hidden Typo. Add "Option Explicit" at the top of your code page and Debug >> Compile will highlight it for you.

Hint: there is no Lower Case "L" in the following

.End(x1Down).Row

jflst
06-19-2015, 10:44 AM
now it says I need to define "i" for code line "For i = 2"...How do I define "i"?

SamT
06-19-2015, 10:48 AM
dim i as Long

jflst
06-19-2015, 11:11 AM
This is what I have now--the message "Compile Error: Variabel not defined" pops up and when I debug it it highlights "(xDown). What's wrong with this? Is the iDim in the right place--and do I need to define variables "filesource" and "newfilesource"?


Option Explicit
Sub Folder_Rename1()
'Declaring variables
Dim filesource As String
Dim newfilesource As String
Dim i As Long
For i = 2 To Sheets("Rename File").Range("B2").End(xDown).Row

'Variable values
filesource = Cells(i, 2)
newfilesource = Cells(i, 6)

'Renames Original Folder Name
Name filesource As newfilesource
Next i
End Sub

'Possible code that works
'Repeats Code Until an Empty Cell is Reached
'Do Until IsEmpty(Cells(iRow, 1))
'dCellValues(iRow) = Cells(iRow, 2).Value
'iRow = iRow + 1
'Loop

Tom Jones
06-19-2015, 11:55 AM
You wrote so
For i = 2 To Sheets("Rename File").Range("B2").End(xDown).Row
.... and had so
For i = 2 To Sheets("Rename File").Range("B2").End(xlDown).Row

(xlDown)

Kenneth Hobs
06-19-2015, 11:57 AM
Syntax error. Change xDown to XLDown.

snb
06-19-2015, 11:58 AM
Did you overlook http://www.vbaexpress.com/forum/showthread.php?52958-VBA-Code-to-Rename-Folders&p=326710&viewfull=1#post326710 ?

SamT
06-19-2015, 12:05 PM
snb's code won't work with Option Explicit, but it will rename all your files really fast. To use it, just comment out the "Option Explicit" line.

jflst
06-19-2015, 12:16 PM
If my excel file is set up as follows:

Column B: old file source
Column F: new file source

Where do I make adjustments to snb's code? And does it rename all files until an empty row?



Sub M_snb()
sn = Cells(1, 2).Resize(Cells(Rows.Count, 2).End(xlUp).Row, 2)

For j = 1 To UBound(sn)
If Dir(sn(j, 1), 16) <> "" And Dir(sn(j, 2), 16) = "" Then Name sn(j, 1) As sn(j, 2)
Next
End Sub

SamT
06-19-2015, 12:44 PM
try to do the first line in his code, then I do the second line.

jflst
06-19-2015, 12:49 PM
Sub M_snb()

sn = Cells(2, 2).Resize(Cells(Rows.Count, 2).End(xlUp).Row, 2)

Cells(2,2) refers to the row, column, which in this cse would be B2. I'm not really sure what the Dir string (second line) does. If I'm correct, it's a directory code but I don't knwo what it does.

snb
06-19-2015, 01:33 PM
Also your VBEditor has an F1 button : help.


Sub M_snb()
sn = Cells(1, 2).Resize(Cells(Rows.Count, 2).End(xlUp).Row, 5)

For j = 1 To UBound(sn)
If Dir(sn(j, 1), 16) <> "" And Dir(sn(j, 5), 16) = "" Then Name sn(j, 1) As sn(j, 5)
Next
End Sub

apo
06-20-2015, 05:23 AM
snb's code won't work with Option Explicit, but it will rename all your files really fast. To use it, just comment out the "Option Explicit" line.

ergo...

Here we raise the valid question (imo) of using a non 'Option Expicit' environment..

Why.. Why not?

Whilst, i to, do try to Dim my variables.. I am felling that it is to appease traditionalism.. rather than for any logical purpose in today's context..

Having said that.. I may be wrong.. doin the best to work it out.. :)

SamT
06-20-2015, 06:43 AM
Here we raise the valid question (imo) of using a non 'Option Expicit'(sic) environment..

When you're as proficient as snb, you don't need it.

When you're as bad a typist as I am, it is a must have item. Without it, "Expicit" and "Explicit" are two different valid variables, your code might not even raise an error, but still won't produce the right result; With it, VBA will tell me exactly where I goofed.

Which raises the question; "Why should you avoid using "Option Explicit," AKA, the VBE Spell Checker?

jflst
06-22-2015, 06:28 AM
When I try to run that I get error message "run-time error '13', type mismatch. Any idea? It tries to debug the code below

If Dir(sn(j, 1), 16) <> "" And Dir(sn(j, 3), 16) = "" Then

snb
06-22-2015, 06:45 AM
You will see with:



msgbox j
msgbox sn(j,1)
msgbox sn(j,3)
If Dir(sn(j, 1), 16) <> "" And Dir(sn(j, 3), 16) = "" Then

jflst
06-22-2015, 07:16 AM
When I execute that code, three message boxes pop up (as would be expected). The first box, from code "j" delivers a message box that reads "1". The second message box delivers "old name", and where I'm having trouble is that the third message box doesn't deliver "new name" as would be expected. Rather, it delivers a blank message box. Why?

SamT
06-22-2015, 07:56 AM
Think about lines 2 and 3 of that code and compare it to the columns in your workbook.

BTW, j = Row number