Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I batch convert 97-2003 .xls files to 2007 .xlsx files | Excel Discussion (Misc queries) | |||
Batch conversion of XP files to 97 files | Excel Discussion (Misc queries) | |||
Can I batch rename new worksheets | New Users to Excel | |||
Batch Files | Excel Programming | |||
Batch files and VBA | Excel Programming |