Solved

Batch renaming of files using Visual Basic

Posted on 2008-06-16
9
492 Views
Last Modified: 2013-11-25
A rather simple query but I haven't used Visual Basic in 10 years.

I am going to write a small app to loop through scanning a directory containing >1000 PDF files, grab each file name, manipulate the filename, then copy/save with the new filename to a new directory.

What is the correct VB syntax to use to traverse a folder from start to finish, and extraxt the filename component.

I know how to save the files and manipulate the files.
Thanks in advance.
0
Comment
Question by:kaysee
9 Comments
 
LVL 39

Expert Comment

by:appari
ID: 21798672
try this

Dim fileName As String

fileName = Dir("C:\test\*.csv")
While fileName <> ""
    Debug.Print fileName
    fileName = Dir()
Wend
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 21798765
Hello kaysee,

1) How are you manipulating the file names?

2) Are you going to execute this using VB, VBA, or VBScript?

Regards,

Patrick
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 21807477
VBScript code such as this can do the job....

Regards,

Rob.
strSourceFolder = "C:\Temp\PDFs"

strTargetFolder = "C:\Temp\NewPDFs"

If Right(strTargetFolder, 1) = "\" Then strTargetFolder = Left(strTargetFolder, Len(strTargetFolder) - 1)

Set objFSO = CreateObject("Scripting.FileSystemObject")

If objFSO.FolderExists(strTargetFolder) = False Then objFSO.CreateFolder(strTargetFolder)

For Each objFile In objFSO.GetFolder(strSourceFolder).Files

	If Right(LCase(objFile.Name), 4) = LCase(".pdf") Then

		strNewName = "NewPDF" & objFile.Name

		objFSO.CopyFile objFile.Path, strTargetFolder & "\" & strNewName, True

	End If

Next

MsgBox "Done"

Open in new window

0
 

Author Comment

by:kaysee
ID: 21819274
Patrick,

1)  Current files use the naming convention of "...\value1_value2_value3.pdf" all in the 1 directory.  I am changing this so that it breaks out the 3 values and rewrites tham into a folder structure based on the values like this: "...\value1\value2\value3.pdf"

2) Originally was going to grab a demo copy of VB to create this one off app but now will just use a VBScript.

Cheers
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 65

Expert Comment

by:RobSampson
ID: 21819581
Hi, given the info you've provided, this will attemp to create the necessary folder structure for the renamed PDF files...

Regards,

Rob.
strSourceFolder = "C:\Temp\"

strTargetFolder = "C:\Temp\NewPDFs"

If Right(strTargetFolder, 1) = "\" Then strTargetFolder = Left(strTargetFolder, Len(strTargetFolder) - 1)

Set objFSO = CreateObject("Scripting.FileSystemObject")

If objFSO.FolderExists(strTargetFolder) = False Then objFSO.CreateFolder(strTargetFolder)

For Each objFile In objFSO.GetFolder(strSourceFolder).Files

	' Only get PDFs with an underscore in their name

	If Right(LCase(objFile.Name), 4) = LCase(".pdf") And InStr(objFile.Name, "_") > 0 Then

		strNewFullPath = strTargetFolder & "\" & Replace(objFile.Name, "_", "\")

		strNewFileName = Mid(objFile.Name, InStrRev(objFile.Name, "_") + 1)

		'MsgBox "Creating " & Left(strNewFullPath, Len(strNewFullPath) - Len(strNewFileName)) & VbCrLf & " to move in " & VbCrLf & strNewFullPath

		CreateLocalDirectoryStructure Left(strNewFullPath, Len(strNewFullPath) - Len(strNewFullPath))

		objFSO.MoveFile objFile.Path, strNewFullPath

	End If

Next

MsgBox "Done"
 

Sub CreateLocalDirectoryStructure(strPath)

	If Right(strPath, 1) = "\" Then strPath = Left(strPath, Len(strPath) - 1)

	arrBits = Split(strPath, "\")

	strSubPath = arrBits(0)

	If UBound(arrBits) > 0 Then

		For intBit = 1 To UBound(arrBits)

			strSubPath = strSubPath & "\" & arrBits(intBit)

			If objFSO.FolderExists(strSubPath) = False Then objFSO.CreateFolder(strSubPath)

		Next

	End If

End Sub

Open in new window

0
 

Author Comment

by:kaysee
ID: 21819844
Hi Rob
Utilizing your code I have tried the code below however it is giving me a "Path Not Found" error when I run it, also here are a few other notes about the process that help explain my code:

1) as I said there are 3 "values" and they are always 8 digits long and deliminated by an "_".  Each value uses 0's to fille out their 8 chars.  In my code I want to strip these leading zeros from the 1st 2 Values, for example
convert "...\00012345_00000123_01234567.pdf"  to "...\12345\123\01234567.pdf"

2) My source directory only contains PDF files in that original format.

I get the error "Path Not Found" on the line:

If objFSO.FolderExists(strTargetFolder) = False Then
                  objFSO.CreateFolder(strTargetFolder)

Thanks

	dim fso

	dim SourceFile

	dim SourceFolder

	dim FileCounter
 

' Declare variables

	dim strSourceFolder as String

	dim strTargetFolder as String

	dim TempClient as String

	dim TempMatter as String

	dim TempFileName as String
 

' Initialise variables

	strSourceFolder = "\\adelaide\Shared\BusinessIntelligence\SKC_Test_Input"

	strTargetFolder  = "\\adelaide\Shared\BusinessIntelligence\SKC_Test_Output"

	FileCounter= 0
 

' Create/open file object I guess for manipulating file structures and load source directory.

	set fso = CreateObject("Scripting.FileSystemObject")

	set SourceFolder= fso.GetFolder(strSourceFolder )

	

' Loop through source folder 

	for each SourceFile in SourceFolder.Files

		TempClient =  Mid (SourceFile.Name, 1,8 )

		TempMatter =  Mid (SourceFile.Name, 10,8 )

		TempFileName =  Mid (SourceFile.Name, 19,8 )

		

		dim ZerosThere = 1

		While ZerosThere > 0 

			if  Mid (TempClient, 1,1 ) = "0"

				TempClient = Mid(TempClient,2)

			else

				ZerosThere = 0

		Wend

		dim ZerosThere = 1

		While ZerosThere > 0 

			if  Mid TempMatter 1,1 ) = "0"

				TempMatter = Mid(TempMatter ,2)

			else

				ZerosThere = 0

		Wend

		

		strTargetFolder = strTargetFolder & "\" & TempClient & "\" & TempMatter

		

		If objFSO.FolderExists(strTargetFolder) = False Then 

			objFSO.CreateFolder(strTargetFolder)
 

		fso.CopyFile SourceFile.Path, (strTargetFolder "\" & TempFileName & ".pdf"), True

		FileCounter = FileCounter + 1

	next

Open in new window

0
 
LVL 65

Accepted Solution

by:
RobSampson earned 125 total points
ID: 21819873
Hi, the FileSystemObject is not capable or creating multiple directories at once. Try using my CreateLocalDirectoryStructure routine to step through creating each folder.

Regards,

Rob.
	dim fso

	dim SourceFile

	dim SourceFolder

	dim FileCounter

 

' Declare variables

	dim strSourceFolder as String

	dim strTargetFolder as String

	dim TempClient as String

	dim TempMatter as String

	dim TempFileName as String

 

' Initialise variables

	strSourceFolder = "\\adelaide\Shared\BusinessIntelligence\SKC_Test_Input"

	strTargetFolder  = "\\adelaide\Shared\BusinessIntelligence\SKC_Test_Output"

	FileCounter= 0

 

' Create/open file object I guess for manipulating file structures and load source directory.

	set fso = CreateObject("Scripting.FileSystemObject")

	set SourceFolder= fso.GetFolder(strSourceFolder )

	

' Loop through source folder 

	for each SourceFile in SourceFolder.Files

		TempClient =  Mid (SourceFile.Name, 1,8 )

		TempMatter =  Mid (SourceFile.Name, 10,8 )

		TempFileName =  Mid (SourceFile.Name, 19,8 )

		

		dim ZerosThere = 1

		While ZerosThere > 0 

			if  Mid (TempClient, 1,1 ) = "0"

				TempClient = Mid(TempClient,2)

			else

				ZerosThere = 0

		Wend

		dim ZerosThere = 1

		While ZerosThere > 0 

			if  Mid TempMatter 1,1 ) = "0"

				TempMatter = Mid(TempMatter ,2)

			else

				ZerosThere = 0

		Wend

		

		strTargetFolder = strTargetFolder & "\" & TempClient & "\" & TempMatter

		

		If objFSO.FolderExists(strTargetFolder) = False Then CreateLocalDirectoryStructure strTargetFolder

 

		fso.CopyFile SourceFile.Path, (strTargetFolder "\" & TempFileName & ".pdf"), True

		FileCounter = FileCounter + 1

	Next
 
 

Sub CreateLocalDirectoryStructure(strPath)

	If Right(strPath, 1) = "\" Then strPath = Left(strPath, Len(strPath) - 1)

	arrBits = Split(strPath, "\")

	strSubPath = arrBits(0)

	If UBound(arrBits) > 0 Then

		For intBit = 1 To UBound(arrBits)

			strSubPath = strSubPath & "\" & arrBits(intBit)

			If objFSO.FolderExists(strSubPath) = False Then objFSO.CreateFolder(strSubPath)

		Next

	End If

End Sub

Open in new window

0
 

Author Closing Comment

by:kaysee
ID: 31470435
Sorry for delay in allocating the points.  Understood most of it just didn't know what the "arrbits" parts were doing in "CreateLocalDirectoryStructure"... not too worry it all worked for this one off process, so it was all good.  Thanks for you help.
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 21862218
Thanks for the grade.

The arrBits part basically takes each folder name from a path and creates them in turn.

So if you had
C:\Temp\Temp\Test

arrBits is an array returned by the Split function, splitting by the backslash.

So the arrBits array has four elements now, being
C:
Temp
Temp
Test

then it just rebuilds the path, creating each folder as it goes along....

Regards,

Rob.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now