none
From button on worksheet open browse file dailog and return selected file name and path to worksheet cell or text box

    Question

  • I have a routine named BrowseFile that uses the file system object to open up a file picker dialog box.

    I also have a button on a worksheet that fires off the BrowseFile routine.

    Both code and button are working fine.

    However I am trying to figure out how to return the selected file name and path to a cell on the worksheet.

    I want the Browse File routine to be very generic and used for other buttons on other sheets as well.

    I tried turning the BrowseFile routine into a function, but I don't know how to assign the returned value to a cell or text box

    Friday, March 9, 2018 3:49 AM

Answers

  • Hi James,

    I suppose what you want to achieve is:
      (1) show a dialog for selecting a file
      (2) (when a file is selected)
           separate the file name into file path and file name

    I made a sample, using [msoFileDialogOpen].
        

    [sample code]
    Private Sub CommandButton1_Click()
        Dim mySFO As Object
        Set mySFO = CreateObject("Scripting.FileSystemObject")
        Dim targetFile As String
        ' --- FileDialog
        With Application.FileDialog(msoFileDialogOpen)
            ' --- initial folder
            .InitialFileName = "C:\temp"
            ' --- filter: file extension
            .Filters.Add "All files (*.*)", "*.*; *.*", 1
            ' --- check: if a valid button is clicked
            If (.Show = -1) Then
                targetFile = .SelectedItems(1)
                Range("A2").Value = mySFO.GetParentFolderName(targetFile)
                Range("B2").Value = mySFO.GetFileName(targetFile)
            End If
        End With
    End Sub
    

    Regards,

    Ashidacchi >> http://hokusosha.com/

    • Proposed as answer by Ashidacchi Tuesday, March 13, 2018 4:35 PM
    • Marked as answer by James N San Wednesday, March 14, 2018 11:52 AM
    Saturday, March 10, 2018 3:16 AM

All replies

  • You didn't post the code you have but there are a number of different ways to assign a value to a Cell in an Excel Worksheet:

    https://www.quora.com/How-can-you-change-a-cell-value-using-VBA-in-Microsoft-Excel

    To make it generic you could use ActiveSheet instead of the Worksheet name reference.

    http://analystcave.com/excel-vba-worksheets-tutorial-vba-activesheet-vs-worksheets/


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, March 9, 2018 2:39 PM
  • Hi James,

    I suppose what you want to achieve is:
      (1) show a dialog for selecting a file
      (2) (when a file is selected)
           separate the file name into file path and file name

    I made a sample, using [msoFileDialogOpen].
        

    [sample code]
    Private Sub CommandButton1_Click()
        Dim mySFO As Object
        Set mySFO = CreateObject("Scripting.FileSystemObject")
        Dim targetFile As String
        ' --- FileDialog
        With Application.FileDialog(msoFileDialogOpen)
            ' --- initial folder
            .InitialFileName = "C:\temp"
            ' --- filter: file extension
            .Filters.Add "All files (*.*)", "*.*; *.*", 1
            ' --- check: if a valid button is clicked
            If (.Show = -1) Then
                targetFile = .SelectedItems(1)
                Range("A2").Value = mySFO.GetParentFolderName(targetFile)
                Range("B2").Value = mySFO.GetFileName(targetFile)
            End If
        End With
    End Sub
    

    Regards,

    Ashidacchi >> http://hokusosha.com/

    • Proposed as answer by Ashidacchi Tuesday, March 13, 2018 4:35 PM
    • Marked as answer by James N San Wednesday, March 14, 2018 11:52 AM
    Saturday, March 10, 2018 3:16 AM
  • Works perfectly! Thanks.
    Wednesday, March 14, 2018 11:52 AM