Microsoft KB Archive/152971

= XL7: Macro to Determine the Short File Name of Active Workbook =

Article ID: Q152971

The information in this article applies to:


 * Microsoft Excel for Windows 95, versions 7.0, 7.0a
 * Microsoft Windows NT Workstation version 3.51

SUMMARY
Visual Basic for Applications does not have a function that will allow you to determine the short file name (MS-DOS name) of a Microsoft Excel workbook. However, this article provides a sample macro that uses a Windows API (application programming interface) call to display the short file name of the active workbook.

MORE INFORMATION
Microsoft provides examples of Visual Basic for Applications procedures for illustration only, without warranty either expressed or implied, including, but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures in this article are provided 'as is' and Microsoft does not guarantee that they can be used in all situations. While Microsoft support professionals can help explain the functionality of a particular macro, they will not modify these examples to provide added functionality, nor will they help you construct macros to meet your specific needs. If you have limited programming experience, you may want to consult one of the Microsoft Solution Providers. Solution Providers offer a wide range of fee-based services, including creating custom macros. For more information about Microsoft Solution Providers, call Microsoft Customer Information Service at (800) 426-9400.

Follow these steps to set up the sample macro code:

1. Open a new workbook.

2. Insert a new module sheet into the workbook.

3. Enter the following macro code on the new module sheet.

' The following statement must be include for the macro to work. Declare Function GetShortPathName Lib "KERNEL32.DLL" Alias " _     GetShortPathNameA" (ByVal lpctstrLongName As String, _      ByVal lptstrShortName As String, ByVal bufLen As Long) As Long

Sub Get_Short_Name 'Dimension variables. Dim LongStr As String Dim ShortStr As String Dim lStrLen As Long Dim lRet As Long

' LongStr is any long file name or variable pointing to a file. LongStr = ActiveWorkbook.Name ' To display the path with the file name comment the line above ' and remove the comment from the line below. 'LongStr = ActiveWorkbook.FullName

lRet = GetShortPathName(LongStr, ShortStr, lStrLen) ' This allows you to create a buffer the same length as     ' the returned string, saving you the trouble of having ' to strip the left of the buffer to get the string ShortStr = String(lRet, " ") lRet = GetShortPathName(LongStr, ShortStr, lRet)

' Display a message with the long and short file name. MsgBox "The name: " & LongStr & Chr(10) & _ "will be converted to: " & ShortStr End Sub 4. Click Save As on the File menu, type "This is a long filename" in the

"File name:" edit box, and then click Save. 5. Click Macro on the Tools menu, click Get_Short_Name, and then click Run.

Note that a message box appears with the following message:

The name: This is a long filename.xls will be converted to: THISIS~1.XLS For additional information about converting long file names, please see the following article in the Microsoft Knowledge Base:

ARTICLE-ID: Q142982 TITLE    : How Windows 95 Generates 8.3 File Names from Long File Names Additional query words: 7.00 7.00a

Keywords         : kbcode kbhowto Version          : 7.0 7.0a Platform         : WINDOWS