Read Name of Selected File

[seekeagle]

The code below navigates to a specified location, opens the folder and allows the user to select an Excel file, which will be opened and the file name stored in the variable "File_nam".

'
******  Get Data from Checked out Laptop
    data_path = "d:\data\CAM_Tool"   
    
 ''''''**** commented out to use access
    If Application.Dialogs(xlDialogOpen).Show(data_path) Then
         ' continue running subroutine
    Else
        Exit Sub
    End If
    File_nam = ActiveWorkbook.Name       ' File Name with Raw Data

I am trying to do something similar but need help getting it working. I would like to give the user the opportunity to navigate to a location of his choice, and select an Access file. I do not want to open the file, but I need to record the name, which will subsequently be used in reading data from the file using ActiveX Data Objects without opening the file.

I know there is an commend called "GetSelectedFile", but cannot get it to work. How do I get the name of a selected file without opening it? Your help will be greatly appreciated.

Thanks,
George,
Fort Worth

 

[Anthony47]

The property "FileDialog" should do the job; the help on line list this example:

Sub UseFileDialogOpen()

    Dim lngCount As Long

    ' Open the file dialog
    With Application.FileDialog(msoFileDialogOpen)
        .AllowMultiSelect = True
        .Show

        ' Display paths of each file selected
        For lngCount = 1 To .SelectedItems.Count
            MsgBox .SelectedItems(lngCount)
        Next lngCount

    End With

End Sub

Il will open the file select dialogue box, allow the user to select any number of files, then the mesgbox shows each of the selected file names.

Bye.

 

[Norie]

George

What is this command 'GetSelectedFile'?

 

[seekeagle]

Norie... I saw the GetSelectedFile at http://www.java2s.com/Code/Java/Swing-JFC/Asimplefilechoosertoseewhatittakestomakeoneofthesework.htm . It seemed interesting. I could not figure out what they were doing, but it looked like it could possibly help with my task.

Anthony47............. thanks very much for taking the time and effort to offer the solution below. It looks like it will work for me. I will try it out.

George

 

[mikerickson]

Application.GetOpenFileName also has a MultiSelect argument.

 

[logicalman]

Hi
If i want to display the selected file path in a column how can i do that? thanks

 

[mikerickson]

This should do that:

Sub test()
    Dim uiFileNames As Variant
    
    uiFileNames = Application.GetOpenFilename(MultiSelect:=True)
    
    If TypeName(uiFileNames) = "Boolean" Then
        MsgBox "nothing selected"
    Else
        With Range("A1")
            .Resize(UBound(uiFileNames), 1) = Application.Transpose(uiFileNames)
        End With
    End If
End Sub