dougv.com | The Web home of Doug Vanderweide

Recently asked on Yahoo! Answers:

Help with VB script?

I am new to all this, so please forgive me for talking like a newbie.

I am trying to get into the wonderful world of vbscript to try to help me speed up my listings on Ebay.
[details snipped]

Now as you would imagine all the above takes me several hours to do, and I just thought if I could get my head around a little bit of vbscripting I would save myself one hell of a lot of time. If any kind person out there could point me in the right direction it would be much appreciated.

To summarize what this questioner wants done:

• He takes about 300 images per night with his digital camera; it somehow orders the photos.
• He wants to create however many directories are needed to put five photos each into each new directory; each directory should be sequentially numbered based on the last directory he created.
• He wants to create copies of an HTML template, name it the same as each of the new directories, and change some text in the template to be the same as each folders’ number.

Well, that’s fairly easy to do with VBScript, as he suggests — and it takes VBScript all of about two seconds to do the work.

Since it’s been a cow’s age since I’ve used VBScript to automate something like this, I knew I had to put this script together. It demonstrates some really cool things you can do with shell scripting:

• Create directories on-the-fly;
• Move files from one directory to another;
• Rename files to something else, based on patterns or numeric values;
• Edit text-based files.

As always, I’ll have the code and sample files available as a download at the end of this post.

Assumptions

For this script to function properly, I had to make some basic assumptions:

• All the images from the camera are taken, and named, sequentially; e.g., the first five pictures are for item 1, the next five photos are for item 2, etc.
• All the images are properly sized and have a .jpg extension.
• The To Sort directory contains at least one image.
• The Sorted directory contains at least one numbered directory.

With those assumptions out of the way, we can now tackle the individual problems of the question.

Constants And Globals

We need to declare some constants and some global variables. Here they are:

Dim folderToSort 'raw camera images folder
folderToSort = "C:\\TEST\ToSort\"			
Dim folderSorted 'where sorted images folders go
folderSorted = "C:\\TEST\Sorted\"			
Dim folderBaseName 'base names for sorted folder groups
folderBaseName = "000000"				    
Dim strHTMLExtension 'file extension for HTML files
strHTMLExtension = ".html"				    
Dim intImagesPerFolder 'number of images for each sorted subfolder, cannot be 0
intImagesPerFolder = 5				        
Dim strHTMLTemplate 'the HTML template file
strHTMLTemplate = "C:\\TEST\template.html"	
Dim strReplace 'string to replace with folder name in HTML files
strReplace = "{REPLACE}"

I also want to declare some global variables I’ll be using. While they’re not constants, they are important.

Dim I, X, Z 'generic looping variables
I = 0   					                        
X = 0                                               
Dim objFSO, objFolder, objFile 'declare FSO variable names
Dim intFolderBaseNameLength 'length of folder base name
intFolderBaseNameLength = Len(folderBaseName)	    
Dim strTemp 'generic temp variable
strTemp = ""

Put The File Names Into An Array Variable

One of the problems with the way Windows stores file names in a directory is that it’s not always done alphabetically. That’s the way they usually are stored in a directory, but not always. As such, we need to be able to sort the names of all the files we’re going to use, to ensure that we wind up with our subdirectories properly ordered.

So, we’re going to populate an array with all the names of the image files in the directory, and then we’ll sort that array.

Unfortunately, VBScript doesn’t have native support for either dynamic arrays or sorting, so we need to do both ourselves.

In VBScript, instead of the language automatically resizing an array as you add (push) items to it, you need to ReDim the array and use the Preserve keyword — which basically says, “make the array this new size, but copy all the current cell values into the resized array.”

And the way you sort a one-dimensional array in VBScript is with a “bubble sort,” which is a staple of beginner computer programming classes.

“Bubble sorting” basically looks through the array twice. It starts by going backward through the array. Then, the array is reiterated going forward, comparing the backward-based item to all the forward-based items leading up to it. If those items are alphabetically after the current item, then we move the items in the array around to properly position the current item.

So, first we create an instance of the FileSystemObject, which will let us work with our computer’s files and folders, and use it to open the folder where all our camera images are located.

Then, we declare the array, and iterate through all the files in the To Sort folder, adding to the array all the files that have extensions ending in “.jpg”.

Then, we perform our bubble sort, and close both the folder and the FileSystemObject.

(Unfortunately, the FileSystemObject cannot be recycled. If you close one object you created from it — such as I do by closing the folder object — you need to close the parent FileSystemObject, too.)

'open folder
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(folderToSort)
 
'create array of file names & populate it
Dim arrFiles()
For Each objFile In objFolder.Files
    If InStr(LCase(objFile.Name), ".jpg") > 0 Then
        ReDim Preserve arrFiles(X)
	    arrFiles(X) = objFile.Name
	    X = X + 1
	End If
Next
 
'bubble sort the array
For I = UBound(arrFiles) - 1 To 0 Step -1
    For Z = 0 to I
        If arrFiles(Z) > arrFiles(Z + 1) Then
            strTemp = arrFiles(Z + 1)
            arrFiles(Z + 1) = arrFiles(Z)
            arrFiles(Z) = strTemp
        End If
    Next
Next 
 
'remove folder object
Set objFolder = Nothing
Set objFSO = Nothing

Create The Image Subfolders

We now need to create the subfolders into which we will place our product images; and to do that, we need to know how many folders we need to create, as well as where to begin numbering.

First, to figure how many folders we need, we merely divide the number of images we have by the total number of images per product. We round the result off by casting it as an integer. And, if necessary, we add one more folder, in case we’re short an image for the last product.

'get count of total subfolders to create
Dim intTmp 
intTmp = X / intImagesPerFolder
intTotalFolders = CInt(intTmp)
 
'may need to add one folder due to limitations of CInt rounding
If (intImagesPerFolder * intTotalFolders) < X Then
	intTotalFolders = intTotalFolders + 1
End If

To get the number for the first folder, we have a function named GetHighestSortedSubfolderName() It takes as its argument the name of the Sorted folder; it looks there for the highest-numbered folder currently in the directory (so that means you have to have at least one numbered directory in there) and returns that to a variable.

First, the function:

Function GetHighestSortedSubfolderName(folderSorted)
	'Returns highest ID of subfolder name
	'Assumes sorted folders remain in folderSorted
	Dim objFSO, objRoot, objFolder
 
	'Declare output variable
	Dim strFolder 
	strFolder = ""
 
	'Open parent folder
	Set objFSO = CreateObject("Scripting.FileSystemObject")
	Set objRoot = objFSO.GetFolder(folderSorted)
 
	For Each objFolder in objRoot.SubFolders
		If objFolder.Name > strFolder Then
			strFolder = objFolder.Name
		End If
	Next
 
	'clean up objects
	Set objFolders = Nothing
	Set objFSO = Nothing
 
	'return output
	GetHighestSortedSubfolderName = strFolder
End Function

Next, the variable we set with that function:

'get integer for first folder name
Dim intFirstFolder 
intFirstFolder = GetHighestSortedSubfolderName(folderSorted)
intFirstFolder = CInt(intFirstFolder)
intFirstFolder = intFirstFolder + 1

And now, we can call a function to actually create all the subdirectories we want. We’ll call that function CreateSubFolders() and it will take, as arugments, the name of the Sorted folder; the “base name” variable for your files (which should always be a string of zeros, whatever length you want), the first folder number that we just computed; the total number of folders needed, which we also computed; and the length of (number of zeros in) the “base name” for each folder.

Basically, all the function does is open the Sorted folder, then start adding folders to it, naming each folder incrementally from the first folder we need to create to the last folder we need to create.

Function CreateSubFolders(folderSorted, strFolderBaseName, intFirstFolder, intTotalFolders, intFolderBaseNameLength)
	'Creates the subfolders
	Dim I 
	I = intFirstFolder
	Dim X 
	X = I + intTotalFolders
	Dim strFolderName
	strFolderName = ""
 
	Set objFSO = CreateObject("Scripting.FileSystemObject")
 
	Do While I < X
		strFolderName = CStr(strFolderBaseName) 
		strFolderName = strFolderName & CStr(I)
		strFolderName = Right(strFolderName, intFolderBaseNameLength)
		objFSO.CreateFolder(folderSorted & strFolderName)
		I = I + 1
	Loop
 
	'Clean up objects
	Set objFSO = Nothing
	CreateSubFolders = True
End Function

Move The Files From The To Sort Directory To The Subfolders

We can now move the files from the To Sort directory to each of the subfolders we just made. That’s simple: We start by declaring the file path to the first folder we made.

Then, we create a FileSystemObject and use it to open the To Sort directory. Then, we iterate though the array of file names we made earlier.

If we reach the number of images we’ve set per item, we change the current subdirectory to be the next one in the Sorted directory. Otherwise, we use the FileSystemObject to move the selected file from the To Sort directory to the current subdirectory. Then, we clean up the FileSystemObject.

'Move images to new subfolders
Dim intCurrentFolder 
intCurrentFolder = intFirstFolder - 1
Dim strCurrentFolder
 
Set objFSO = CreateObject("Scripting.FileSystemObject")
 
For I = 0 to UBound(arrFiles)
	'change subdirectory every however many files per subdirectory
	If I Mod (intFolderBaseNameLength - 1) = 0 Then
		intCurrentFolder = intCurrentFolder + 1
		strCurrentFolder = folderSorted & Right(CStr(folderBaseName) & CStr(intCurrentFolder), intFolderBaseNameLength) & "\"
	End If
 
	'move files
	strTemp = folderToSort & arrFiles(I)
	Set objFile = objFSO.GetFile(strTemp)
	strTemp = strCurrentFolder & arrFiles(I)
	objFile.Move(strTemp)
Next
 
'clean up FSO object
Set objFSO = Nothing

Rename The Image Files

With the files moved to the subdirectories, we can rename the files by simply iterating through each subdirectory, and naming the files from 1 to however many images are in there. (Note that this doesn’t necessarily rename the images in “alphabetical” order; again, Windows tends to sort files by name in its catalog, but not always, so the images might be out of order via this naming method. I’m cutting a corner here because I don’t want to have to keep reloading an array with file names and bubble sorting it.)

The function that does the renaming is RenameFilesInSubfolders(), and its sole argument is the path of the Sorted directory.

Function RenameFilesInSubfolders(folderSorted)
 
	'Renames files
	'Assumes all files are JPGs
	'outputs lower-case .jpg extension to all files in each subfolder
	Dim I, objFSO, objRoot, objFolder, objFile
 
	Set objFSO = CreateObject("Scripting.FileSystemObject")
	Set objRoot = objFSO.GetFolder(folderSorted)
 
	For Each objFolder in objRoot.SubFolders
		I = 1
		For Each objFile In objFolder.Files
		    If InStr(LCase(objFile.Name), ".jpg") > 0 Then
			    objFile.Name = CStr(I) & ".jpg"
			    I = I + 1
			End If
		Next
	Next
 
	Set objRoot = Nothing
	Set objFSOs = Nothing
	RenameFilesInSubfolders = True
End Function

Make And Edit The HTML Pages

Our last step is to create HTML pages for each subdirectory from the template, and edit the text of the HTML to replace certain text with the number of the subdirectory corresponding to that HTML file.

To do that, we’ll open the HTML template via the FileSystemObject and read its contents into a string variable. We’ll also open the Sorted directory and get all the subdirectories within it.

Provided the subdirectory is one we just made, we’ll create a new HTML file from the string variable that holds the template’s text, and replace the parts we want replaced — as we declared in the constant — with the name of the subdirectory.

We do that with a function named MakeHTMLFiles(), which takes as arguments that path to the HTML template, the part to the Sorted folder, the file extension we want to use for the HTML files, the number of the first folder we created, and the string we want to replace in the template (which, again, we declared in our constants).

Function MakeHTMLFiles(strHTMLTemplate, folderSorted, strHTMLExtension, intFirstFolder, strReplace)
	Dim objFSO, objRoot, objFolder, objFile, strFileName, strTemp, strOut, objOut
 
	Set objFSO = CreateObject("Scripting.FileSystemObject")
	Set objRoot = objFSO.GetFolder(folderSorted)
	Set objFile = objFSO.OpenTextFile(strHTMLTemplate, 1)
 
	strTemp  = objFile.ReadAll
 
	For Each objFolder In objRoot.SubFolders
	    If CInt(objFolder.Name) >= intFirstFolder Then
		    strFileName = objFolder.Name & strHTMLExtension
		    strOut = Replace(strTemp, strReplace, objFolder.Name)
		    Set objOut = objFSO.CreateTextFile(folderSorted & strFileName, True)
		    objOut.Write(strOut)
		    objOut.Close
		    Set objOut = Nothing
		End If
	Next
 
	Set objFile = Nothing
	Set objRoot = Nothing
	Set objFSO = Nothing
	MakeHTMLFiles = True
End Function

And to wrap the whole thing up neatly, we have the script give us a Message Box to report that it’s done with the job.

MsgBox("Job Complete")

That’s it. I’ve tested this solution on my Windows XP Professional SP2 machine, which has all the latest patches, and it runs fine. You should only need to change the constants to have it work on any Windows-based PC.

I’ve posted samples of the HTML template and the HTML pages this script produces here:

http://www.dougv.com/demo/vbscript_ebay_automation/

The script itself is available here:

http://www.dougv.com/demo/vbscript_ebay_automation/sort.txt

Just save the file to your computer, change the constants to meet your needs and you should be good to go. I distribute code under the GNU GPL version 3.