File open with textbox entry.

[buzz71023]

I have the code below that opens up a file based on based ona number entered into a textbox in a userform (named frmLOAD).
It works as is but I really need for the user to be able totype what they need to open in the textbox and a directory box pop upprefilled with what was keyed in the textbox so the user is making sure theyare opening the correct file.

[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Private Sub CommandButton1_Click()[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Dim MyFileDir As String, FName As String, myXLFile As String[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]FirstTime As Range[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]FName = frmLOAD.Textbox1.text[/COLOR][/SIZE][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]MyFileDir = "P:\Production\Hours ReportingArchives\"[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]myXLFile = FName & "*"[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Workbooks.OpenText Filename:=MyFileDir & myXLFile[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]End Sub[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT]

 

[Steve_]

I have the code below that opens up a file based on based ona number entered into a textbox in a userform (named frmLOAD).
It works as is but I really need for the user to be able totype what they need to open in the textbox and a directory box pop upprefilled with what was keyed in the textbox so the user is making sure theyare opening the correct file.

Try this..

Sub File_Picker()
Dim MyFileDir As String, FName As String, myXLFile As String, FirstTime As Range
    MyFileDir = "P:\Production\Hours ReportingArchives\"
    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = False
        .InitialFileName = MyFileDir & frmLOAD.Textbox1.Text
        .Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.xlsb"
        .Show
        Verified_Filename = .SelectedItems(i)
    End With
    Workbooks.OpenText Filename:=Verified_Filename
End Sub

 

[buzz71023]

Thanks Steve for the reply.
I have two issues.

1. The file path directory is not automaticallyopening. It is starting at the desktop.
   
   
2. When the user clicks the cancel or “X” button toclose, I get an Run-Time Error
   
   1. Run-time error ‘5’: Invalid procedure call or argument
      

 

[Steve_]

Thanks Steve for the reply.
I have two issues.

1. The file path directory is not automaticallyopening. It is starting at the desktop.
2. When the user clicks the cancel or “X” button toclose, I get an Run-Time Error
   1. Run-time error ‘5’: Invalid procedure call or argument
      

Try this instead..

Sub File_Picker()
Dim MyFileDir As String, FName As String, myXLFile As String, FirstTime As Range
    MyFileDir = "P:\Production\Hours ReportingArchives"
    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = False
        .InitialFileName = MyFileDir & Userform1.Textbox1.Text
        .Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.xlsb"
        .Show
        If .SelectedItems.Count > 0 Then
            Verified_Filename = .SelectedItems(1)
        End If
    End With
    If Not IsEmpty(Verified_Filename) Then
        Workbooks.OpenText Filename:=Verified_Filename
    End If
End Sub

 

[buzz71023]

Is there a way to open or select the file without having theuser type the whole thing out?
For instance. If Ihave a file named “1234567 – 543210” couldthe code find it the file by just typing 1234567 in the textbox?