VBA Express : Excel - Workbook Name Minus the .xls

Excel

Workbook Name Minus the .xls

Ease of Use

Easy

Version tested with

2000, 2002

Submitted by:

Justinlabenne

Description:

Udf''s that remove the .xls on ths end of your excel workbook names

Discussion:

One way to use this function is as a normal worksheet function just like a formula. The FILENAMEIS2 function is best suited for this method. The other example is best used when called from a sub procedure, such as saving a copy of the workbook with the original filename and adding the date and time on the end. This makes it quicker and easier to just return the workbooks name for other uses.

Code:

instructions for use

			Option Explicit 
 
Function FILENAMEIS() As String 
     '   Active wb's filename - .xls
    Application.Volatile True 
     
    FILENAMEIS = Left$(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) 
End Function 
 
 
Function FILENAMEIS2() As String 
     '   From Zack Barresse
     '   Best option if using similiar to a formula in a cell
    Dim wbName As String 
    Application.Volatile True 
    wbName = Application.Caller.Parent.Parent.Name 
    FILENAMEIS2 = Left(wbName, Len(wbName) - 4) 
End Function

How to use:

Copy the above code.
Open any workbook.
Press (Alt + F11) to open the Visual Basic Editor (VBE).
From the Menu, choose Insert-Module.
Paste the code into the right-hand code window.
Close the VBE (Alt + Q)
Save the file if desired.

Test the code:

Use it just llike a formula or call it from a sub-routine
Either type it in a cell like: =FILENAMEIS2()
and press enter
or-
Use the Paste-Function dialog
It will be in the User-Defined category.
or-
Insert it into a sub-routine to be used within code
Examples are included in the workbook

Sample File:

BookName.zip 9.28KB

Approved by mdmackillop

This entry has been viewed 225 times.