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

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

Sub test()
Dim PName As String, FName As String
Dim i As Long, Dot As Long

PName = "C:\RootFolder\SubFolder\MyBook.xls" 'full path
i = InStrRev(PName, "\") + 1 'position of last "\"
Dot = InStrRev(PName, ".")
FName = Mid(PName, i, Dot - i)
End Sub

Mike F
"LeAnn" wrote in message
...
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

Thank you Sean & Mike. I will try it out - been working on something else
lately. I'll let you know what works.

"Mike Fogleman" wrote:

Sub test()
Dim PName As String, FName As String
Dim i As Long, Dot As Long

PName = "C:\RootFolder\SubFolder\MyBook.xls" 'full path
i = InStrRev(PName, "\") + 1 'position of last "\"
Dot = InStrRev(PName, ".")
FName = Mid(PName, i, Dot - i)
End Sub

Mike F
"LeAnn" wrote in message
...
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

Very nice - worked perfectly. Didn't know about InStrRev - was thinking
there should be something like that.


"Mike Fogleman" wrote:

Sub test()
Dim PName As String, FName As String
Dim i As Long, Dot As Long

PName = "C:\RootFolder\SubFolder\MyBook.xls" 'full path
i = InStrRev(PName, "\") + 1 'position of last "\"
Dot = InStrRev(PName, ".")
FName = Mid(PName, i, Dot - i)
End Sub

Mike F
"LeAnn" wrote in message
...
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

Thanks Sean,

I think your scripting techniques will be very helpful to me in other
applications.

"SeanC UK" wrote:

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

Glad to help. Notice that this works on all extension lengths.

Mike F
"LeAnn" wrote in message
...
Very nice - worked perfectly. Didn't know about InStrRev - was thinking
there should be something like that.


"Mike Fogleman" wrote:

Sub test()
Dim PName As String, FName As String
Dim i As Long, Dot As Long

PName = "C:\RootFolder\SubFolder\MyBook.xls" 'full path
i = InStrRev(PName, "\") + 1 'position of last "\"
Dot = InStrRev(PName, ".")
FName = Mid(PName, i, Dot - i)
End Sub

Mike F
"LeAnn" wrote in message
...
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