Function for transforming long file name in 8.3 format

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Claudio Pedrazzi - 10 Mar 2007 16:12 GMT

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

Reply to this Message

Dana DeLouis - 10 Mar 2007 16:56 GMT

> 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

Signature

HTH :>)
Dana DeLouis
Windows XP & Office 2007

> Hi everybody,
> I hope not being OT here. I have a long list of names of files in a
[quoted text clipped - 6 lines]
> Ciao from Italy
> Claudio

Reply to this Message

Claudio Pedrazzi - 11 Mar 2007 18:12 GMT

> > outputs a short 8.3 file name.
>
> 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

Reply to this Message

Gary''s Student - 11 Mar 2007 23:48 GMT

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.

Signature

Gary's Student
gsnu200710

> > > outputs a short 8.3 file name.
> >
[quoted text clipped - 20 lines]
> Ciao
> Claudio

Reply to this Message

Harlan Grove - 12 Mar 2007 16:19 GMT

Gary''s Student <GarysStud...@discussions.microsoft.com> wrote...

>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.

Reply to this Message

Harlan Grove - 11 Mar 2007 23:49 GMT

"Claudio Pedrazzi" <clavagerm...@yahoo.com> wrote...
...

>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.

Reply to this Message

Gary''s Student - 10 Mar 2007 17:02 GMT

give an example

Signature

Gary''s Student
gsnu200709

> Hi everybody,
> I hope not being OT here. I have a long list of names of files in a
[quoted text clipped - 6 lines]
> Ciao from Italy
> Claudio

Reply to this Message