UNC equivalent path for flash drives

[KGee]

Does such a thing exist? We want to be specify a flash drive by the actual name of the device as opposed to the drive letter which may vary depending on which computer the drive is connected to.

 

[SillyCat]

Sadly no. You could try something like having a Macro which finds out which driver letter the flash drive is on today.

Which then modifies the formulas (Which I assume is what you are referencing the drive letter in?) to have the correct drive letter.

You can run a Macro when you open the spreadsheet adding code to Workbook_Open

Getting the drive letter you could use something like:

flashDriveName = "Label of my Flash Drive"
flashDriveLetter = "?"

Set fs = CreateObject("Scripting.FileSystemObject")
Set dc = fs.Drives
For Each d In dc
    If d.IsReady Then
        If d.VolumeName = flashDriveName Then
            flashDriveLetter = d.DriveLetter
        End If
    End If
Next

Then it would just be a case of updating all the formulas that reference the drive letter to the correct letter using cell.formula

Using Regular Expressions would be the most easy way to do that. You would probably want to move all the formulas with external references (E.g. the onces that would need updating to one sheet and then reference the values in that sheet in the rest of the workbook that was the cells you need to update are all in one range, making it easy to update them all using a For loop.

Anyhow if you need help with that bit feel free to ask, if you fancy going this route of keeping the drive letter up to date.