File open with textbox entry.

buzz71023

Active Member
Joined
May 29, 2011
Messages
280
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.

Code:
[/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_

Board Regular
Joined
Apr 28, 2010
Messages
167
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..

Code:
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
 
Last edited:

buzz71023

Active Member
Joined
May 29, 2011
Messages
280
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_

Board Regular
Joined
Apr 28, 2010
Messages
167
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..


Code:
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

Active Member
Joined
May 29, 2011
Messages
280
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?

 

Forum statistics

Threads
1,085,674
Messages
5,385,133
Members
401,934
Latest member
bob_says_hello

Some videos you may like

This Week's Hot Topics

Top