Short Path files (3 Viewers)

[AndyG]

Hi All,

Have searched and not been able to find a solution but, - Is there code I can place in an update query to convert a column of long filenames to shortfile names?

My users have many folders and tend to use over-descriptive file names which means there are cases where follow hyperlink fails.

Any help would be gratefully appreciated.

Many thanks

Andy

 

[Jon]

Welcome to Access World! We're so happy to have you join us as a member of our community. As the most active Microsoft Access discussion forum on the internet, with posts dating back more than 20 years, we have a wealth of knowledge and experience to share with you.

We're a friendly and helpful community, so don't hesitate to ask any questions you have or share your own experiences with Access. We're here to support you and help you get the most out of this powerful database program.

To get started, we recommend reading the post linked below. It contains important information for all new users of the forum:

https://www.access-programmers.co.uk/forums/threads/new-member-read-me-first.223250/

We hope you have a great time participating in the discussion and learning from other Access enthusiasts. We look forward to having you around!

 

[Pat Hartman]

Welcome @AndyG . You joined quite a while ago but haven't been active in a year. I moved your question from the introduction forum which isn't used to post questions so no one will find it there.

To "renmame" the files, you need to "move" them to a new folder with the better name. You can use that using FSO (File System Object). You can do this using a VBA code loop that reads one folder at a time and then moves all the files to the new folder and when the original folder is empty, deletes the original folder.

If you need some help with the code, I can do it later or someone might provide it immediately. I'm about to start a conference call and can't really code while I'm trying to talk

 

[Mark_]

My users have many folders and tend to use over-descriptive file names

For future reference, will you also need something to stop them from doing this? Or will this be handled by having them use better names?

For scope, difference is "Do we do this ONCE so we are not worried if it takes a while" VS "We need to do this often, so if it takes an hour that is a problem".

 

[Pat Hartman]

You can always add code to check the length of a path or file name as part of the save process and at least complain if they are too long.

 

[Gasman]

Examples would be nice?

 

[Isaac]

Use the GetShortPathName method to figure out the abbreviated file path, if any
I'm hoping this method is exposed in File System Object but not sure at the moment

edit - it's not, but see below. ShortPath is a property of an FSO file

 

[Isaac]

Sub foo()
Dim fso As Object, fsofile As Object, strShortName As String
Dim fsofolder As Object

Set fso = CreateObject("scripting.filesystemobject")
Set fsofolder = fso.getfolder("C:\Users\Isaac \Desktop\Bills")

For Each fsofile In fsofolder.Files
    MsgBox fsofile.shortpath
Next fsofile

End Sub

Now all you need to do is loop through your table records, and for each file, use that method to determine its ShortPath.
Store that in a table too, and use that in follow hyperlink, if it works better (I've never tried it)

 

[Gasman]

How long is considered long?

foo("F:\Temp\Served With")
Long - F:\Temp\Served With\Quayle_G Served With Report.pdf short - F:\Temp\Served With\Quayle_G Served With Report.pdf
Long - F:\Temp\Served With\Very long name for a file as Quayle_G Served With Report - Copy.pdf short - F:\Temp\Served With\Very long name for a file as Quayle_G Served With Report - Copy.pdf


foo("F:\Temp\Very Long name for a folder in a directory in Temp on C drive")

Long - F:\Temp\Very Long name for a folder in a directory in Temp on C drive\Quayle_G Served With Report.pdf short - F:\Temp\Very Long name for a folder in a directory in Temp on C drive\Quayle_G Served With Report.pdf
Long - F:\Temp\Very Long name for a folder in a directory in Temp on C drive\Very long name for a file as Quayle_G Served With Report - Copy.pdf short - F:\Temp\Very Long name for a folder in a directory in Temp on C drive\Very long name for a file as Quayle_G Served With Report - Copy.pdf

 

[cheekybuddha]

Here's another version, but I would go with Isaac's - it's cleaner and shorter:

Function ShortPath(LongPath As String, Optional DeleteBatFile As Boolean) As String

  Const CMD       As String = "@ECHO OFF" & vbNewLine & "echo %~s1"
  Const BAT       As String = "shortpath.cmd"
 
  Dim cmdPath     As String
  Dim iFile       As Integer
  Dim ret         As Variant
  Dim tmp         As String
  Dim i           As Integer
 
  cmdPath = CurrentProject.path & "\" & BAT
  If Len(Dir(cmdPath)) = 0 Then
    iFile = FreeFile
    Open cmdPath For Output As #iFile
    Write #iFile, CMD
    Close #iFile
  End If
    
  With CreateObject("WScript.Shell")
    tmp = .Exec(BAT & " " & DQ & LongPath & DQ).StdOut.ReadAll
  End With
  ret = Split(tmp, vbNewLine)
  If UBound(ret) >= 0 Then
    For i = UBound(ret) To 0 Step -1
      If Len(ret(i)) Then
        ShortPath = ret(i)
        Exit For
      End If
    Next i
  End If
  If DeleteBatFile Then
    Kill cmdPath
  End If
 
End Function

 

[The_Doc_Man]

How long is considered long?

There is a specific consideration for long names. It is the old 8-3 requirement - 8 characters in the file name, 3 characters in the file type. As it happens, that same limit would apply (individually) to each folder name in a path having no more than 8 characters. However, the total path can be quite long. I think a complete file spec (drive letter, path, name, type) is limited to 255 bytes.

 

[Isaac]

I'm not sure it's a matter of how long or how short - Rather, it is an official Windows attribute, the "short file path" of any given file

 

[Cotswold]

Why not use the Tilde ~ to shorten directory/folder names to eight characters?

A couple of examples using the DIR command which should be self explanatory. Unfortunately there is little on the internet about using the Tilde. So you may need to do a few tests to get what you want.

EXAMPLE 1
ACTUAL FOLDER SET - NOTE! as there are spaces in folder names you need the speech marks as a prefix and suffix when using full names
DIR "C:\AllAccess\AccessBookA2010InsideOut\Sample Files\Access 2010 Inside Out\Documents\XML\XMLDepartments.xml"

ADJUSTED but using Tilde and max length of 8 for folder names
DIR C:\Allacc~1\Access~2\Sample~1\Access~1\Docume~1\XML\XMLDepertments.xml


EXAMPLE 2
ACTUAL FOLDER SET - NOTE! No spaces in this path
DIR C:\AllAccess\Access2002Getz\ChapterProjects\ch02\ch02.mdb

ADJUSTED but using Tilde and max length of 8
DIR C:\allacc~1\Access~3\chapte~1\ch02\ch02.mdb

NOTE! If you have two, or more folders inside a folder which have the same first six alpha/numeric names you need to advance from ~1 to ~2 etc. But I'm sure you can work it out. As far as I know Linux uses the Tilde ~ but LS instead of DIR as in MS-DOS, so you may find more examples there. In the first example there are more than one subfolders to C:\AllAccess starting with Access, hence the ~2

If you take the full directory path you should be able to string handle it from actual long names down to eight as shown to do what you want. But in some instances you may need to do a quick search for duplicates for any first six identical in a folder. An interesting exercise.

 

[cheekybuddha]

Why not use the Tilde ~ to shorten directory/folder names to eight characters?

Both Post#8 and Post#10 have methods that will convert full paths to short paths (the old DOS 8.3 naming convention)

 

[arnelgp]

there are cases where follow hyperlink fails.

you can leave the long filename as is.
messing with it might render the path useless.
instead try using a custom "followHyperlink"

Public Sub subShell(ByVal strPath As String)
'Shell "explorer.exe " & strpath
CreateObject("Shell.Application").NameSpace(0).ParseName(strPath).InvokeVerb "Open"
End Sub

 

[AndyG]

Sub foo()
Dim fso As Object, fsofile As Object, strShortName As String
Dim fsofolder As Object

Set fso = CreateObject("scripting.filesystemobject")
Set fsofolder = fso.getfolder("C:\Users\Isaac \Desktop\Bills")

For Each fsofile In fsofolder.Files
    MsgBox fsofile.shortpath
Next fsofile

End Sub

Now all you need to do is loop through your table records, and for each file, use that method to determine its ShortPath.
Store that in a table too, and use that in follow hyperlink, if it works better (I've never tried it)

the above works from the c:\ but not from g:\ (google grive) it just messages the orginal file path?