PDA

View Full Version : File name pick- Excel VBA



sarah2010
05-19-2011, 08:27 AM
Hello Guys,
I've a piece of code to choose an excel file using "common dialog".
Here it is.
========
CommonDialog1.Filter = "Excel Files (*.XLS)|*.xls"
CommonDialog1.Filename = ""
CommonDialog1.ShowOpen
If CommonDialog1.Filename <> "" Then
master_file.Value = CommonDialog1.Filename
Open CommonDialog1.Filename For Input As #1
Workbooks.Open CommonDialog1.Filename
Close #1
End If
========
The variable "commondialog1.filename" returns the file name with the complete path, like, "D:\TEST\xyz.xls".
Is there a way to get only the file name? like "xyz". I need this info for my upcoming function.
Appreciate your help.
Thanks!

Kenneth Hobs
05-19-2011, 10:32 AM
Sub test()
Dim s As String, fso As Object
s = ThisWorkbook.Name
Set fso = CreateObject("Scripting.FileSystemObject")
MsgBox fso.GetBaseName(s)
Set fso = Nothing
End Sub

sarah2010
05-19-2011, 12:28 PM
"s = ThisWorkbook.Name" returns the name of the workbook where the VB code is residing. Eventhough the xyz.xls is opened by "Workbooks.Open CommonDialog1.Filename", it doesn't catch this. Let me know, if I missed something.

Kenneth Hobs
05-19-2011, 03:07 PM
I gave an example. Just use the fso method as you need it.

MsgBox fso.GetBaseName(CommonDialog1.Filename)

sarah2010
05-20-2011, 07:08 AM
Thanks Ken,
I made it work by replacing "ThisWorkbook.Name" to "ActiveWorkbook.Name" & used the FSO method to get rid of the extension. Thanks for your time & help!

shrivallabha
05-21-2011, 05:37 AM
This is another way of doing it:
Public Sub GetFileName()
Dim sName As String
Dim vName As Variant
sName = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
If sName <> "False" Then
vName = Split(sName, "\")
sName = vName(UBound(vName))
MsgBox sName
Else
MsgBox "Please select Excel File!"
End If
End Sub