Results 1 to 5 of 5

Thread: File open with textbox entry.
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    May 2011
    Posts
    277
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default File open with textbox entry.

    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:
    Private Sub CommandButton1_Click()
    Dim MyFileDir As String, FName As String, myXLFile As String
    FirstTime As Range
    
    
    FName = frmLOAD.Textbox1.text
    
    MyFileDir = "P:\Production\Hours ReportingArchives\"
    myXLFile = FName & "*"
    
    Workbooks.OpenText Filename:=MyFileDir & myXLFile
    End Sub

  2. #2
    Board Regular
    Join Date
    Apr 2010
    Posts
    167
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: File open with textbox entry.

    Quote Originally Posted by buzz71023 View Post
    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 by Steve_; Aug 2nd, 2019 at 12:35 PM.

  3. #3
    Board Regular
    Join Date
    May 2011
    Posts
    277
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: File open with textbox entry.

    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


  4. #4
    Board Regular
    Join Date
    Apr 2010
    Posts
    167
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: File open with textbox entry.

    Quote Originally Posted by buzz71023 View Post
    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

  5. #5
    Board Regular
    Join Date
    May 2011
    Posts
    277
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: File open with textbox entry.

    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?


Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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