Rename Batch of Files in VBA
 

I'm using Excel 2000.
I have a big macro to create - I don't expect the group to do all my work,
information on how to get started would be good for now.

My goal:
I have a directory ("C:\My Documents\Archive") which contains multiple
subdirectories, each of which contains files to be renamed. Some
subdirectories also have their own subfolders.
Those files whose name starts with the string "File" needs that string
replaced with the folder name. If the file does not start with the string
"File", the immediate folder name gets added to the start of the folder
name.
The filelist will include non-Excel files.

I can do the string replacing bit, it's the creating a list of folders and
their content files, and then getting the immediate folder and using that to
rename the files that I have no idea about, and Google isn't helping much :)

Thank in advance.

Darren

Rename Batch of Files in VBA
 

Darren,

Here's some code to get you started. You'll need to go to the
Tools menu in VBA, choose References, and set a reference to the
Windows Scripting Runtime library.

Sub Start()

Dim FSO As Scripting.FileSystemObject
Dim TopFolder As Scripting.Folder

Set FSO = New Scripting.FileSystemObject
Set TopFolder = FSO.GetFolder("C:\FolderName") '<< CHANGE
ProcessOneFolder FSO, TopFolder

End Sub

Sub ProcessOneFolder(FSO As Scripting.FileSystemObject, _
F As Scripting.Folder)

Dim OneFolder As Scripting.Folder
Dim OneFile As Scripting.File

For Each OneFolder In F.SubFolders
ProcessOneFolder FSO, OneFolder
Next OneFolder
For Each OneFile In F.Files
Debug.Print OneFile.Name, OneFile.ParentFolder.Path,
OneFile.Path
Next OneFile

End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Darren Hill" wrote in message
...
I'm using Excel 2000.
I have a big macro to create - I don't expect the group to do
all my work,
information on how to get started would be good for now.

My goal:
I have a directory ("C:\My Documents\Archive") which contains
multiple
subdirectories, each of which contains files to be renamed.
Some
subdirectories also have their own subfolders.
Those files whose name starts with the string "File" needs that
string
replaced with the folder name. If the file does not start with
the string
"File", the immediate folder name gets added to the start of
the folder
name.
The filelist will include non-Excel files.

I can do the string replacing bit, it's the creating a list of
folders and
their content files, and then getting the immediate folder and
using that to
rename the files that I have no idea about, and Google isn't
helping much :)

Thank in advance.

Darren

Rename Batch of Files in VBA
 

Hi Darren

Wow. Well, Google would actually help a lot if you broke down the big task
into small definite problems. Ok, first, you rename a file like this:

Sub test()
Name "C:\Temp\MyApp.ini" As "C:\Temp\WifesApp.ini"
End Sub

And here's one among several ways to scan folders and files from a root.
Display the immediate window in the VBE while running it, it shows all
Debug.Print actions. Note also the "*.*" in the file code, change to "*.xl*"
to limit to Excel files. Or whatever.

Sub test()
Call LookForDirectories("C:\Documents and Settings\All Users")
End Sub
Sub LookForDirectories(ByVal DirToSearch As String)
Dim counter As Integer
Dim i As Integer
Dim Directories() As String
Dim Contents As String

counter = 0
DirToSearch = DirToSearch & "\"
Contents = Dir(DirToSearch, vbDirectory)
Do While Contents < ""
If Contents < "." And Contents < ".." Then
If (GetAttr(DirToSearch & Contents) And _
vbDirectory) = vbDirectory Then
counter% = counter% + 1
ReDim Preserve Directories(counter)
Directories(counter) = DirToSearch & Contents
End If
End If
Contents = Dir
Loop
If counter = 0 Then Exit Sub
For i = 1 To counter
Debug.Print
Debug.Print "*********************"
Debug.Print "Folder " & Directories(i)
Debug.Print "*********************"
GetFilesInDirectory Directories(i)
LookForDirectories Directories(i)
Next i
End Sub

Sub GetFilesInDirectory(ByVal DirToSearch As String)
Dim NextFile As String
On Error Resume Next
With ActiveSheet
NextFile = Dir(DirToSearch & "\" & "*.*")
Do Until NextFile = ""
Debug.Print "Folder " & DirToSearch, NextFile
NextFile = Dir()
Loop
End With
End Sub

--
HTH. Best wishes Harald
Followup to newsgroup only please

"Darren Hill" skrev i melding
...
I'm using Excel 2000.
I have a big macro to create - I don't expect the group to do all my work,
information on how to get started would be good for now.

My goal:
I have a directory ("C:\My Documents\Archive") which contains multiple
subdirectories, each of which contains files to be renamed. Some
subdirectories also have their own subfolders.
Those files whose name starts with the string "File" needs that string
replaced with the folder name. If the file does not start with the string
"File", the immediate folder name gets added to the start of the folder
name.
The filelist will include non-Excel files.

I can do the string replacing bit, it's the creating a list of folders and
their content files, and then getting the immediate folder and using that
to
rename the files that I have no idea about, and Google isn't helping much
:)

Thank in advance.

Darren

Rename Batch of Files in VBA
 

That was quick :)

I don't appear to have a "Windows Scripting Runtime" in the list, but I do
have a "Microsoft Scripting Runtime" - is that the one?

--
Darren
"Chip Pearson" wrote in message
...
Darren,

Here's some code to get you started. You'll need to go to the
Tools menu in VBA, choose References, and set a reference to the
Windows Scripting Runtime library.

Sub Start()

Dim FSO As Scripting.FileSystemObject
Dim TopFolder As Scripting.Folder

Set FSO = New Scripting.FileSystemObject
Set TopFolder = FSO.GetFolder("C:\FolderName") '<< CHANGE
ProcessOneFolder FSO, TopFolder

End Sub

Sub ProcessOneFolder(FSO As Scripting.FileSystemObject, _
F As Scripting.Folder)

Dim OneFolder As Scripting.Folder
Dim OneFile As Scripting.File

For Each OneFolder In F.SubFolders
ProcessOneFolder FSO, OneFolder
Next OneFolder
For Each OneFile In F.Files
Debug.Print OneFile.Name, OneFile.ParentFolder.Path,
OneFile.Path
Next OneFile

End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Darren Hill" wrote in message
...
I'm using Excel 2000.
I have a big macro to create - I don't expect the group to do
all my work,
information on how to get started would be good for now.

My goal:
I have a directory ("C:\My Documents\Archive") which contains
multiple
subdirectories, each of which contains files to be renamed.
Some
subdirectories also have their own subfolders.
Those files whose name starts with the string "File" needs that
string
replaced with the folder name. If the file does not start with
the string
"File", the immediate folder name gets added to the start of
the folder
name.
The filelist will include non-Excel files.

I can do the string replacing bit, it's the creating a list of
folders and
their content files, and then getting the immediate folder and
using that to
rename the files that I have no idea about, and Google isn't
helping much :)

Thank in advance.

Darren

Rename Batch of Files in VBA
 

Darren,

Yes, the correct library is "Microsoft Scripting Runtime". Sorry
about that.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Darren Hill" wrote in message
...
That was quick :)

I don't appear to have a "Windows Scripting Runtime" in the
list, but I do
have a "Microsoft Scripting Runtime" - is that the one?

--
Darren
"Chip Pearson" wrote in message
...
Darren,

Here's some code to get you started. You'll need to go to
the
Tools menu in VBA, choose References, and set a reference to
the
Windows Scripting Runtime library.

Sub Start()

Dim FSO As Scripting.FileSystemObject
Dim TopFolder As Scripting.Folder

Set FSO = New Scripting.FileSystemObject
Set TopFolder = FSO.GetFolder("C:\FolderName") '<< CHANGE
ProcessOneFolder FSO, TopFolder

End Sub

Sub ProcessOneFolder(FSO As Scripting.FileSystemObject, _
F As Scripting.Folder)

Dim OneFolder As Scripting.Folder
Dim OneFile As Scripting.File

For Each OneFolder In F.SubFolders
ProcessOneFolder FSO, OneFolder
Next OneFolder
For Each OneFile In F.Files
Debug.Print OneFile.Name, OneFile.ParentFolder.Path,
OneFile.Path
Next OneFile

End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Darren Hill" wrote in message
...
I'm using Excel 2000.
I have a big macro to create - I don't expect the group to
do
all my work,
information on how to get started would be good for now.

My goal:
I have a directory ("C:\My Documents\Archive") which
contains
multiple
subdirectories, each of which contains files to be renamed.
Some
subdirectories also have their own subfolders.
Those files whose name starts with the string "File" needs
that
string
replaced with the folder name. If the file does not start
with
the string
"File", the immediate folder name gets added to the start
of
the folder
name.
The filelist will include non-Excel files.

I can do the string replacing bit, it's the creating a list
of
folders and
their content files, and then getting the immediate folder
and
using that to
rename the files that I have no idea about, and Google
isn't
helping much :)

Thank in advance.

Darren

Rename Batch of Files in VBA
 

When I try the code below, with the following line
Set TopFolder = FSO.GetFolder("C:\FolderName")
changed to
Set TopFolder = FSO.GetFolder("C:\Documents and Settings")
I get an error in the called Sub, the line:

Debug.Print OneFile.Name, OneFile.ParentFolder.Path,
OneFile.Path

The msg is "Invalid Use of Property"
and the Onefile.Path property is highlighted.

I've tried a couple of different folder names, so they aren't the problem.
Any idea?

Darren


"Chip Pearson" wrote in message
...
Darren,

Here's some code to get you started. You'll need to go to the
Tools menu in VBA, choose References, and set a reference to the
Windows Scripting Runtime library.

Sub Start()

Dim FSO As Scripting.FileSystemObject
Dim TopFolder As Scripting.Folder

Set FSO = New Scripting.FileSystemObject
Set TopFolder = FSO.GetFolder("C:\FolderName") '<< CHANGE
ProcessOneFolder FSO, TopFolder

End Sub

Sub ProcessOneFolder(FSO As Scripting.FileSystemObject, _
F As Scripting.Folder)

Dim OneFolder As Scripting.Folder
Dim OneFile As Scripting.File

For Each OneFolder In F.SubFolders
ProcessOneFolder FSO, OneFolder
Next OneFolder
For Each OneFile In F.Files
Debug.Print OneFile.Name, OneFile.ParentFolder.Path,
OneFile.Path
Next OneFile

End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Darren Hill" wrote in message
...
I'm using Excel 2000.
I have a big macro to create - I don't expect the group to do
all my work,
information on how to get started would be good for now.

My goal:
I have a directory ("C:\My Documents\Archive") which contains
multiple
subdirectories, each of which contains files to be renamed.
Some
subdirectories also have their own subfolders.
Those files whose name starts with the string "File" needs that
string
replaced with the folder name. If the file does not start with
the string
"File", the immediate folder name gets added to the start of
the folder
name.
The filelist will include non-Excel files.

I can do the string replacing bit, it's the creating a list of
folders and
their content files, and then getting the immediate folder and
using that to
rename the files that I have no idea about, and Google isn't
helping much :)

Thank in advance.

Darren

Rename Batch of Files in VBA
 

Darren,

That is a word wrap problem. One line of code got split in to
two. The 'OneFile.Path' should be on the same line of code as
the Debug.Print statement.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Darren Hill" wrote in message
...

When I try the code below, with the following line
Set TopFolder = FSO.GetFolder("C:\FolderName")
changed to
Set TopFolder = FSO.GetFolder("C:\Documents and Settings")
I get an error in the called Sub, the line:

Debug.Print OneFile.Name, OneFile.ParentFolder.Path,
OneFile.Path

The msg is "Invalid Use of Property"
and the Onefile.Path property is highlighted.

I've tried a couple of different folder names, so they aren't
the problem.
Any idea?

Darren


"Chip Pearson" wrote in message
...
Darren,

Here's some code to get you started. You'll need to go to
the
Tools menu in VBA, choose References, and set a reference to
the
Windows Scripting Runtime library.

Sub Start()

Dim FSO As Scripting.FileSystemObject
Dim TopFolder As Scripting.Folder

Set FSO = New Scripting.FileSystemObject
Set TopFolder = FSO.GetFolder("C:\FolderName") '<< CHANGE
ProcessOneFolder FSO, TopFolder

End Sub

Sub ProcessOneFolder(FSO As Scripting.FileSystemObject, _
F As Scripting.Folder)

Dim OneFolder As Scripting.Folder
Dim OneFile As Scripting.File

For Each OneFolder In F.SubFolders
ProcessOneFolder FSO, OneFolder
Next OneFolder
For Each OneFile In F.Files
Debug.Print OneFile.Name, OneFile.ParentFolder.Path,
OneFile.Path
Next OneFile

End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Darren Hill" wrote in message
...
I'm using Excel 2000.
I have a big macro to create - I don't expect the group to
do
all my work,
information on how to get started would be good for now.

My goal:
I have a directory ("C:\My Documents\Archive") which
contains
multiple
subdirectories, each of which contains files to be renamed.
Some
subdirectories also have their own subfolders.
Those files whose name starts with the string "File" needs
that
string
replaced with the folder name. If the file does not start
with
the string
"File", the immediate folder name gets added to the start
of
the folder
name.
The filelist will include non-Excel files.

I can do the string replacing bit, it's the creating a list
of
folders and
their content files, and then getting the immediate folder
and
using that to
rename the files that I have no idea about, and Google
isn't
helping much :)

Thank in advance.

Darren

Rename Batch of Files in VBA
 

Thanks, this looks very promising.
I obviously have much to learn about using Google as well as VBA :)
--
Darren
"Harald Staff" wrote in message
...
Hi Darren

Wow. Well, Google would actually help a lot if you broke down the big task
into small definite problems. Ok, first, you rename a file like this:

Sub test()
Name "C:\Temp\MyApp.ini" As "C:\Temp\WifesApp.ini"
End Sub

And here's one among several ways to scan folders and files from a root.
Display the immediate window in the VBE while running it, it shows all
Debug.Print actions. Note also the "*.*" in the file code, change to
"*.xl*"
to limit to Excel files. Or whatever.

Sub test()
Call LookForDirectories("C:\Documents and Settings\All Users")
End Sub
Sub LookForDirectories(ByVal DirToSearch As String)
Dim counter As Integer
Dim i As Integer
Dim Directories() As String
Dim Contents As String

counter = 0
DirToSearch = DirToSearch & "\"
Contents = Dir(DirToSearch, vbDirectory)
Do While Contents < ""
If Contents < "." And Contents < ".." Then
If (GetAttr(DirToSearch & Contents) And _
vbDirectory) = vbDirectory Then
counter% = counter% + 1
ReDim Preserve Directories(counter)
Directories(counter) = DirToSearch & Contents
End If
End If
Contents = Dir
Loop
If counter = 0 Then Exit Sub
For i = 1 To counter
Debug.Print
Debug.Print "*********************"
Debug.Print "Folder " & Directories(i)
Debug.Print "*********************"
GetFilesInDirectory Directories(i)
LookForDirectories Directories(i)
Next i
End Sub

Sub GetFilesInDirectory(ByVal DirToSearch As String)
Dim NextFile As String
On Error Resume Next
With ActiveSheet
NextFile = Dir(DirToSearch & "\" & "*.*")
Do Until NextFile = ""
Debug.Print "Folder " & DirToSearch, NextFile
NextFile = Dir()
Loop
End With
End Sub

--
HTH. Best wishes Harald
Followup to newsgroup only please

"Darren Hill" skrev i melding
...
I'm using Excel 2000.
I have a big macro to create - I don't expect the group to do all my
work,
information on how to get started would be good for now.

My goal:
I have a directory ("C:\My Documents\Archive") which contains multiple
subdirectories, each of which contains files to be renamed. Some
subdirectories also have their own subfolders.
Those files whose name starts with the string "File" needs that string
replaced with the folder name. If the file does not start with the
string
"File", the immediate folder name gets added to the start of the folder
name.
The filelist will include non-Excel files.

I can do the string replacing bit, it's the creating a list of folders
and
their content files, and then getting the immediate folder and using
that
to
rename the files that I have no idea about, and Google isn't helping
much
:)

Thank in advance.

Darren

Rename Batch of Files in VBA
 

Oops, I should have spotted that. Thanks :)

--
Darren
"Chip Pearson" wrote in message
...
Darren,

That is a word wrap problem. One line of code got split in to
two. The 'OneFile.Path' should be on the same line of code as
the Debug.Print statement.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Darren Hill" wrote in message
...

When I try the code below, with the following line
Set TopFolder = FSO.GetFolder("C:\FolderName")
changed to
Set TopFolder = FSO.GetFolder("C:\Documents and Settings")
I get an error in the called Sub, the line:

Debug.Print OneFile.Name, OneFile.ParentFolder.Path,
OneFile.Path

The msg is "Invalid Use of Property"
and the Onefile.Path property is highlighted.

I've tried a couple of different folder names, so they aren't
the problem.
Any idea?

Darren


"Chip Pearson" wrote in message
...
Darren,

Here's some code to get you started. You'll need to go to
the
Tools menu in VBA, choose References, and set a reference to
the
Windows Scripting Runtime library.

Sub Start()

Dim FSO As Scripting.FileSystemObject
Dim TopFolder As Scripting.Folder

Set FSO = New Scripting.FileSystemObject
Set TopFolder = FSO.GetFolder("C:\FolderName") '<< CHANGE
ProcessOneFolder FSO, TopFolder

End Sub

Sub ProcessOneFolder(FSO As Scripting.FileSystemObject, _
F As Scripting.Folder)

Dim OneFolder As Scripting.Folder
Dim OneFile As Scripting.File

For Each OneFolder In F.SubFolders
ProcessOneFolder FSO, OneFolder
Next OneFolder
For Each OneFile In F.Files
Debug.Print OneFile.Name, OneFile.ParentFolder.Path,
OneFile.Path
Next OneFile

End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Darren Hill" wrote in message
...
I'm using Excel 2000.
I have a big macro to create - I don't expect the group to
do
all my work,
information on how to get started would be good for now.

My goal:
I have a directory ("C:\My Documents\Archive") which
contains
multiple
subdirectories, each of which contains files to be renamed.
Some
subdirectories also have their own subfolders.
Those files whose name starts with the string "File" needs
that
string
replaced with the folder name. If the file does not start
with
the string
"File", the immediate folder name gets added to the start
of
the folder
name.
The filelist will include non-Excel files.

I can do the string replacing bit, it's the creating a list
of
folders and
their content files, and then getting the immediate folder
and
using that to
rename the files that I have no idea about, and Google
isn't
helping much :)

Thank in advance.

Darren

Thanks (was Rename Batch of Files in VBA)
 

Thanks Chip and Harald, with your help the task is done. I expected it to
take at least a week :)

--
Darren
"Darren Hill" wrote in message
...
I'm using Excel 2000.
I have a big macro to create - I don't expect the group to do all my work,
information on how to get started would be good for now.

My goal:
I have a directory ("C:\My Documents\Archive") which contains multiple
subdirectories, each of which contains files to be renamed. Some
subdirectories also have their own subfolders.
Those files whose name starts with the string "File" needs that string
replaced with the folder name. If the file does not start with the string
"File", the immediate folder name gets added to the start of the folder
name.
The filelist will include non-Excel files.

I can do the string replacing bit, it's the creating a list of folders and
their content files, and then getting the immediate folder and using that
to
rename the files that I have no idea about, and Google isn't helping much
:)

Thank in advance.

Darren

Thanks (was Rename Batch of Files in VBA)
 

Thanks Darren. Appreciate the feedback.

Best wishes Harald

"Darren Hill" skrev i melding
...
Thanks Chip and Harald, with your help the task is done. I expected it to
take at least a week :)