The Amsterdam Excel Summit

Results 1 to 6 of 6

Thread: File name pick- Excel VBA

  1. #1

    File name pick- Excel VBA

    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 = ""
    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.

  2. #2
    [VBA]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[/VBA]

  3. #3
    "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.

  4. #4
    I gave an example. Just use the fso method as you need it.

    [VBA]MsgBox fso.GetBaseName(CommonDialog1.Filename)[/VBA]

  5. #5
    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!

  6. #6
    This is another way of doing it:
    [vba]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
    MsgBox "Please select Excel File!"
    End If
    End Sub
    Using Excel 2010 in Home / 2007 in Office on Win XP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts