Windows Explorer Has Stopped Working: FreeCommander and Other Alternatives to Windows Explorer

I was using Windows 7. I installed a new EVGA video card. Immediately, I began getting "Windows Explorer has stopped working" error messages, especially when I would right-click on a file or folder and try to move it somewhere else. A search led to a Microsoft webpage that identified the video driver as the first culprit. I had also had problems with the previous video card (also an EVGA), but had partially resolved those by rolling back to an earlier driver. That solution did not work this time. While awaiting EVGA's reply to a service request, I decided to look into Windows Explorer replacements.

This was not the first time I'd had problems with Windows Explorer. As far back as the late 1990s, I had found PowerDesk 98 to be a superior alternative to Windows Explorer in Windows 95 and 98. I had grown so attached to it that I was still occasionally looking, years later, to see whether they had come out with an update. It just offered a lot of features and advantages that I hadn't found in Windows Explorer.

To me and to at least some other users, Windows Explorer in Windows 7 had seemed like a step backwards from Windows Explorer under Windows XP. Some functionality was lost; some new problems appeared. For example, not long before this latest "stopped working" issue, I started getting the unwanted reminder that "This folder is shared with other people," which would come up when I tried to move or delete a folder. Despite some effort, I hadn't been able to get rid of that.

FreeCommander

In the past year or so, after repeated albeit superficial looks at various Windows Explorer alternatives, I had started using FreeCommander for some tasks. One thing pushing me in that direction was that, in Win7, Windows Explorer seemed eager to forget file selections. That is, I could go through a list of files and select some; but then Windows Explorer would de-select them if I (or anything else) changed the file list. In FreeCommander, unlike Windows Explorer, I could select files, and they would tend to stay selected -- even if I deleted one entry from the list (from within another program, for example, or another Windows Explorer session) or if I re-sorted the list by file size rather than file name. FreeCommander wasn't perfect about this. At this writing, a quick test revealed that it, too, would lose selections if I sorted by file type. Nonetheless, FreeCommander was much better than the current version of Windows Explorer in this regard, in my ordinary usage. FreeCommander was also not crashing, at present, during these instances when I was getting these "Windows Explorer has stopped working" messages.

FreeCommander also did a better job of putting me where I wanted to be. It would remember my last location, and would never put me in a pseudo-folder under Ray (my user name in the navigation pane), as Windows Explorer insisted on doing, when I actually wanted to be in Computer > Drive D > Folder X. I also liked that FreeCommander did not lard down my navigation pane with all sorts of Libraries and other top-level folders (which, to some degree, I had been able to eliminate from Windows Explorer by using registry tweaks).

On the downside, unlike Windows Explorer, FreeCommander did not preserve a memory of multiple sessions after a reboot. That is, if I had three Windows Explorer sessions and three FreeCommander sessions open before a crash, all focused on different folders, I would wind up with those same three Windows Explorer sessions after the crash, but only one FreeCommander session (or pair of sessions, if I was using FreeCommander's split-window feature). (I think I had to enable an option somewhere in Windows, or in a tweaking program, to get this functionality from Windows Explorer.) Then again, FreeCommander's option to open or close tabs (via View > New Folder Tab or else Ctrl-T and Ctrl-W) had the potential to make it unnecessary to have so many Windows Explorer sessions open at once.

At first, I did not realize that I could turn FreeCommander's split-window feature off. There were times when it was convenient to be able to view two separate lists of files -- from, say, two different drives -- within one FreeCommander session (and to split the screen either horizontally or vertically). The options to split the window or to view just the left or right sides were available through the menu (View > Split Window) or by shortcuts (Ctrl-Shift F1, F2, and F3). If I had multiple tabs open on one side, it would remember them, even if it was currently displaying only the other side.

I didn't like that FreeCommander wouldn't show me a full navigation tree (in the left-hand pane) for all drives: instead, it was focused on just one drive. To switch drives, I had to go to the top of the screen and select another drive from a list or combo box (configurable via Extras > Settings > View > Drives). That seemed inconvenient, and that top bar took up screen space. One workaround might be to have a different tab open for each drive, and just navigate within that tab whenever I wanted to work in a different drive.

I had some batch files that would open various Windows Explorer sessions automatically, on certain dates or at certain times of day. There was a simple command format to specify which folder a Windows Explorer session should display: start explorer.exe /e,"D:\Folder Name\File Name.doc." I wondered if I could do something similar with FreeCommander. It would let me specify a pair of starting folders (via Extras > Settings > Start Program), but could I go further than that? It appeared that, to match what I was doing with Windows Explorer in this regard, I would have to set up a relatively complicated arrangement with alternative .ini files in FreeCommander. (I wasn't entirely sure whether this was what the program's author meant when he said that "several layouts can be saved.") I probably wouldn't go to that trouble for the most part, though I could imagine setting up a couple of really complicated sets of tabs and linking to them with shortcuts on my semi-portable customized Start Menu.

One feature of FreeCommander that I liked immediately was its more condensed display. Windows Explorer had seemed to add more space between lines, going from Windows XP to Windows 7. I wasn't sure why. The result was that Windows Explorer would display 38 items at a time (or a few more, if the top menu and bottom status bar were turned off) while FreeCommander would display 45 -- and those 45 would be presented in a larger, more readable font. FreeCommander's status bar was also more informative, telling me that I had selected 45 out of 110 items within a folder, and such information would remain visible in the status bar, whereas for some reason Windows Explorer would sometimes replace it with the date when a folder was created.

One thing I didn't like about the FreeCommander interface was that it didn't have an address bar into which I could paste an address for a quick switch to a different directory. In FreeCommander, I had to use the Edit menu to get a folder's address for pasting elsewhere, and the Folder > Go to Folder menu pick to paste an address from elsewhere into FreeCommander. These steps were inconvenient because (a) they required more steps and (b) they were in two different locations, which made things a little more confusing. On the other hand, FreeCommander offered the option (via Alt-Ins or Edit > Copy Full Name as Text) of copying both the path and the file name in one step, which Windows Explorer did not do.

I felt that FreeCommander needed some reorganization and clarification. There was apparently a new XE version in the works; it appeared that the present version dated from 2009. On the other hand, I wasn't sure how much I could complain about the program's offerings. Quite aside from the fact that it was free, FreeCommander had all kinds of features not available in Windows Explorer, and there didn't seem to be much that WinEx could do that FreeCommander couldn't. I figured that, with or without reorganization, I would become familiar with FreeCommander's menus and possibilities if I began using it more extensively.

One particular area that I did not explore, within FreeCommander, was its offering of various built-in utilities. These included file viewing (e.g., hex, image, binary formats), zipping, searching, wiping, checksums, listing, renaming, and splitting; multiple file renaming; directory comparison and synchronization; and the option to add more tools to the list. I didn't look into this because I was not sure how much I would use these tools. As indicated in other posts in this blog, a lot of problems and questions could arise when you really got into the details of these sorts of functions. For example, I had paid some attention, in recent years, to GoodSync and alternatives for synchronization between computers, and to Beyond Compare and rsync for comparing a computer's files against backups. I was hesitant to replace those sorts of dedicated tools with what might be a simplistic form of file comparison that could make a mess.

In terms of other conveniences, there was quite a list of hotkeys. FreeCommander was available in both installed and portable versions. There were user forums with a total of maybe 10,000 posts. It was definitely a stable and worthy program. Given my previous searches and my experiences to date with FreeCommander, the question for me at this point was whether there was some other Windows Explorer alternative that was even better.

Other Windows Explorer Alternatives

Along with various ways to customize Windows Explorer (which did not presently appeal to me, given the fact that it was crashing every time I looked at it sideways), there were various lists of Windows Explorer alternatives. For example, a PCTIPS 3000 webpage briefly listed FreeCommander as one of the best (free) Windows Explorer replacements, along with Q-Dir, Explorer XP, NexusFile, and CubicExplorer.

A potentially manipulable poll suggested that users of one forum were using Total Commander, Directory Opus, XYplorer, Explorer++, Q-Dir, and FreeCommander, in approximately that (descending) order. A SuperUser thread ranked the leading Windows Explorer alternatives as being (in this order) Total Commander, QTTabBar, FreeCommander, FarManager, XYplorer, Directory Opus, the command line, xyplorer2 (?), Q-Dir, TeraCopy (!), CubicExplorer, SpeedCommander, Altap Salamander, Xplorer2, muCommander, and others recommended by at least one person. Another webpage listed at least 30 free and paid alternatives to Windows Explorer. TechRepublic listed these as its preferred free Windows Explorer replacements: CubicExplorer, Explorer++, Xplorer2, NexusFile, and Q-Dir.

Wikipedia provided a comparison of many such programs. It appeared that they could first be divided into free and nonfree categories. Since I was not likely to pay for something that FreeCommander (not to mention Windows Explorer) could already do for free, I focused on the free ones. This removed Total Commander ($40) from consideration -- a perennial favorite that I had tried briefly but hadn't found that terribly impressive. The nonfree group also included some other popular entries from the lists cited above: Directory Opus ($85!), XYplorer ($43), SpeedCommander (~$55), Xplorer2 ($30), and Salamander ($30) -- assuming the lite versions available for some of these programs would fail to match a competitive freeware alternative like FreeCommander. It was not clear how recently the Wikipedia page had been updated -- it didn't include any reference to some of the programs listed above, and therefore might not reflect pricing changes (including decisions to offer a given program for free, or to stop doing so). Then again, I guessed that any developer who was on top of the game would know of this Wikipedia page and would be updating the pricing information pretty quickly.

ExplorerXP did not appear to have been updated for Windows 7. I had encountered other programs, great in Windows XP, that had stumbled at one point or another in Win7. Given the number of competing programs, I tentatively ruled out those that were not clearly Windows 7 compatible. I was also inclined to exclude those whose screenshots provided a clunky interface and/or limited information -- notably, Far Manager.

The leading free Windows Explorer alternatives, other than FreeCommander, thus appeared to include Q-Dir, NexusFile, CubicExplorer, Explorer++, QTTabBar, and muCommander. The Wikipedia comparison page provided a table indicating the kinds of views that each such program offered. I considered a Details view option essential. Evidently no such view was available in CubicExplorer. I was not sure if a twin-panel or tab options were essential, but CubicExplorer seemed to lack those features too. As noted above, I was not especially interested in the utilities (e.g., file compression) that such programs might offer, so I did not examine the Wikipedia tables on those matters.

I looked at the webpages for the ones that remained on my list: Q-Dir, NexusFile, Explorer++, QTTabBar, and muCommander. I had previously glanced at QTTabBar but, for reasons I did not recall, had not gone far with it. On this review, I was not too impressed. Its forums seemed to be lonely places; there was an indication that it was still in "public beta"; and there were remarks of instability and Windows 7 incompatibility.

Q-Dir's screenshots seemed to offer the potential to divide up the screen in a number of interesting ways. They did not seem to display a tabbed browsing option, but I gathered that tabbing was an option somehow. There did not appear to be a forum, but the author did provide an array of FAQs, with perhaps some English-language limitations. My principal reaction at this point was that, having glanced ahead at Explorer++, I thought it might be a little more of what I was looking for.

NexusFile had a snazzy webpage, especially for people who like black, but I wasn't so sure about its content. Forum posts didn't seem to be categorized, and there didn't seem to be a way of searching them. Generally, as with most of the other programs noted here, the information provided on the webpages did not seem responsive to the detailed concerns noted in my review of FreeCommander (above). That might have been alleviated in some cases if I had plunged into their FAQs in detail. It would not have been alleviated in the case of NexusFile, whose FAQs page had a total of six questions.

Between muCommander and Explorer++, a comparison of forum posts suggested that muCommander was more of a cross-platform option, with many of its posts coming from Linux users. There also seemed to be somewhat more recent activity in the Explorer++ forums. MuCommander, not listed on the Wikipedia comparison page (above) at this point, apparently did not yet have tab support. Between the two, then, I was inclined toward Explorer++.

The thing is, I wasn't seeing anything in any of these alternatives that FreeCommander lacked. At present, the best course of action seemed to be to focus on FreeCommander, to the extent that it performed more stably and functionally than Windows Explorer. I decided that I would return to these alternatives if, at some point, neither Windows Explorer nor FreeCommander seemed to be working well for me.

Suggestion

It seemed, in particular, that while these various programs were trying to compete to be the best Swiss Army knife, doing everything fairly well, there might actually be some merit in taking more of a niche approach. For example, I tended to use a few folders very frequently. I had not really worked out the whole thing of using Favorites, links, Send To, Move To, Copy To, and other ways of moving and sorting my attention, or my files, among these key folders. It would have been very helpful if, for instance, I could have just punched a function key or other hotkey to move a file to any of a number of previously designated folders. I often found myself sorting files; this would have been very useful.

In that particular example -- and that was obviously not the only specialized way in which people might use Windows Explorer -- there seemed to be a lot of room for people to develop tools that would help me navigate across my computer adequately, while providing a really special experience among key locations. In that case, I could easily imagine running this other program along with (not "instead of") Windows Explorer or FreeCommander.

Batch Verifying or Validating Scattered WAV Files

I had previously looked for ways to test MP3 files.  Now I wanted to test some WAVs.  This post describes one approach that seemed to work.

Initially, I tried using IrfanView, before rediscovering that IrfanView did not do audio file conversions.  After some searching around, I tried the command-line option in Boxoft WAV to MP3 Converter (freeware).  This looked promising.

To run the Boxoft converter, I used this syntax:

WavToMp3Cmd "D:\Folder\File Name.wav" -O"D:\Test\Output Filename.mp3"

This syntax seemed a little awkward, because (as shown) there was no space after the -O. (That's an oh, not a zero.)  Of course, I had to use quotation marks, as shown, because I had spaces in the folder and/or file names.  I had to run this command in the folder where the Boxoft program was installed (i.e., where WavToMp3Cmd.exe was located). That is, I had to run my batch file from there. That installation folder, on my x64 system, was C:\Program Files (x86)\Boxoft Wav to MP3 (freeware).

The foregoing command was pretty bare-bones.  I would have had to add more parameters, as described in the Boxoft help file, if I had wanted to produce high-quality output MP3s.  I was just creating the output MP3s to see whether the input WAVs were valid.  I would be deleting the output MP3s when the test was done.

I wasn't sure how the Boxoft converter would deal with a flawed WAV file.  To find out, I created a TXT file, changed its extension to WAV, and ran the foregoing command on it.  Boxoft produced an unplayable Test.mp3 in the output folder, so in that sense the test failed:  I would have preferred that it would not produce junk files.  Fortunately, Boxoft did register an error message on the command line. I captured that error to a log file by adding some stuff to the foregoing command.  That command now looked like this:

WavToMp3Cmd "D:\Folder\File Name.wav" -O"D:\Test\Output Filename.mp3" >> D:\Test\Log.txt

That was all on one command line.  Now I would have a Log.txt file that would capture error messages produced by the converter.  The next step was to work up my list of commands.  My WAV files were scattered in various folders around the computer, so as described in more detail in another post (and in several prior posts listed at the start of that one), I used a DIR command to identify the files I wanted to test (in this case, they were of the form 2010*.wav), and then I used Microsoft Excel formulas to create the relevant commands.  So now, for each file to be tested, I had an Excel row containing something like the command shown above.  I copied all those commands into a text file in Notepad, renamed that file's extension to be BAT (so that it would run), saved it in the Boxoft folder as noted above, and ran it.

In 10 or 15 minutes, the batch file was done; I had tested about 2,800 WAVs.  I verified that the number of output MP3 files equaled the number of input WAV files.  I wasn't sure how Log.txt would register errors, so I copied and pasted the entire log into a Microsoft Word document and reduced its size by doing global search-and-replace operations to remove the lines pertaining to the Title, Artist, and other settings that didn't matter to me.  (Word's special character to remove line breaks was ^p.)  I changed the remaining output to fit on one line (that is, I changed "^pSave to" to be just "Save to"), so that the file name and the result (e.g., "Success") would all be on one line.  I pasted this remaining text into Excel, did a FIND for lines containing "Success," and sorted the list according to whether "Success" appeared.  There was only one non-Success line.  It said, "Set the error bit rate. Please run WavToMp3Cmd to get help."  In the Word doc, I searched for that text.  I played the original WAV file whose name appeared just before that text.  It played OK.  I looked for the output MP3 file of similar name.  It wasn't there.  In this case, Boxoft hadn't created the failing file.  But then why did my file count agree?  Oh, of course, because the Log.txt file was in the output folder.  I wasn't sure why Boxoft had balked at the input WAV; but now that I had manually tested it and found it was OK, I didn't really care.

It occurred to me that Boxoft had produced an error when confronted with the obvious impossibility of converting a TXT file to MP3; but what about if some of the input files were mislabeled as WAVs when, in fact, they were MP3s or WMAs or some other kind of audio file?  I wasn't sure whether Boxoft would produce an error message or warning for such files.  I ran a version of the foregoing command on an input WMA, not renamed to WAV.  That crashed Boxoft.  I tried again after renaming the WMA to WAV.  That crashed Boxoft too.  I also tried with an input MP3.  Same results:  crashes.  I re-checked that the command did work with a genuine input WAV.  So apparently Boxoft would have crashed if my list of WAVs to be tested had contained an MP3 or WMA that was accidentally misnamed as a WAV.

To sum up, it seemed that the Boxoft command line converter, using a command with syntax shown above, was able to test large numbers of WAV files by converting them to MP3s, with error messages captured in a log file.

Batch Converting Multiple Word DOC Files to PDF in Scattered Folders

I had a large number of .doc files produced by Microsoft Word.  These files were in assorted folders.  I wanted to convert some or all of these files to PDF format.  This post describes the steps I took.

I had already tackled similar problems in several other posts, including these:

• Batch Merging Many Scattered JPGs into Many Multipage PDFs - Clarified
• Batch Converting Many Microsoft Word (.doc) or WordPerfect (.wpd) Files to PDF - Streamlined
• Batch Converting Many Text Files to PDF

This post does not detail all of the steps described in those other posts.  If a step described here is not clear, perhaps one of those posts expresses it more lucidly.

I started by getting a list of the DOC files to be converted.  For this, I opened a command window and typed "DIR /s /b /a-d > doclist.txt."  It was OK if this DOC list included files that I did not want to convert:  I could go through the list manually at this point, deleting those that I did not want to convert, or I could do that in the next step.  The next step was to copy and paste the list of files from doclist.txt into Microsoft Excel or some other spreadsheet.  This gave me a list of file and path names that looked like this:

D:\Folder3\Subfolder 8\Filename Z.doc

Since some paths and/or filenames contained spaces, I would tend to use quotation marks in commands relating to them, in both Excel and the command window.  In Excel, I used the REVERSE function and other spreadsheet commands to extract the path (e.g., "D:\Folder3\Subfolder 8\") from the filename (e.g., "Filename Z.doc").  So now I had separate columns showing the paths and the filenames for each entry in doclist.txt.  This would be a good point for using formulas to identify groups of DOC files that I did not presently wish to convert to PDF.

The next step in the spreadsheet was to identify the filename without the extension, and to add PDF instead of DOC to that rump filename.  In other words, in this step I went from having Filename Z.doc to having Filename Z.pdf.  This gave me the essential ingredients for the batch commands that I would assemble on each line of the spreadsheet and would then paste into Notepad and save as a .bat file, so as to automate the conversion.

There were two ways to proceed at this point.  One was to leave the DOCs in place, in their home folders, and do the conversion and replacement right there.  I didn't like that approach.  It was too hard to be sure of what had happened in all those scattered folders.  The approach I preferred was to bring all those .DOC files together in one central folder, do the conversion, and then use the spreadsheet to construct batch files that would put those PDFs back where they belonged and, optionally, delete the DOCs from which they had come.

Bringing the DOC files to a central folder could be done very easily with a search program like Everything, searching for *.doc.  It could also be done with batch commands constructed in the spreadsheet.  An Excel formula producing a command of the latter nature would be something like ="move /-y "&char(34)&[cell containing filename including .doc extension]&char(34)&" D:\CentralFolder").  It would be important not to take this step -- that is, not to move the files away from their home folders to the central folder -- until I already had a list of where the files came from originally.  Without that, I'd have a big collection of DOC files and no idea of where they belonged.  Note that files bearing identical names, coming from different folders into one, could require some advance manual renaming to avoid overwriting.  In that case, after renaming but before moving, it would probably be advisable to re-run DIR, so as to get the current filenames.

Once the files were all in a central location (in this case, D:\Conversion), it was time to work up the batch conversion process.  For this, first, I set the General and Options tabs in Bullzip (my free PDF printer) so that it would operate without asking questions or opening PDFs, and would save the PDFs to a designated folder (D:\Conversion\PDFs).  Then I saved this command into a batch file that I called Converter.bat:

FOR /F "usebackq delims=" %%g IN (`dir /b "*.doc"`) DO "C:\Program Files (x86)\Microsoft Office\Office11\winword.exe" "%%g" /q /n /mFilePrintDefault /mFileExit && TASKKILL /f /im winword.exe

I saved Converter.bat in the folder containing the DOC files (in this case, D:\Conversion) and ran it.  It worked away for a while, at the speed of one document every few seconds, until it had produced one PDF for each of my DOC files.  Several times during the process, Word or Bullzip stalled with error messages (e.g., "Word cannot start the converter Rftdca32.cnv").  This seemed to result primarily from corrupted Word docs.  There seemed to be little alternative but to delete those files except where I could find a backup.

Now I had a set of DOCs and a set of PDFs.  One easy way to make sure that I had a copy of PDF for each DOC was to view the folders using a Windows Explorer alternative like FreeCommander.  In FreeCommander, I could combine the DOCs and PDFs together, sort by file type, select all DOCs, re-sort by file name, and look for instances in which alternating lines were not regularly highlighted.  (In Windows 7, Windows Explorer had lost the ability to retain highlighting after files were re-sorted.)  At this point or later, one could then just delete all DOCs that did have a corresponding PDF.  DoubleKiller Pro would provide a similar approach.  Another method, more suitable for large numbers of files, was to use the DIR and spreadsheet approach outlined above, writing formulas to check for identical filenames (not counting extensions).  Of course, there was no need to actually delete the DOCs if I wanted to keep both the PDF and the DOC.

I postponed that step to verify, first, that I would not be needing any of the DOCs anymore.  I had previously worked on ways to check PDFs by converting them to JPGs and seeing which ones converted successfully.  In that previous effort, IrfanView (my preferred tool) had not behaved as expected, so I had grappled with other approaches.  This time, however, the quick IrfanView batch conversion went smoothly.  This gave me a JPG displaying the first page of each PDF.  My decision there was that, in the interests of speed (and to avoid having to go through every page of every PDF),  I was content to look just at the first page.  There could still be errors on later pages of a PDF, but that would be rare.  If the first page came through OK, I could be fairly confident that most docs converted successfully.  So now, using IrfanView, I flipped through those JPGs quickly.

With these steps out of the way -- PDFs checked, superannuated DOCs deleted -- I went back to my Excel spreadsheet and worked up batch commands to move the new PDFs back to where the DOCs had been.  I had changed a couple of names along the way, so I had to move those manually, but the rest went automatically.  Project done!

A Backup Arrangement with Beyond Compare

I had been using Beyond Compare (BC) for a year or two.  Over that period, I had settled into what seemed like a decent backup arrangement.  This post describes that arrangement.

For a while, I had a spare internal partition to which I would make backups.  The original concept there was that I would use rsync or some other program to make backups on an hourly basis.  That setup had fallen into disrepair, mostly because I didn't quite like how it was working.  So I didn't have an hourly backup at this point.  The arrangement described here is more on the longer-term (e.g., daily, weekly, monthly) level.

My backup took place on external drives.  I had an external enclosure that I would have to open up (removing several screws) to swap drives, and I also had an inexpensive dock that I could just plug an internal SATA drive into.  Both seemed to work equally well.  The enclosure was handy for unplugging the drive and taking it with me.  Now and then -- especially when the tornado alarms sounded -- I visualized myself grabbing it and running for the basement.  I wondered if that would be one of those fateful delays that would cost me my life.

The external enclosure had an eSATA connector, but my previous motherboard had not been able to accommodate eSATA on a hot-swappable basis.  In other words, I had to reboot in order to get the system to recognize it.  It also had a USB connector.  The external dock (i.e., not the enclosure) was also a USB device.  USB was slower but very adaptable.  That was almost always what I used.  Some partitions on the external drive were compressed, to save space.  I had the impression that this did not help with the USB connection -- that the CPU would unpack the file before shipping it across the slow USB cable to the computer, resulting in at least as much data moving along the wire -- but I hadn't verified that.

For my purposes, Beyond Compare offered two key concepts.  First was the workspace.  If I plugged in the external drive that I used for daily backups, then I would open up the DAILY workspace in BC.  If I plugged in a drive that I used for weekly backups, then I would choose the WEEKLY workspace.  I also had a SIMPLE COMPARE workspace that I would use for random tasks -- say, comparing two folders on a one-shot basis.  And I had a NETBOOK workspace that I would use to synchronize my laptop.  GoodSync might have been better for that if I had been using the laptop frequently, but at this point it was mostly a case of keeping the data on the laptop current with the desktop.  That is, I was mostly doing one-way updates, from desktop to laptop.

My workspaces differed in the tabs they made available.  In the DAILY workspace, I had a tab for each day of the week, plus whatever other comparisons I would want BC to make on a daily basis.  Likewise for the WEEKLY and the other workspaces.  In other words, I used a workspace as a place where I would be able to see tabs for each comparison that I wanted BC to make, whenever I plugged in the weekly drive or the laptop or whatever.

I found that the best approach was to start BC first, let the workspace load, and only then turn on or connect the external USB drive.  That way, BC would not try to do complete comparisons for all of the open tabs.  It would do its calculations for the relevant folders on the drives inside the computer, which were already available to it, but on the external drive it would have to wait until I gave it the go-ahead within a particular tab.

Focusing on the DAILY workspace, I was writing these notes on a Friday.  So to guide my remarks, I opened BC at this point.  Somehow, I had arranged for the DAILY workspace to come up by default; or maybe BC just defaulted to the last open workspace.  I wasn't sure how I had arranged that.  When BC was up and running, I turned on the USB drive.  It took that drive a moment to become available.  (I found that AntiRun was useful, not only for protecting my system from autorun malware and such, but also for telling me when a drive really was online or offline, and for giving me a functional way of taking external drives offline.)

I went to the Friday tab.  BC had stalled because the Friday folder on the external drive had been unavailable.  I told it to retry; and now that the USB drive was connected, BC ran its comparison.  (Details on the kinds of comparisons available, and other program capabilities, are available at Beyond Compare's website.  Their forums and other tech support had been very responsive, the few times I had contacted them.)

I had modified my BC toolbar to present the red Mirror button.  This said, basically, just overwrite whatever is in the backup space (in this case, the Friday folder on the USB drive) with whatever is on drive D in the computer.  Drive D was the one that I backed up daily.  So in this case, a number of files had changed since the previous Friday.  Sometimes I would take a look at them; sometimes not.  Usually not.  It seemed pretty rare that a file would be accidentally deleted.  Daily examination of all changing files had seemed to be overkill.

When I say that I would take a look, I mean that BC showed me two panes, one for each of the folders being compared.  To keep things organized, the left-hand pane was almost always the authoritative one.  The left-hand pane would correspond, that is, to a partition inside the computer.  So I was looking at the right-hand pane, corresponding to the backup device.  If I saw a file listed in the right-hand pane, but not in the left-hand pane, that would mean that it was on the system when I made my backup a week ago, but now it was no longer on the system.  BC would also alert me, with a red font, if the file in the right-hand pane was newer.  Generally speaking, that wasn't supposed to happen.

I had an alternating weekly folder on this backup drive.  I used that one on Saturdays.  That's the one I examined more closely.  If I found that something was missing on Saturday, and I didn't think it should be missing, I could then click on the tabs for the other days of the week until I found the last backed-up version, and I could restore it from there.

Drive D contained the things that were in more active use.  I also had a separate partition, drive E, for things that took up a lot of space and didn't change very often.  Videos were the main example.  Because there were so few changes there, it was easier to look at the differences identified in BC, and verify that additions and deletions were desired.

In net terms, I liked this arrangement because it gave me some flexibility to combine automated and manual processes.  I wasn't vulnerable to one of those black-box backup solutions that would seem like they were working just fine until the moment of crisis, when I would painfully discover that I had failed to adjust some essential setting, or that the drive was malfunctioning, or whatever.

In this arrangement, if I was worried that files were missing, I could look down through lists of what was being added and deleted.  If I was confident that everything was fine, I could just click the Mirror button and the backup would happen.  I could also combine both approaches within a single tab, by telling BC to mirror only the selected folder(s).  This would gradually reduce the number of things remaining on the screen (assuming I had BC set, as usual, to Show Differences rather than Show All).  When confronted with what looked like a mess, I could thus eliminate the parts that seemed OK, and focus on the files and folders that didn't seem like they should have been getting added or deleted.

Like most other computer-related matters, my backup approach continued to evolve.  But as I say, I had been using BC for a while, at this point, and I was pretty much satisfied with the combination outlined here.

Open a Search for a Certain Type of File Every Day

I was in the process of converting scattered Microsoft Word .doc files to PDF.  I had found a way to automate the conversion of large numbers of such files, provided I was willing to put them all into a single folder; but that left me with some that would have to be handled on a more piecemeal basis, one folder at a time.

To aid in this process, I wanted my computer to give me a list of where my remaining .doc files were located, so that I could choose the ones that were ripe for conversion.  I had found Everything to be a very useful file finder.  On a one-time basis, I could find my Word docs by just typing *.doc on the Everything search line.  But now I wanted a command-line solution that would run Everything and do that search automatically.

I had not previously tried using Everything with command-line options, but now I found those options in the Everything wiki.  For this purpose, I found that this command would do the trick:

Everything -filename *.doc

That command worked because I had put a copy of Everything.exe (originally named Everything-1.2.1.371.exe) into C:\Windows.  Now, if I wanted that command to run on a regular basis (say, once a day, or once a week), I could add it to a regularly scheduled batch file.  Not that I would have to; I could also just run it from the command line as needed, or save it in a batch file that I would run by double-clicking on it (or on a shortcut to it).

File Naming Conventions

I had a bunch of files. I was looking at ways to sort them. It seemed that it might help if they were all named according to file naming conventions, so that files of a certain type would be named in certain standard ways.

It was not immediately clear if there was any scholarly consensus as to what approach would be best. Along with general advice, there seemed to be at least two fundamentally different philosophies. On one hand, sources like the National Technology Assistance Project (NTAP) recommended using a folder hierarchy and relatively plain-English filenames. For instance, a file named "2009-05-15 Letter to Smith, R P" might be found in the 2009 > Correspondence subfolder; or in a different scheme, it might be in the Completed Projects > Waterway subfolder. On the other hand, Vincent Santaguida recommended putting the information into the filename itself and avoiding folder hierarchies. (I found that document and others on an Exadox webpage.) Santaguida's first example said this:

Do: Z:\Prod\QA\AssL7_WO_Suzuki_L3688_20090725.xls

Don't: Z:\Production \Quality Control\Assembly Line7\Work Orders\Clients\Suzuki Motors\ LOT3688_July‐25‐2009.xls

Depending on who was using the files and how much they knew about the variety of filenames in the archive, it seemed that Santaguida's approach might benefit from a formal, elaborate naming scheme -- with, for instance, a reference work where users could look up "Quality Control" or "Assembling Line 7" (and other variations) and find the proper rules for naming documents related to those topics, and a list or guidance system leading to relevant documents already filed. I could see where such a system might be valuable in some settings. I had a couple of concerns about it, though. One was, what happens if you lose the reference list, or if the specialized database management system creating such filenames goes on the fritz?

It seemed that, for most purposes, PC Magazine had the better idea: make your filenames indicative of what the file contained, in terms that potential users could understand -- and, I would add, within a scheme that would not require more maintenance than users or database managers would devote to it. For instance, aside from special projects like this one, I was not generally going to invest the time to create a highly precise file naming arrangement. It did seem that Santaguida's approach could help reduce file duplication, but I felt that DoubleKiller gave me an adequate solution for that. The other thing was that I didn't actually know how life was going to turn out yet. File arrangements grew up on the fly, as new situations emerged. I wasn't positioned to put it all into a rigid structure.

In other words, while adopting some principles recommended in the Best Practice for File-Naming produced by the Government Records Branch of North Carolina, I was concerned that "Records will be moved from their original location" -- that, in other words, I might have to re-sort things that I had already sorted once -- but I didn't see an easy way around that. Building their location into the filename would have been a bad idea because, in many cases, I *wanted* to be able to re-sort things at random.

Within the individual filename, Santaguida's second principle seemed right: "Put sufficient elements in the structure [particularly in the filename] for easy retrieval and identification but do not overdo it." I had been working toward a couple of basic formats:

2009-05-04 13.59 Message from X to Y re Z.pdf
Shakespeare--Romeo and Juliet.pdf
Shakespeare--Romeo and Juliet - with notes.pdf
Garfunkel--Bridge Over Troubled Water.mp3

The present project, I decided, was one in which I could mostly tend toward the first example: Year-Mo-Da Hr.Mn DocType from Sender to Recipient re Subject.ext. I would use periods and hyphens (-)for some limited purposes, but would tend not to use other punctuation. This tended to agree with Santaguida's third rule: Do not use characters such as ! # $ % & ' @ ^ ` ~ + , . ; =)]([. Santaguida said don't use spaces, but I had rejected that in opting for plain-English filenames. He also said to use the underscore (_) to separate filename elements, but that was unnecessary in my approach. It also had the potential to confuse things. I noticed that some naming and conversion programs used the underscore in place of the space, giving me "File_name.exe" instead of "File name.exe." In Santaguida's approach, that would falsely suggest that "File" and "name" were two different elements. I planned to scout out and remove underscores. The intention to minimize punctuation also seemed generally consistent with various uses of special punctuation in Windows.

I also had to think about Santaguida's seventh principle. He recommended putting surname first, first name second: "Smith, Roger" rather than "Roger Smith." Actually, in his approach, it was "Smith-Roger." It seemed to me that there were some reasons not to do it that way, at least in my system. One was that I would have to sweep filenames (at least new filenames) for consistency occasionally at any rate, to catch and rename those newly created documents where some variation appeared (e.g., "Smith, R.P.," "Smith, R P," 'R Smith"). There didn't seem to be any difference between one approach and the other in that sense. What seemed more practical was to use whatever name I actually tended to use for someone, so that I would be most likely to name it correctly the first time, when I was thinking about something other than my file naming scheme. Typically, this would be along the lines of "Roger Smith" -- which would also have the advantage of eliminating extra hyphens and commas.

Once I had such ideas in mind, I went through the list of files, using Excel to generate batch commands to rename many files at once. Where the files had one of the structures shown above, I was able to use FIND and other text functions to segregate certain elements (e.g., Author, Date) into separate columns, and then use unique filters and other tools to eliminate variations (in e.g., personal names).

Mitigating a Data Verification Nightmare: Thoughts on Removing Duplicate Files

I had gotten myself into a data nightmare, with a bunch of files that appeared partly duplicative of one another.  I wanted to get rid of the duplicates.  This appeared likely to be a long struggle.  This post is one battle in that war.

For starters, I used DoubleKiller (I had the pro version, but the freeware one would have helped too) to pluck the low-hanging fruit -- to delete, that is, the verifiably exact duplicates.  But now there were files with almost identical names (e.g., Longfilename and LongfilenameA), files with identical names but different extensions (e.g., was Filename.pdf simply a PDF version of Filename.doc?), filenames with slight differences (e.g., was 2010-09-10 Résumé an essentially identical duplicate of 2010-09-10 Resume if their times were identical but their sizes were different?), and so forth.

It was easy enough to just guess at it and delete the ones that looked like they might be duplicates.  In many cases, that would have been fine; it wouldn't have made any real-world difference.  Obviously, though, this would not be not a good data management solution.  More like data abdication.  Second-best, I could identify likely duplicates and put them in a ZIP file, out of the way.  One problem with ZIP files, I had found, was that the reason for zipping them could fade from recollection, over a period of years; and then, one bright day, someone might decide to see what was in there, and the monster would live again.

One general underlying purpose was to have files that would actually be useful.  I thought that the processes of gradually absorbing useful files and eliminating unnecessary ones might be aided if I could sort them by topic.  Here, again, there were some obvious ways of quickly taking care of large numbers of files, such as those that were already sorted into folders with meaningful names.  But that left quite a few that were not usefully categorized.

In some cases, I could categorize files just from the information in their names.  But I hated to spend the time to do it manually.  I tried to sort them into categories by identifying key multiword phrases they contained.  That problem became complicated by variations in punctuation and other textual vagaries.  Therefore, I started over, this time beginning with an effort to clean up punctuation and other aspects of the text.

While that cleanup attempt was underway, I also looked for ways to reduce the number of filenames being sorted.  This brought me back to a focus on identifying duplicates.  It seemed, belatedly, that it would have been useful to have named all files according to a consistent rubric.  I took a look at that in a separate post.  That got me to a point where I was able to name many of the files in a certain standard way.  So, for example, an email would be named using a Date-From-To-Subject format.

Along the way, certain realizations forced themselves into my consciousness.  One was that, as a general rule, if it looks like a mess, it is probably a mess in ways that you haven't even imagined.  A corollary is that whatever you do, you will have to do over again, once you have discovered additional unforeseen ways in which the data are intertwined.  I did find that doing it wrong, several times over, was a good way to become familiarized with possible starting points.  Having a good backup was essential.  Being able to reconstruct steps, by saving relevant documents in generational steps, was a real plus.

There was also the problem of deciding whether to nibble around the edges or make a decisive stroke to divide major problem areas from one another.  The quandary here was that the decisive stroke would likely go astray if it was not informed by prior familiarity with the actual fault lines shooting through the data.  In the worst case, you would not only add to the confusion, but would divide things in exactly the wrong direction, so that the procedures taken with Group A would have to be repeated with Group B -- except that, inevitably, they could not be repeated *exactly* with Group B, because the two groups would differ in some subtle but significant way.  But nibbling around the edges could turn relatively simple aspects of the project into enormously tedious exercises in repetition, as exceedingly minor pieces of the puzzle were interminably quasi-resolved.

I made these notes during a particularly dark moment in the process.  I am pleased to report that, later on, when I returned to these notes to wrap them up and post them, I had turned to other projects, and was thus making good progress toward resolving the data verification nightmare by simply ignoring it until it (or I) went away.

Using Excel to Clean Unwanted Characters from Filenames or Other Text

I was trying to organize some files.  This post contains some notes I made on ways of using Microsoft Excel to change a list of filenames.  The renaming project drew on file renaming techniques described elsewhere.

In various attempts in this process, I came up against situations where inconsistent punctuation and other filename characteristics made the process unnecessarily complicated.  For instance, file names might refer to "Barack Obama," "Barack H. Obama," "President Obama," and so forth.  These would all be alphabetized differently.  Duplicates might be concealed, and files that belonged together topically might be overlooked.

In part, that was an issue of choosing and using consistent file naming conventions.  But at this point, given that the files were already named, there was an issue of how to deal with punctuation and other non-alphanumeric characters.  I wondered if there was a relatively straightforward way to get my arms around the varieties of character usage within a particular corpus.

The first task seemed to be identifying what unwanted characters were in use. Taking Microsoft Excel as my weapon du jour, I made a list of the filenames that I might want to change, using DIR on the Windows command line (see the comments following another recent post). I pasted that list of filenames into Excel.

Next, I ran a search and found several ingenious approaches to related problems. One would use a wildcard search-and-replace in Microsoft Word:   search for ? (making sure to indicate that this would be a "Use wildcards" search) and replace with ^&^p to put the same character (that is, any character) on a line by itself; then paste the whole text into Excel and do a unique filter to get all of the unique characters found in the text. This did not seem likely to work for my purposes, unfortunately. For one thing, Excel 2003 was limited to 65,536 rows, and there could easily be more characters than that in a text being analyzed.  I had also found that spreadsheets capable of handling more rows could be slow at it.

There were other approaches that used an array formula or a script. I was not as comfortable with those that I did not understand, because it would be difficult for me to work through their logic and make sure they were really doing what I wanted them to do. An unnoticed mistake could have unfortunate effects when altering a large or important text, or when renaming thousands of files.

Another possibility came to mind as I looked at the suggestion to use MoreFunc's REGEX.COUNT function (alternately, Excel's SUBSTITUTE or possibly MATCH) to produce character-by-character counts.  I ran a search and found some interesting variations in SUBSTITUTE, including nested and case-insensitive substitutions, along with the CLEAN function. One solution, it seemed, would be to use 26 columns containing something like =SUBSTITUTE(UPPER(A1),"","") to replace all upper- and lower-case letters with nothing, another 10 columns to do the same for numbers, and then see what's left.

With SUBSTITUTE, one possibility was to set up a map. Put the rows of text being analyzed in one worksheet ("Sheet1"). Create a second worksheet ("Sheet2") within the same Excel file.  In Sheet2, make each row relate to the row of the same number in Sheet1. So, for example, if there was a text string in cell A5 in Sheet1, there would be a row of characters across row 5 in Sheet2. Each cell in that row would count the occurrences of a specific character. For example, the 65th column would detect occurrences of the character whose Excel code happened to be 65 (that is, the letter "A," producible by =CHAR(65)).  The chief problem with this approach was that its calculations would take a year.  It would also max out Excel 2003's limit of 256 columns.  It would have the advantage of transparency:  I would be able to see much of what was happening in the data.

I found a user-defined function (UDF) -- what I had been referring to as a script -- called SUBSTITUTELIST.  This UDF would let me indicate a list of characters to simply remove.  In naming files, I would definitely not want to include many of the odd characters that were readily visible if, say, I opened a non-text file in Notepad.



The first step, with SUBSTITUTELIST, was to define it as a function that I could use in spreadsheet formulas.  To do that, using the steps described atop another post, I copied and pasted this text into an Excel module:

Function SubstituteList(rString As String, rList As Range) As String
Dim rCell As Range
For Each rCell In rList
rString = Replace(rString, rCell.Text, "")
Next rCell
SubstituteList = rString
End Function

I didn't write that code, and was only partly clear on what it said.  It appeared, though, that its middle line was going to replace rString with nothing (""). In other words, rString was being removed. Now I had to tell it what characters to treat as rString. In the spreadsheet listing the filenames I wanted to work on, I opened a new worksheet, called Sheet2, and put the numbers 1 through 255 in cells A2 to A256. (Two easy ways to insert a series of numbers: use =A1+1 in cell A2, and so forth; or enter the first two numbers (i.e., 1 and 2) manually, highlight the cells to be filled, and then use Edit > Insert > Series. The Alt command option would probably still work in post-2003 versions of Excel: Alt-E, I, S, Enter.)

Then, in Sheet2 cell B1, I typed =CHAR(A1), and copied it down to B255. So now column B contained the actual characters I wanted to look at, whose CHAR equivalents were shown in column A. Finally, column C contained the characters to be removed from consideration.  For example, in cell C128, I typed =B128.  That put a copy of CHAR(127) into column C.  SUBSTITUTELIST was going to remove anything in column C.  So that particular character would be removed from the cell in Sheet1 where I was going to be using SUBSTITUTELIST.  Depending on the situation, I might or might not want to remove all non-alphanumeric characters.  For instance, I thought that what I would really want to do with something like the euro character (€), in a filename, would not be to eliminate it, but rather to replace it (e.g., €35 becomes EUR 35).

To use SUBSTITUTELIST, I put my text to be analyzed in column A of Sheet1. In B1, I entered this: =SUBSTITUTELIST(A1,Sheet2!$C$1:$C$255). I copied that formula all the way down, next to each text string to be examined. That gave me a collection of funky characters in Sheet1 column B. Originally, I added TRIM to the SUBSTITUTELIST formula just cited, but then I realized that I actually wanted to see the pattern of spaces and other non-alphanumeric characters.

SUBSTITUTELIST seemed to have potential for single-character eliminations, but it didn't seem capable of substitutions involving multiple-character strings (e.g., EUR in place of €).  It looked like a relatively simple change to SUBSTITUTELIST might do the job, but I had little ability for that sort of work at this point, so I posted a question on it.  One reply to that question suggested a UDF called CLEANUPTEXT.  The contents of that UDF (created in the same manner as SUBSTITUTELIST, above) were as follows:

Option Explicit
Sub cleanupText()

Dim allTxt() As Variant, sublist() As Variant
Dim i As Long, j As Long, k As Long, tdots As Integer

'Store data from sheets in arrays.
allTxt = Sheets(1).UsedRange.Value
sublist = Sheets(2).UsedRange.Offset(1, 0).Resize(Sheets(2).UsedRange.Rows.Count - 1, Sheets(2).UsedRange.Columns.Count).Value

For i = 1 To UBound(allTxt, 1)
    For j = 1 To UBound(allTxt, 2)
        'Loop through replacement terms and make replacements to data in array.
        For k = 1 To UBound(sublist, 1)
            allTxt(i, j) = Replace(allTxt(i, j), sublist(k, 1), sublist(k, 2))
        Next k
        allTxt(i, j) = Trim(allTxt(i, j))
        'Remove series of trailing periods.
        If Right(allTxt(i, j), 1) = "." Then
            tdots = 1
        Else
            tdots = 0
        End If
        Do While tdots = 1
            allTxt(i, j) = Left(allTxt(i, j), Len(allTxt(i, j)) - 1)
            If Right(allTxt(i, j), 1) = "." Then
                tdots = 1
            Else
                tdots = 0
            End If
        Loop
        allTxt(i, j) = Trim(allTxt(i, j))
    Next j
Next i
'Print cleaned up results in array onto sheet.
ActiveSheet.UsedRange.Value = allTxt
End Sub

The instructions accompanying that UDF seemed to indicate that it required a separate worksheet, called Sheet2, containing the "before" terms in column A of Sheet2, and the "after" terms in column B of that sheet; and Sheet1 had to be the name of the tab containg the text being operated on.  In response to my question, the text shown in blue (above) was designed to remove trailing dots from the text being modified.  I did not actually try this UDF, but I hoped that I would not have to write similar lines of text for other kinds of unwanted text strings -- that, in other words, the column A / column B replacement approach would suffice.  If so, I might try to simplify this UDF by removing the lines in blue.

On a separate note, I had noticed that some of the files in my list looked like their names were identical except for punctuation, so I added a column, in my spreadsheet that listed filenames, for this formula:  =LOWER(substitutelist(M2,Sheet2!$C$1:$C$255)).  M2 contained the existing filename, or part thereof, and Sheet2 column C was set to eliminate all non-alphanumeric characters, as shown above.  This step did succeed in highlighting some duplicate filenames and other issues.

Another problem was that, in some cases, I would have to rename files before proceeding with something like SUBSTITUTELIST.  The specific problem was that some characters in filenames would defeat batch commands.  That is, I could have a command to change a filename, perhaps in response to an insight emerging from SUBSTITUTELIST; but the command might not run.  So I would have to change or delete those troublesome characters first.  (I often used Excel formulas to generate commands that I could run in a batch file to rename numerous files.  The basic idea here was that I would put the existing filename in cell A1 of an empty worksheet, and the desired filename in cell B1, and then I would enter this formula in cell C1:  ="ren "&CHAR(34)&A2&CHAR(34)&" "&B2. (If the new filename in B2 had spaces in it, then it too would have to be surrounded by CHAR(34) instances, as done in that formula for cell A1.)  The particular chacters at issue here were percent symbols and possibly exclamation marks.  The commands were right.  They would run if I typed them into the command line, or copied and pasted them there.  But they wouldn't work from within a batch file.  To address this concern, I just deleted the exclamation marks, and I replaced the percent symbols with "pct" (e.g., 45% became 45pct).

In some cases, I would want a tool that could replace one or more undesirable characters, in a single bulk process, with one or more preferred characters.  In part, this was a simple interest in transliteration or Romanization (i.e., converting foreign-language characters to the standard English alphabet). There would be at least two reasons to do that. One was that I had run into some file handling issues with some such characters. I recalled one recent case where something that looked like a C (maybe ḉ or ć or ĉ or ċ or č) was not working correctly with some backup program or something. The other reason was that I might not remember which character to use when doing filename searches.  So, for instance, if I was looking for all of the files containing a copy of someone's resume, I might or might not want to have to search for both "resume" and "résumé."  There were many Romanization tables for various languages, possibly best approached by finding conversion tables for the thousands of possible Unicode characters.  This could take further attention, as there were multiple kinds of Unicode, not all of which might be consistent with Excel's coding.  In filenames, especially, examples involving several characters could include "pct" (above) and the replacement of $ with USD and of © with (C).  One readability example, arising where HTML has become involved, would be to replace the HTML code of ' with the apostrophe.

As I thought further about the problem, I realized that characters might be unwanted in context, not in isolation. For instance, the hyphen ("-") could be fine in some settings, not fine in others. At the risk of opening the door to complexity, it seemed the ideal approach (perhaps the less messy approach, in the long run) would give me the possibility of listing multi-character combinations and indicating their desired replacement. I found a UDF called SUPERSUB that seemed to be just what the doctor ordered:

Function SuperSub(OriginalText As String, rngOldText As Range)
    Dim cel As Range
    Dim strOldText As String, strNewText As String
     'loop through list of old_text used in substitute
    For Each cel In rngOldText.Cells
        strOldText = cel.Value
        strNewText = cel.Offset(0, 1).Value
        OriginalText = Application.WorksheetFunction.Substitute(OriginalText, strOldText, strNewText)
    Next cel
    SuperSub = OriginalText
End Function

But, again, I did not try this script at the time, but was merely preserving its information here for possible future application.

Another post offered a UDF called REMOVEPUNCTUATION where I could create a list of items to replace, and their replacements, within the macro.  The text of that one (with a few illustrative examples) was as follows:

Sub test()
Dim c As Range, t As String
For Each c In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
    t = UCase(" " & RemovePunctuation(c.Text) & " ")
    t = Replace(t, " AVE ", " AVENUE ")
    t = Replace(t, " BLVD ", " BOULEVARD ")
    t = Replace(t, " BVD ", " BOULEVARD ")
    t = Replace(t, " CYN ", " CANYON ")
    t = Replace(t, " CTR ", " CENTER ")
    t = Replace(t, " CIR ", " CIRCLE ")
    t = Replace(t, " CT ", " COURT ")
    t = Replace(t, " DR ", " DRIVE ")
    t = Replace(t, " FWY ", " FREEWAY ")
    t = Replace(t, " HBR ", " HARBOR ")
    t = Replace(t, " HTS ", " HEIGHTS ")
    t = Replace(t, " HWY ", " HIGHWAY ")
    t = Replace(t, " JCT ", " JUNCTION ")
    t = Replace(t, " LN ", " LANE ")
    t = Replace(t, " MTN ", " MOUNTAIN ")
    t = Replace(t, " PKWY ", " PARKWAY ")
    t = Replace(t, " PL ", " PLACE ")
    t = Replace(t, " PLZ ", " PLAZA ")
    t = Replace(t, " RDG ", " RIDGE ")
    t = Replace(t, " RD ", " ROAD ")
    t = Replace(t, " RTE ", " ROUTE ")
    t = Replace(t, " ST ", " STREET ")
    t = Replace(t, " TRWY ", " THROUGHWAY ")
    t = Replace(t, " TL ", " TRAIL ")
    t = Replace(t, " TPKE ", " TURNPIKE ")
    t = Replace(t, " VLY ", " VALLEY ")
    t = Replace(t, " VLG ", " VILLAGE ")
    t = Replace(t, " APT ", " APARTMENT ")
    t = Replace(t, " APTS ", " APARTMENTS ")
    t = Replace(t, " BLDG ", " BUILDING ")
    t = Replace(t, " FLR ", " FLOOR ")
    t = Replace(t, " OFC ", " OFFICE ")
    t = Replace(t, " OF ", " OFFICE ")
    t = Replace(t, " APT ", " APARTMENT ")
    t = Replace(t, " STE ", " SUITE ")
    t = Replace(t, " N ", " NORTH ")
    t = Replace(t, " E ", " EAST ")
    t = Replace(t, " S ", " SOUTH ")
    t = Replace(t, " W ", " WEST ")
    t = Replace(t, " NE ", " NORTHEAST ")
    t = Replace(t, " SE ", " SOUTHEAST ")
    t = Replace(t, " SW ", " SOUTHWEST ")
    t = Replace(t, " NW ", " NORTHWEST ")
    c = Trim(t)
Next
End Sub

Function RemovePunctuation(r As String) As String
With CreateObject("vbscript.regexp")
    .Pattern = "[^A-Z0-9 ]"
    .IgnoreCase = True
    .Global = True
    RemovePunctuation = .Replace(r, "")
End With
End Function

It seemed that, if I used REMOVEPUNCTUATION to make a large number of changes (more than just the few dozen shown here), I might use Excel to generate the text of the additional lines to be inserted into the REMOVEPUNCTUATION script.

That was the extent of these notes.  I would have to await another situation requiring mass file renaming in order to test these techniques.

Saving Disk Space; Finding Types of Files to Shrink

I wanted to save drive space.  The first line of attack was to use a freeware program to find large files.  Among the various possibilities, WinDirStat, TreeSize, and SpaceSniffer seemed to be the most positively reviewed and/or familiar to me.  Among those three, TreeSize and WinDirStat used a directory listing to indicate the largest folders, while SpaceSniffer and WinDirStat used a graphic approach to highlight large individual files.  In other words, WinDirStat offered both.  The graphic approach led me to multiple space-saving solutions faster than the TreeSize approach.  Between the two graphic progams, I found SpaceSniffer's graphic presentation more readable and zoomable than that of WinDirStat, except that WinDirStat made it easier to tell, right from the start, whether a folder was large because it had many small or few large files.  SpaceSniffer also offered a full right-click Windows Explorer context menu, while the right-click options in WinDirStat were more limited.

This sort of program was good for quickly locating large space hogs.  The concept, which I pursued to some extent, was that you would probably free up the most space most quickly by homing in on very large files or folders.  But once I got past the point of being impressed by the graphics, I noticed certain drawbacks.  One was disorientation, especially in SpaceSniffer:  its method of zooming in on a particular folder was not the exact opposite of its method of zooming out.  It seemed like I was coming out by a route different than the one I had gone in on.  So it could be hard to build an intuitive sense of where you were located, with respect to the drive or directory as a whole.  Another drawback was that I could not compare across partitions.  I had to back out and start over, or run a different session of the program, to see clearly that I should be focusing on one drive rather than another.  Another missing dimension of coherence:  file type.  I might never know, from looking at the graphic maps, that I was consistently making PDFs or JPGs that were larger than they needed to be.  Even a grossly large PDF could escape notice when nestled among AVIs ten times its size.  There was also no logging or comparison capability that might alert me to the fact that a certain folder had been growing more rapidly than I would have expected -- or, for that matter, that a certain folder had disappeared since the last time I ran the comparison.

I thought that I might be able to capture the chief benefits of these programs -- that is, their ability to draw attention to large files -- while also adding at least some of those missing ingredients (though admittedly I could not compete with their graphics).  What I wanted, for this purpose, was a simple file list that I could sort according to selected criteria, particularly folder size, file date, file size, and file extension.  This approach seemed likely to require a much larger time investment up front; but once I had the file list, it seemed I would be able to do quite a bit of analysis and revision of files and processes, so as to root out a variety of kinds of disk space waste.  In short, I was seeking a systematic approach that would minimize my preoccupation with the same few large files (which may have had to remain large for good reason), turning my attention instead to other areas where I could make an impact on drive bloat.

The approach I took was to develop a somewhat automated method of generating a list of files across multiple partitions, and then put that list into a spreadsheet.  It took some work to figure out a way to automate the production of a file list that would work simply in a spreadsheet.  The problem was that simple batch commands, with which I had some familiarity, did not want to put all relevant information about each file on a single line.  (While I was particularly interested in the full path, file size, and date, others might have wanted to draw on some other available types of file information, such as file attributes.)

As described in another post, I found the desired solution by installing TCC/LE and running its PDIR command in a batch file. This operation required two parts. First, I had to work up the command that I would run to make everything happen. This command would start TCC/LE and would tell it to open a batch file. To find where TCC was, I used the Properties > Target path I found in a right-click on its Start Menu icon. To see if this would all work, I generated a little batch file called Test File.bat. Then I ran this:

"C:\Program Files\JPSoft\TCCLE13x64\tcc.exe" "D:\Current\Test File.bat"

It worked. Test File.bat ran. So I went ahead and replaced the second part of that command with the name and location of the real batch file that I wanted to run. I called it ListAllFiles.bat, and I put it in a permanent location with other batch files that I would run for various purposes. (For the moment, it was an empty file, but that would change momentarily.) I also made a copy of the Start Menu shortcut that would run TCC. I modified that shortcut's Properties so that its Target line contained the information just described: the path to tcc.exe, and then the path to ListAllFiles.bat, each in quotation marks as shown in the line quoted above. Now I could double-click on that icon to make TCC run ListAllFiles.bat, instead of having to look up the proper command syntax. Once that was done, I just needed to make sure ListAllFiles.bat said what I wanted. I was still working on that, but for now it looked like this:

@echo off
cls
echo.
echo For cleanest results, empty the Recycle Bin before proceeding.
echo.
pause
cls
echo.
echo Notepad may take several minutes to display a large list.
echo.
echo Be patient ...
echo.
:: PDIR requires TCC/LE to be installed

pdir D:\ /s /(dy-m-d zc fpn) > "D:\Current\List of All Files on D and E.txt"

pdir E:\ /s /(dy-m-d zc fpn) >> "D:\Current\List of All Files on D and E.txt"

start notepad.exe "D:\Current\List of All Files on D and E.txt"

exit

The last four lines (double-spaced for clarity, in case the blog wraps them) were where the action was; everything before that just displayed a few informational notices and a comment about PDIR that would not be visible when the batch file ran. I opened the resulting text file, "List of All Files on D and E.txt," in a LibreOffice Calc spreadsheet, since LibreOffice (alternately OpenOffice 3.3) could accommodate a large number of rows. When I did that, LibreOffice Calc detected that it was a text file and defaulted to LibreOffice Writer, but I just copied and pasted from there into Calc. I told it that the imported text had fixed-width columns, and I pointed out where those columns were.  LibreOffice Calc crashed repeatedly during this process -- I had to remember to save frequently -- but in the end it came through.

So I had my table. I added a column to display the extension, extracted it using the RIGHT function (using different values to extract extensions of different length, e.g., .html, .px), and saved the extension in an adjacent column (i.e., undisturbed by those calculations of varying extension length).  I added a column in which I could record the date on which I had last examined the file to see whether it was currently feasible to shrink it, and another column for notes.  For instance, I had a 6GB zip file that I couldn't get into right now, because there were things I needed to do and learn before I would be ready for the project that its opening would commence.So I added that "Last Examined" column.  For purposes of making a first pass through the files, I could filter out that one, among many others, as not being of further concern right now.

Now that the spreadsheet was ready, I could do some file sorting.  First, I sorted by date, and also by extension, to find any files whose name or other properties might need to be adjusted, perhaps with the aid of a relevant utility (e.g., SetFileDate, TrID).  Then I sorted by file extension.  I added a Flag column to the spreadsheet, to indicate files that would be worth looking at, to see if I could possibly shrink them.  I put an X in the Flag column for each AVI file.  I suspected that, with or without editing, most AVIs could probably be converted to MP4 or some other more compressed format that would retain about the same apparent quality for a fraction of the space.  Likewise for BMPs (most could probably be JPGs) and WAVs (many could be converted to MP3).  I also flagged all ISOs, which I probably didn't need to keep, and all ZIP (and RAR and 7z) files, because they could hold a lot of unnecessary stuff somewhat removed from notice.

Next, I sorted the spreadsheet in order of declining file size, and flagged all files larger than 500MB.  Within that sort, I filtered for common image extensions (including PDF as well as JPG and PNG) and flagged all files larger than 100MB.  I could see that these steps were going to draw attention to whole folders full of files.  For example, I noticed a folder of mixed MP3 and WAV files, all of which could have been MP3s.  In that instance, I suspected I would probably wind up converting the whole folder at once.

I figured I would probably refine this approach, if I went back through it again sometime in the future.  But for right now, these steps resulted in the flagging of about 9% of the total number of files on these partitions -- and those 9% accounted for about 60% of disk usage.  I probably wouldn't have time to work through all of those files individually.  But identifying a few major categories of unnecessarily large files did seem likely to yield some reductions in disk usage that I could achieve through mass conversions and other relatively simple steps.  Finally, while I had concluded that I liked LibreOffice Calc -- it had pretty much stopped crashing as I got more familiar with it, thus probably making fewer finger fumbles -- the focus on just 9% of the files gave me a list short enough to handle in Excel 2003.