Select Text Files from Combobox

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

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

Kind regards,

D.Parker