Friday, May 26, 2006

Network Paths

Available in my UW.DOT and UX.XLS libraries.

In developing an archiving tool, I find a need to manipulate paths. Specifically I want to “spray” several copies of each file to a set of locations, like this:

MsgBox strArchiveToSites("b:\ShellLnk\Module1.bas", _
",b:\archive,\\BBB\Boot80GB (C),\\BBB\A,\\BBB\d", 0)

“BBB” is my Big beige Box (as distinct from LapTop).
Drive C is my R&D drive.
Drive D is the removable 400 GB backup drive on rails.
Drive A is the floppy disk on the BBB; I’m testing, OK? (grin!)

The procedure strArchiveToSites is to copy the Module1.BAS file to each of those areas, and so it may need to create folders (MkDir) on networked drives. VBA can’t cope with this, so I need to assign a drive letter temporarily while I do my stuff.

Because I’ll be issuing the “copy” command repeatedly for the same set of network paths, it makes sense to map each network path to a unique drive letter, copy all the files using the drive letter assignments, and then disconnect the drive letters.

In the example below I’ve shown how I obtain a drive letter for a single path, do My Stuff, and then disconnect.

Sub TESTstrPathToDriveMap()
Dim strTempDriveLetter As String
strTempDriveLetter = strPathToDriveMap("\\Bigbeigebox\Boot80GB (C)", "")
''' do stuff below here
MkDir strTempDriveLetter & "\" & Format(Time(), "hhmmss")
''' do stuff above here
strTempDriveLetter = strPathToDriveUnMap(strTempDriveLetter)
End Sub

In the real world, the pseudo-code would look like this:

Get drive letters for each networked path
For each file to be processed
Call the strArchiveToSites procedure using the mapped drives.
Release the mapped drive letters.

To see how these two functions work for you, paste the macro TESTstrPathToDriveMap into a module, change the literal network path, run the macro, and check that a new folder has been created on your network drive.

(

Available in my UW.DOT and UX.XLS libraries.

Thursday, May 25, 2006

About This Blog

I develop applications, primarily in Visual Basic for Applications (VBA), and primarily using Microsoft Word as a vehicle, but extending to Excel-VBA, PowerPoint-VBA and Access-VBA.

My background in application development has taught me to use libraries of utility procedures to speed up my development.

This blog documents the new and the more commonly-used existing functions that can be found in my UW.DOT and UX.XLS libraries.

You can download the most current copy of those files by clicking on the links.

In some examples you will see a small subroutine “TEST”, commented out. I usually drag this commented test out of the procedure body and de-comment it as a built-in self-testing procedure.

[ Home ] [ Contact Me ]