Code to convert path name to short/dos style

[localfiend]

I've got a variable path for a file stored in a cell inside a worksheet that I need to convert to the old dos format (no spaces).

I.E.

From c:\Awesome File\FTP\My Awesome File.txt

to c:\Awesom~1\FTP\My~1.txt

The actual path/file name is much longer and is determined by multiple fillable fields, cells, and drop down menu's so the only feasible way at the moment seems to be that I need some code to convert the file/path name all at once.

After doing a little searching on google I came up with several that could determine the short path name of the actual workbook, but none that could convert a path that was different from the workbook.

 

[Ed in Aus]

Try this... haven't tested yet just about to.

run cmd.exe and do the following:

cd "long path name" this is where you would type in: "c:\Awesome File\FTP\My Awesome File.txt"
command

Then command.com will come up and display the short path name.

 

[Ed in Aus]

works like a charm...

Option Explicit

Private Declare Function GetShortPathName Lib "kernel32" _
   Alias "GetShortPathNameA" (ByVal lpszLongPath As String, _
   ByVal lpszShortPath As String, ByVal cchBuffer As Long) _
   As Long
   
Public Function GetShortFileName(ByVal FullPath As String) _
  As String

'PURPOSE: Returns DOS File Name (8.3 Format) Give
'FullPath for long file name

'PARAMETERS: FullPath: Full Path of Original File

'RETURNS: 8.3 FileName, or "" if FullPath doesn't
'         exist or file fails for other reasons

'EXAMPLE:
'Debug.Print _
'  GetShortFileName("C:\My Documents\My Very Long File Name.doc")

'If file exists, will display C:\MYDOCU~1\MYVERY~1.DOC
'in the debug window

Dim lAns As Long
Dim sAns As String
Dim iLen As Integer
   
On Error Resume Next

'this function doesn't work if the file doesn't exist
If Dir(FullPath) = "" Then Exit Function

sAns = Space(255)
lAns = GetShortPathName(FullPath, sAns, 255)
GetShortFileName = Left(sAns, lAns)
    
End Function

 

[localfiend]

I found the above code while doing a google search but was unable to get it to run, probably because I don't understand exactly how it works.

I created a test file, on the proper path and attempted to use the example in the comments with no luck. I kept getting compile errors on the sAns = Space(255) line (Can't find project or library).

 

[Ed in Aus]

ok to get this to work copy and paste into new module... don't change anything in it.

now get the file path you want and paste it into cell A1

in cell B1 copy and paste this in =GetShortFileName(A1)

this will give you your short path name.

 

[Ed in Aus]

ok try this pste this into the work book... then run "Get_Short_Name_DOS" this will allow you to browse to the file and it will show the short path in an input box which you can copy and paste this path.

Private Declare Function GetShortPathName Lib "kernel32" _
   Alias "GetShortPathNameA" (ByVal lpszLongPath As String, _
   ByVal lpszShortPath As String, ByVal cchBuffer As Long) _
   As Long
   
Public Function GetShortFileName(ByVal FullPath As String) _
  As String

'PURPOSE: Returns DOS File Name (8.3 Format) Give
'FullPath for long file name

'PARAMETERS: FullPath: Full Path of Original File

'RETURNS: 8.3 FileName, or "" if FullPath doesn't
'         exist or file fails for other reasons

'EXAMPLE:
'Debug.Print _
'  GetShortFileName("C:\My Documents\My Very Long File Name.doc")

'If file exists, will display C:\MYDOCU~1\MYVERY~1.DOC
'in the debug window

Dim lAns As Long
Dim sAns As String
Dim iLen As Integer
   
On Error Resume Next

'this function doesn't work if the file doesn't exist
If Dir(FullPath) = "" Then Exit Function

sAns = Space(255)
lAns = GetShortPathName(FullPath, sAns, 255)
GetShortFileName = Left(sAns, lAns)
    
End Function
Sub Get_Short_Name_DOS()
fileToOpen = Application _
    .GetOpenFilename("Master Sheet (*.XLS, *.XLS")
InputBox "Here is a copy and pastable for you", , (GetShortFileName(fileToOpen))

End Sub

 

[localfiend]

ok to get this to work copy and paste into new module... don't change anything in it.

now get the file path you want and paste it into cell A1

in cell B1 copy and paste this in =GetShortFileName(A1)

this will give you your short path name.

Yah, tried that as well. I have the code pasted into a clean module - which makes the formula/command available. Still gives me the compile error and directs me to the "Space" line.

Is there perhaps something I need to reference to get this to work? Using Excel 2007, Vista x64 Ultimate.

 

[Ed in Aus]

Try "Visual Basic for Applications", I don't have 07 at work, I do at home though...

 

[localfiend]

Just tried this in a brand new workbook (without referencing anything new) and it works perfectly fine. I must have something corrupted or confliction (don't know what it would be though). Grrr.

 

[Ed in Aus]

If your like me you would want to find out for sure, go to the sheet that was giving the error and search (in vba) for one of these terms sAns, Space I'm guessing you have Space used somewhere else... when searching make sure you have project selected this will search all modules.