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.

(Read more at http://www.chrisgreaves.com/utils/NetworkPaths.html)

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