+ Reply to Thread
Results 1 to 5 of 5

Select Text Files from Combobox

  1. #1
    D.Parker
    Guest

    Select Text Files from Combobox

    Hello again!

    Is there a way to read in multiple files of "almost" similar into a combobox
    for the user to select the file they want? The filenames first few
    characters will be the same but the following characters will be dymanic.
    For example:

    filenames: SN1033_XXX_67489564926.txt
    SN1033_XXX_00909890879.txt
    SN1033_XXX_78987907893.txt
    SN1008_XXX_78290574238.txt
    SN1008_XXX_54545454544.txt
    and so on....

    So I would like to read all the text filenames beginning with "SN1033". Can
    I search a given folder where the files exist for any file beginning with
    SN1033 and take all of those file and do a .AddItem for each file to a
    UserForm, combobox form, where all the SN1033 files will be listed in the
    drop down when the downarrow on the combobox is selected? Thus, the user can
    select the file they want given the dynamic variable.

    Once that file is selected and becomes ComboBox1.Value, I want to set my
    variable TextPath to TextPath = ComboxBox1.Value instead of
    "C:\Temp\SN1033_XXX_67489564926.txt". Is that possible?

    My current code ==========================

    Sub ImportTextStringSN1033()

    'Variable Declarations

    Dim TextPath As String

    Dim NCData As Variant

    '===============================================

    TextPath = "C:\Temp\SN1033_XXX_67489564926.txt"

    On Error GoTo ErrorMsg
    Open TextPath For Input As #1 'open text file for SN1033


    Do While Not EOF(1) 'go while not end of text file
    Line Input #1, NCData

    If EOF(1) Then
    Application.Range("AP9") = NCData

    Range("AP9").Select

    'On Error GoTo ErrorMsg1004
    'recorded macro for text to columns
    Selection.TextToColumns _
    Destination:=Range("AP9"), _
    DataType:=xlDelimited, _
    Comma:=True, _
    FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1),
    Array(4, 1)), _
    TrailingMinusNumbers:=True

    End If
    Loop
    Close #1


    Exit Sub
    ErrorMsg: 'Error handling routines
    Close #1
    Range("AP9").Select
    Selection.ClearContents
    MsgBox "Error " & Err & ": " & Error(Err)

    End Sub

    =========================================

    Thank you all again for any and all assistance!!!!

    Kind regards,

    D.Parker

  2. #2
    JMB
    Guest

    RE: Select Text Files from Combobox

    Yes. One way of doing that is with FileSearch. Following is an example for
    a combobox I had set up on Sheet1. Of course, you could use variables for
    some of the items like .LookIn and FileName.

    One caveat - if you assign the code to load the filenames into the combobox
    to the DropButton_Click event for the combobox, I've had problems with the
    combobox not retaining the value you select (almost as if the event runs
    again when you select an item from the drop down menu). I've gotten around
    this by saving the combox value right before clearing the combobox, then
    assigning that value back to the combobox right after I cleared it.

    Sheet1.ComboBox1.Clear

    With Application.FileSearch
    .NewSearch
    .LookIn = "C:\Temp"
    .Filename = "SN1033" & "*.txt"
    .SearchSubFolders = False
    .Execute
    For i = 1 To .FoundFiles.Count Step 1
    Sheet1.ComboBox1.AddItem .FoundFiles(i)
    Next i
    End With


    "D.Parker" wrote:

    > Hello again!
    >
    > Is there a way to read in multiple files of "almost" similar into a combobox
    > for the user to select the file they want? The filenames first few
    > characters will be the same but the following characters will be dymanic.
    > For example:
    >
    > filenames: SN1033_XXX_67489564926.txt
    > SN1033_XXX_00909890879.txt
    > SN1033_XXX_78987907893.txt
    > SN1008_XXX_78290574238.txt
    > SN1008_XXX_54545454544.txt
    > and so on....
    >
    > So I would like to read all the text filenames beginning with "SN1033". Can
    > I search a given folder where the files exist for any file beginning with
    > SN1033 and take all of those file and do a .AddItem for each file to a
    > UserForm, combobox form, where all the SN1033 files will be listed in the
    > drop down when the downarrow on the combobox is selected? Thus, the user can
    > select the file they want given the dynamic variable.
    >
    > Once that file is selected and becomes ComboBox1.Value, I want to set my
    > variable TextPath to TextPath = ComboxBox1.Value instead of
    > "C:\Temp\SN1033_XXX_67489564926.txt". Is that possible?
    >
    > My current code ==========================
    >
    > Sub ImportTextStringSN1033()
    >
    > 'Variable Declarations
    >
    > Dim TextPath As String
    >
    > Dim NCData As Variant
    >
    > '===============================================
    >
    > TextPath = "C:\Temp\SN1033_XXX_67489564926.txt"
    >
    > On Error GoTo ErrorMsg
    > Open TextPath For Input As #1 'open text file for SN1033
    >
    >
    > Do While Not EOF(1) 'go while not end of text file
    > Line Input #1, NCData
    >
    > If EOF(1) Then
    > Application.Range("AP9") = NCData
    >
    > Range("AP9").Select
    >
    > 'On Error GoTo ErrorMsg1004
    > 'recorded macro for text to columns
    > Selection.TextToColumns _
    > Destination:=Range("AP9"), _
    > DataType:=xlDelimited, _
    > Comma:=True, _
    > FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1),
    > Array(4, 1)), _
    > TrailingMinusNumbers:=True
    >
    > End If
    > Loop
    > Close #1
    >
    >
    > Exit Sub
    > ErrorMsg: 'Error handling routines
    > Close #1
    > Range("AP9").Select
    > Selection.ClearContents
    > MsgBox "Error " & Err & ": " & Error(Err)
    >
    > End Sub
    >
    > =========================================
    >
    > Thank you all again for any and all assistance!!!!
    >
    > Kind regards,
    >
    > D.Parker


  3. #3
    D.Parker
    Guest

    RE: Select Text Files from Combobox

    Is there a way to put this into a UserForm vs directly on the sheet? I want
    the user to click a button on the sheet and the execution of the previously
    mentioned would transpire. Thank you.

    Kind regards,

    D.Parker

    "JMB" wrote:

    > Yes. One way of doing that is with FileSearch. Following is an example for
    > a combobox I had set up on Sheet1. Of course, you could use variables for
    > some of the items like .LookIn and FileName.
    >
    > One caveat - if you assign the code to load the filenames into the combobox
    > to the DropButton_Click event for the combobox, I've had problems with the
    > combobox not retaining the value you select (almost as if the event runs
    > again when you select an item from the drop down menu). I've gotten around
    > this by saving the combox value right before clearing the combobox, then
    > assigning that value back to the combobox right after I cleared it.
    >
    > Sheet1.ComboBox1.Clear
    >
    > With Application.FileSearch
    > .NewSearch
    > .LookIn = "C:\Temp"
    > .Filename = "SN1033" & "*.txt"
    > .SearchSubFolders = False
    > .Execute
    > For i = 1 To .FoundFiles.Count Step 1
    > Sheet1.ComboBox1.AddItem .FoundFiles(i)
    > Next i
    > End With
    >
    >
    > "D.Parker" wrote:
    >
    > > Hello again!
    > >
    > > Is there a way to read in multiple files of "almost" similar into a combobox
    > > for the user to select the file they want? The filenames first few
    > > characters will be the same but the following characters will be dymanic.
    > > For example:
    > >
    > > filenames: SN1033_XXX_67489564926.txt
    > > SN1033_XXX_00909890879.txt
    > > SN1033_XXX_78987907893.txt
    > > SN1008_XXX_78290574238.txt
    > > SN1008_XXX_54545454544.txt
    > > and so on....
    > >
    > > So I would like to read all the text filenames beginning with "SN1033". Can
    > > I search a given folder where the files exist for any file beginning with
    > > SN1033 and take all of those file and do a .AddItem for each file to a
    > > UserForm, combobox form, where all the SN1033 files will be listed in the
    > > drop down when the downarrow on the combobox is selected? Thus, the user can
    > > select the file they want given the dynamic variable.
    > >
    > > Once that file is selected and becomes ComboBox1.Value, I want to set my
    > > variable TextPath to TextPath = ComboxBox1.Value instead of
    > > "C:\Temp\SN1033_XXX_67489564926.txt". Is that possible?
    > >
    > > My current code ==========================
    > >
    > > Sub ImportTextStringSN1033()
    > >
    > > 'Variable Declarations
    > >
    > > Dim TextPath As String
    > >
    > > Dim NCData As Variant
    > >
    > > '===============================================
    > >
    > > TextPath = "C:\Temp\SN1033_XXX_67489564926.txt"
    > >
    > > On Error GoTo ErrorMsg
    > > Open TextPath For Input As #1 'open text file for SN1033
    > >
    > >
    > > Do While Not EOF(1) 'go while not end of text file
    > > Line Input #1, NCData
    > >
    > > If EOF(1) Then
    > > Application.Range("AP9") = NCData
    > >
    > > Range("AP9").Select
    > >
    > > 'On Error GoTo ErrorMsg1004
    > > 'recorded macro for text to columns
    > > Selection.TextToColumns _
    > > Destination:=Range("AP9"), _
    > > DataType:=xlDelimited, _
    > > Comma:=True, _
    > > FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1),
    > > Array(4, 1)), _
    > > TrailingMinusNumbers:=True
    > >
    > > End If
    > > Loop
    > > Close #1
    > >
    > >
    > > Exit Sub
    > > ErrorMsg: 'Error handling routines
    > > Close #1
    > > Range("AP9").Select
    > > Selection.ClearContents
    > > MsgBox "Error " & Err & ": " & Error(Err)
    > >
    > > End Sub
    > >
    > > =========================================
    > >
    > > Thank you all again for any and all assistance!!!!
    > >
    > > Kind regards,
    > >
    > > D.Parker


  4. #4
    JMB
    Guest

    RE: Select Text Files from Combobox

    Go into visual basic (Alt F11). Click Insert/Userform. If it is the first
    userform it will likely be called UserForm1. Click the combobox control on
    the toolbox. Draw your combobox on the userform.

    Click the View Code button above the Project Explorer Window and paste this
    into the code window (modify as needed).

    Private Sub UserForm_Activate()
    Me.ComboBox1.Clear
    With Application.FileSearch
    .NewSearch
    .LookIn = "C:\Temp"
    .Filename = "SN1033" & "*.txt"
    .SearchSubFolders = False
    .Execute
    For i = 1 To .FoundFiles.Count Step 1
    Me.ComboBox1.AddItem .FoundFiles(i)
    Next i
    End With

    End Sub


    Now you will need to add code to your project to show the userform. Could
    be a button on one of your Excel worksheets that links to a macro w/this line
    in it:

    UserForm1.Show

    You will also need to add a command button to the Userform to execute
    whatever code you need to run after the user selects a file. Add command
    button the same way you added the combobox, then double click on the button
    and you'll get

    Private Sub CommandButton1_Click()

    End Sub


    Put your code into the body of this event handler. To refer to the combobox
    on the userform you could use

    UserForm1.ComboBox1.Value

    Or, the preferred way

    Me.Combobox1.Value


    When you are viewing the userform, you can show (and change) the properties
    of the various controls by clicking View/Properties Window.


    "D.Parker" wrote:

    > Is there a way to put this into a UserForm vs directly on the sheet? I want
    > the user to click a button on the sheet and the execution of the previously
    > mentioned would transpire. Thank you.
    >
    > Kind regards,
    >
    > D.Parker
    >
    > "JMB" wrote:
    >
    > > Yes. One way of doing that is with FileSearch. Following is an example for
    > > a combobox I had set up on Sheet1. Of course, you could use variables for
    > > some of the items like .LookIn and FileName.
    > >
    > > One caveat - if you assign the code to load the filenames into the combobox
    > > to the DropButton_Click event for the combobox, I've had problems with the
    > > combobox not retaining the value you select (almost as if the event runs
    > > again when you select an item from the drop down menu). I've gotten around
    > > this by saving the combox value right before clearing the combobox, then
    > > assigning that value back to the combobox right after I cleared it.
    > >
    > > Sheet1.ComboBox1.Clear
    > >
    > > With Application.FileSearch
    > > .NewSearch
    > > .LookIn = "C:\Temp"
    > > .Filename = "SN1033" & "*.txt"
    > > .SearchSubFolders = False
    > > .Execute
    > > For i = 1 To .FoundFiles.Count Step 1
    > > Sheet1.ComboBox1.AddItem .FoundFiles(i)
    > > Next i
    > > End With
    > >
    > >
    > > "D.Parker" wrote:
    > >
    > > > Hello again!
    > > >
    > > > Is there a way to read in multiple files of "almost" similar into a combobox
    > > > for the user to select the file they want? The filenames first few
    > > > characters will be the same but the following characters will be dymanic.
    > > > For example:
    > > >
    > > > filenames: SN1033_XXX_67489564926.txt
    > > > SN1033_XXX_00909890879.txt
    > > > SN1033_XXX_78987907893.txt
    > > > SN1008_XXX_78290574238.txt
    > > > SN1008_XXX_54545454544.txt
    > > > and so on....
    > > >
    > > > So I would like to read all the text filenames beginning with "SN1033". Can
    > > > I search a given folder where the files exist for any file beginning with
    > > > SN1033 and take all of those file and do a .AddItem for each file to a
    > > > UserForm, combobox form, where all the SN1033 files will be listed in the
    > > > drop down when the downarrow on the combobox is selected? Thus, the user can
    > > > select the file they want given the dynamic variable.
    > > >
    > > > Once that file is selected and becomes ComboBox1.Value, I want to set my
    > > > variable TextPath to TextPath = ComboxBox1.Value instead of
    > > > "C:\Temp\SN1033_XXX_67489564926.txt". Is that possible?
    > > >
    > > > My current code ==========================
    > > >
    > > > Sub ImportTextStringSN1033()
    > > >
    > > > 'Variable Declarations
    > > >
    > > > Dim TextPath As String
    > > >
    > > > Dim NCData As Variant
    > > >
    > > > '===============================================
    > > >
    > > > TextPath = "C:\Temp\SN1033_XXX_67489564926.txt"
    > > >
    > > > On Error GoTo ErrorMsg
    > > > Open TextPath For Input As #1 'open text file for SN1033
    > > >
    > > >
    > > > Do While Not EOF(1) 'go while not end of text file
    > > > Line Input #1, NCData
    > > >
    > > > If EOF(1) Then
    > > > Application.Range("AP9") = NCData
    > > >
    > > > Range("AP9").Select
    > > >
    > > > 'On Error GoTo ErrorMsg1004
    > > > 'recorded macro for text to columns
    > > > Selection.TextToColumns _
    > > > Destination:=Range("AP9"), _
    > > > DataType:=xlDelimited, _
    > > > Comma:=True, _
    > > > FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1),
    > > > Array(4, 1)), _
    > > > TrailingMinusNumbers:=True
    > > >
    > > > End If
    > > > Loop
    > > > Close #1
    > > >
    > > >
    > > > Exit Sub
    > > > ErrorMsg: 'Error handling routines
    > > > Close #1
    > > > Range("AP9").Select
    > > > Selection.ClearContents
    > > > MsgBox "Error " & Err & ": " & Error(Err)
    > > >
    > > > End Sub
    > > >
    > > > =========================================
    > > >
    > > > Thank you all again for any and all assistance!!!!
    > > >
    > > > Kind regards,
    > > >
    > > > D.Parker


  5. #5
    D.Parker
    Guest

    RE: Select Text Files from Combobox

    Thank you very much! This is most helpful!!!!

    Kind regards,

    D.Parker

    "JMB" wrote:

    > Go into visual basic (Alt F11). Click Insert/Userform. If it is the first
    > userform it will likely be called UserForm1. Click the combobox control on
    > the toolbox. Draw your combobox on the userform.
    >
    > Click the View Code button above the Project Explorer Window and paste this
    > into the code window (modify as needed).
    >
    > Private Sub UserForm_Activate()
    > Me.ComboBox1.Clear
    > With Application.FileSearch
    > .NewSearch
    > .LookIn = "C:\Temp"
    > .Filename = "SN1033" & "*.txt"
    > .SearchSubFolders = False
    > .Execute
    > For i = 1 To .FoundFiles.Count Step 1
    > Me.ComboBox1.AddItem .FoundFiles(i)
    > Next i
    > End With
    >
    > End Sub
    >
    >
    > Now you will need to add code to your project to show the userform. Could
    > be a button on one of your Excel worksheets that links to a macro w/this line
    > in it:
    >
    > UserForm1.Show
    >
    > You will also need to add a command button to the Userform to execute
    > whatever code you need to run after the user selects a file. Add command
    > button the same way you added the combobox, then double click on the button
    > and you'll get
    >
    > Private Sub CommandButton1_Click()
    >
    > End Sub
    >
    >
    > Put your code into the body of this event handler. To refer to the combobox
    > on the userform you could use
    >
    > UserForm1.ComboBox1.Value
    >
    > Or, the preferred way
    >
    > Me.Combobox1.Value
    >
    >
    > When you are viewing the userform, you can show (and change) the properties
    > of the various controls by clicking View/Properties Window.
    >
    >
    > "D.Parker" wrote:
    >
    > > Is there a way to put this into a UserForm vs directly on the sheet? I want
    > > the user to click a button on the sheet and the execution of the previously
    > > mentioned would transpire. Thank you.
    > >
    > > Kind regards,
    > >
    > > D.Parker
    > >
    > > "JMB" wrote:
    > >
    > > > Yes. One way of doing that is with FileSearch. Following is an example for
    > > > a combobox I had set up on Sheet1. Of course, you could use variables for
    > > > some of the items like .LookIn and FileName.
    > > >
    > > > One caveat - if you assign the code to load the filenames into the combobox
    > > > to the DropButton_Click event for the combobox, I've had problems with the
    > > > combobox not retaining the value you select (almost as if the event runs
    > > > again when you select an item from the drop down menu). I've gotten around
    > > > this by saving the combox value right before clearing the combobox, then
    > > > assigning that value back to the combobox right after I cleared it.
    > > >
    > > > Sheet1.ComboBox1.Clear
    > > >
    > > > With Application.FileSearch
    > > > .NewSearch
    > > > .LookIn = "C:\Temp"
    > > > .Filename = "SN1033" & "*.txt"
    > > > .SearchSubFolders = False
    > > > .Execute
    > > > For i = 1 To .FoundFiles.Count Step 1
    > > > Sheet1.ComboBox1.AddItem .FoundFiles(i)
    > > > Next i
    > > > End With
    > > >
    > > >
    > > > "D.Parker" wrote:
    > > >
    > > > > Hello again!
    > > > >
    > > > > Is there a way to read in multiple files of "almost" similar into a combobox
    > > > > for the user to select the file they want? The filenames first few
    > > > > characters will be the same but the following characters will be dymanic.
    > > > > For example:
    > > > >
    > > > > filenames: SN1033_XXX_67489564926.txt
    > > > > SN1033_XXX_00909890879.txt
    > > > > SN1033_XXX_78987907893.txt
    > > > > SN1008_XXX_78290574238.txt
    > > > > SN1008_XXX_54545454544.txt
    > > > > and so on....
    > > > >
    > > > > So I would like to read all the text filenames beginning with "SN1033". Can
    > > > > I search a given folder where the files exist for any file beginning with
    > > > > SN1033 and take all of those file and do a .AddItem for each file to a
    > > > > UserForm, combobox form, where all the SN1033 files will be listed in the
    > > > > drop down when the downarrow on the combobox is selected? Thus, the user can
    > > > > select the file they want given the dynamic variable.
    > > > >
    > > > > Once that file is selected and becomes ComboBox1.Value, I want to set my
    > > > > variable TextPath to TextPath = ComboxBox1.Value instead of
    > > > > "C:\Temp\SN1033_XXX_67489564926.txt". Is that possible?
    > > > >
    > > > > My current code ==========================
    > > > >
    > > > > Sub ImportTextStringSN1033()
    > > > >
    > > > > 'Variable Declarations
    > > > >
    > > > > Dim TextPath As String
    > > > >
    > > > > Dim NCData As Variant
    > > > >
    > > > > '===============================================
    > > > >
    > > > > TextPath = "C:\Temp\SN1033_XXX_67489564926.txt"
    > > > >
    > > > > On Error GoTo ErrorMsg
    > > > > Open TextPath For Input As #1 'open text file for SN1033
    > > > >
    > > > >
    > > > > Do While Not EOF(1) 'go while not end of text file
    > > > > Line Input #1, NCData
    > > > >
    > > > > If EOF(1) Then
    > > > > Application.Range("AP9") = NCData
    > > > >
    > > > > Range("AP9").Select
    > > > >
    > > > > 'On Error GoTo ErrorMsg1004
    > > > > 'recorded macro for text to columns
    > > > > Selection.TextToColumns _
    > > > > Destination:=Range("AP9"), _
    > > > > DataType:=xlDelimited, _
    > > > > Comma:=True, _
    > > > > FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1),
    > > > > Array(4, 1)), _
    > > > > TrailingMinusNumbers:=True
    > > > >
    > > > > End If
    > > > > Loop
    > > > > Close #1
    > > > >
    > > > >
    > > > > Exit Sub
    > > > > ErrorMsg: 'Error handling routines
    > > > > Close #1
    > > > > Range("AP9").Select
    > > > > Selection.ClearContents
    > > > > MsgBox "Error " & Err & ": " & Error(Err)
    > > > >
    > > > > End Sub
    > > > >
    > > > > =========================================
    > > > >
    > > > > Thank you all again for any and all assistance!!!!
    > > > >
    > > > > Kind regards,
    > > > >
    > > > > D.Parker


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1