Hi LeAnn,

Two ways to do this.

First, using only VBA:

Public Sub Get_FileName1()
Dim strFileName As String
strFileName = Application.GetOpenFilename("Excel files (*.xls;
*.csv),*.xls;*.csv", , "Open My File")
'REMOVE EXTENSION - ASSUMING 3 CHARACTERS AFTER "."
strFileName = Left(strFileName, Len(strFileName) - 4)
'THE PATH UPTO THE FILENAME SHOULD END IN "\" SO REMOVE ALL BEFORE THIS
strFileName = Right(strFileName, Len(strFileName) - InStrRev(strFileName,
"\"))
Call MsgBox(strFileName)
End Sub

This should always work. You could always use InStr to search for the "." to
make sure the filename has 3 characters after it, although you can, and
probably have, force the extension type when asking for the file.

A second method is to employ some scripting techniques. This way may be
slightly long winded, but you can access most of the file properties this way
(filename, path, extension, modified date, creation date, size etc etc). I've
only used the Filename property here.

Public Sub Get_Filename2()
Dim strFileName As String
Dim strFileNameAmended As String
Dim strComputer As String
Dim objWMIService As Object
Dim objFile As Object
Dim colFiles
strFileName = Application.GetOpenFilename("Excel files (*.xls;
*.csv),*.xls;*.csv", , "Open My File")
strFileNameAmended = Replace(strFileName, "\", "\\")
strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colFiles = objWMIService.ExecQuery _
("SELECT * FROM CIM_Datafile WHERE Name = '" & strFileNameAmended & "'")
For Each objFile In colFiles
MsgBox (objFile.Filename)
Next
End Sub

I hope this helps,

Sean.

--
(please remember to click yes if replies you receive are helpful to you)


"LeAnn" wrote:

Hi,

I have a follow up question to my earlier post - using Excel 2003. I am
able to set the default directory for the user to choose a single file from.
However, I need to extract the actual filename from the path (excluding the
extension).

Is there a property that does this? I haven't see anything so far. The
file name length may be variable so my attempt at using a combination of Left
and Right functions didn't always work.

Any ideas?

Thanks
LeAnn