How do I convert a list of file paths to their short file path, DOS/8,3 format, using a macro in VBA?

Question

I have a list of file paths in cells in Excel that locate to sets of folders, example:

**Start:** G:\TSB\Bridge Shared\Bridge Plans\Bridge Plans-SCANNED\2574 35_Long Creek

Want this: G:\TSB\Bridge~1\Bridge~1\Bridge~1\2574 35~1 (I wrote this by hand so it might not be perfect but hopefully you get what I mean)

I have nearly 3000 file paths listed in Excel but would like them to be displayed in the short file path format, DOS or 8.3 format is what it is called I believe.

Basically I have the folder locations listed in column A in Excel, and I want a macro to run that looks at each individual path and converts it to short path version in column B. There has to be some way to do this automatically and not one by one?

Any help would be appreciated, just can't seem to find anything online to get me started as I am a beginner in VBA.

Answer 1

The FileSystemObject can help you here. It's not clear whether your paths are files or folders, and whether you want just the folder/file shortname, or the entire path in short name format. I've allowed for either Folders or Files (and I check if they exist before trying to get their path), and I return the full path in short name format.

EDIT - Adapted to be used as a worksheet function

You can now use this function in a cell like B1 with a formula like: =GetShortPath(A1)

Option Explicit

Public Function GetShortPath(path As String) As String

  'Add a reference to Microsoft Scripting Runtime

  Static fso As FileSystemObject
  If fso Is Nothing Then
      Set fso = New FileSystemObject
  End If

  'If the path is a file - Output the full path in 8.3 format
  If fso.FileExists(path) Then
      GetShortPath =  fso.GetFile(path).ShortPath
      Exit Function
  End If

  'If the path is a folder - Output the full path in 8.3 format
  If fso.FolderExists(path) Then
      GetShortPath = fso.GetFolder(path).ShortPath
      Exit Function
  End If

End Function