Function for transforming long file name in 8.3 format

[Claudio Pedrazzi]

Hi everybody,
I hope not being OT here. I have a long list of names of files in a
column (obtained with JDirPrinter utility). I would like to sort the
column in ascending 8.3 DOS format name.
That is, I need to create or use a function that takes as input a long
file name as string, and outputs a short 8.3 file name.
Is it possible?
Thanks a lot
Ciao from Italy
Claudio

 

[Dana DeLouis]

outputs a short 8.3 file name.

Hi. Here's one way:

Function ShortName(sFilePath As String) As String
'// VBA Library Reference: Microsoft Scripting Runtime
Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
ShortName = fso.GetFile(sFilePath).ShortName
End Function

Sub TestIt()
Dim n As String
n = "C:\ALongFileNameThatExists.txt"
Debug.Print ShortName(n)
End Sub

Returns:
ALONGF~1.TXT

Note: If you want Path, then:
fso.GetFile(sFilePath).ShortPath

 

[Guest]

give an example

 

[Claudio Pedrazzi]

Hi. Here's one way:

Dana, thanks. This is exactly what I want. Only, please do not
laught at me , I never used a script in Excel, asking of a function
I was thinking about something like MEAN(), MAX(), MIN() and so on.

Could you be so kind to point me to a simple step-by-step guide how to
"define" for myself such a fucntion with a program? I have already
tried browsing the help, but I am not sure: is this a Microsoft Scipt
or a Visual Basic Script, or something else?

For Gary's Student asking an example: go under DOS C:> prompt and try
to type:

DIR /X

I want the "short" form that comes out before the long name of each
file.

Thanks everybody
Ciao
Claudio

 

[Guest]

As you suggested, I got the DOS prompt and:

03/11/2007 06:31 PM <DIR> .
03/11/2007 06:31 PM <DIR> ..
03/11/2007 08:41 AM 13,824 BAAD4D~1.XLS b
a.xls
03/11/2007 08:41 AM 13,824 BA5C35~1.XLS b a.xls
03/11/2007 03:24 PM 14,336 BADE5D~1.XLS
B......................................a.xls
03/11/2007 06:30 PM 10,752 CHRONO~1.DOC chronosynclastic.doc
03/11/2007 06:26 PM <DIR> NEWBRI~1 New Briefcase
03/11/2007 06:30 PM 10,752 NEWMIC~1.DOC New Microsoft Word
Document.doc
5 File(s) 63,488 bytes
3 Dir(s) 57,299,603,456 bytes free

The only one that makes sense is:
chronocynclastic.doc

It looks like:
1. remove embedded blanks
2. convert to uppercase
3. keep 6 characters
4. append that name with ~1

The following formula will work on many (?) cases, but not all:

In B1:
=UPPER(SUBSTITUTE(A1," ",""))

In C1:
=LEFT(B1,6) & "~1" & RIGHT(B1,4)


If A1 has:
chronosynclastic.doc

C1 will display:
CHRONO~1.DOC




If this post is useless, just ignore it.

 

[Harlan Grove]

DIR /X

I want the "short" form that comes out before the long name of each
file.

You could do this with a batch file if you're running an NT-ish
version (one with CMD.EXE).


@echo off & setlocal enableextensions enabledelayedexpansion

if exist "%0.tmp" del "%0.tmp"

for /F "delims=" %%a in ('dir /a-d /x ^| findstr /b "../"') do (
set a=%%a
if "!a:~39,12!" == " " (
set b=!a:~52! $
echo !b:~0,13!!a:~52!>> "%0.tmp"
) else (
echo !a:~39!>> "%0.tmp"
)
)

sort < "%0.tmp" > dir.sorted.txt

del "%0.tmp"


This creates a file named dir.sorted.txt in the directory from which
you run the batch file, and the file contains the 8.3 short filename
in the first 12 characters, then a space character, then the long
filename. This could be used in place of your JDirPrinter utility.

 

[Harlan Grove]

As you suggested, I got the DOS prompt and: ....
[modified]
03/11/2007 08:41 AM 13,824 BAAD4D~1.XLS b a.xls
03/11/2007 08:41 AM 13,824 BA5C35~1.XLS b a.xls
03/11/2007 03:24 PM 14,336 BADE5D~1.XLS B....a.xls ....
The only one that makes sense is:
chronocynclastic.doc

You don't understand the algorithm.

It looks like:
1. remove embedded blanks
True.

2. convert to uppercase

True, but irrelevant, because case doesn't matter in long filenames.
Opening "c:\foo\a b c.xls" or "C:\FOO\A B C.XLS" results in the same
file being opened.

3. keep 6 characters
4. append that name with ~1

....

Not always. It depends on how many long filenames there are with the
same first 6 characters. From my own test directory,

KLD28E~1.BAT kludge successor char.5.bat
KLD67E~1.BAT kludge successor char.2.bat
KLDA7E~1.BAT kludge successor char.3.bat
KLDE7E~1.BAT kludge successor char.4.bat

Try to find a pattern. May help to mention that short filenames
depends on what other short filenames are already in use when they're
created, so the short filenames depend on the order of file creation,
and that's effectively random.

There's no RELIABLE substitute for getting the short filenames from
Windows.