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
    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 
    Formatting tags added by mark007

  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.

    MsgBox fso.GetBaseName(CommonDialog1.Filename) 
    Formatting tags added by mark007

  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:
    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 
    Formatting tags added by mark007
    Using Excel 2016 in Home / 2010 in Office

Posting Permissions

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