How to open a .txt file using excel vba

Attachment 4794

From the picture we can see a combobox and a button. What I need to do now is that once I select a combobox and hit the button. I should able to retrieve a file by the name that is exactly same as the combobox name. For e.g. when I select apple combobox, I should able to select a file name that must be apple.txt. And the user has the freedom to choose the location of the file (e.g. the file can be in C drive or D drive or any other folder). I managed to create these codes,

Code:

---

Private Sub CommandButton1_Click()

On Error GoTo ErrorHandler

If ComboBox1.Text = "apple" Then
    myFile = Application.GetOpenFilename("Text Files,*.txt")
 Workbooks.OpenText Filename:= _
        myFile, Origin _
        :=xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array _
        (4, 1), Array(10, 1), Array(26, 1), Array(27, 1), Array(50, 1), Array(58, 1)), _
        TrailingMinusNumbers:=True
    ActiveSheet.Move After:=Workbooks("retriveFile.xls").Sheets(1)
    ActiveWindow.WindowState = xlMaximized
    Exit Sub
End If
ErrorHandler:
    MsgBox "Plese select a file", vbInformation, "unable to continue" '& Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub

---

From this code, the user can choose the file from any location. But at the same time he can also any .txt file, which what I want to avoid.

Important:
* File I want to retrieve is in .txt format
* the user must have full freedom in choosing the file location
* But he can only select a .txt file name that is exactly same as to the combobox he select (e.g. if he select apple for the combobox then, he can only open a file name that goes by apple.txt)

Use GetOpenFilename instead to get the path/filename. Use the combobox result as the filter. Then use OpenText to open the file.

David

Hi, thanks for your help. But i am new to excel vba and wasn't too sure what u tryiing to say.Can you please give a more detailed explanation,if possible with the correct codes. Once again thank you.

Sorry for no direct solution, but I have been covered up at work today.

I forgot that GetOpenFileName does allow for filename filters. Only wildcard filters. So you can filter by *.txt, but not by Apple.txt.

Both of the solutions let you navigate for a directory. Once you have that you can add Me.Combox1.Text to the end, then use OpenText to open the file.

Here is one solution I found. Here is another.

Use a folder dialog rather than a file name dialog as suggested.

Get the function from the referenced thread.

Code:

---

'http://www.vbaexpress.com/forum/showthread.php?t=34695
Private Sub CommandButton1_Click()
    Dim myFile As String
    On Error GoTo ErrorHandler
    If ComboBox1.Text = "apple" Then
        myFile = Get_Folder("Select Folder", ThisWorkbook.Path) & "\" & ComboBox1.Text & ".txt"
    End If
    If Dir(myFile) = "" Then Exit Sub
    Workbooks.OpenText Filename:= _
    myFile, Origin _
    :=xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array _
    (4, 1), Array(10, 1), Array(26, 1), Array(27, 1), Array(50, 1), Array(58, 1)), _
    TrailingMinusNumbers:=True
    'ActiveSheet.Move After:=Workbooks("retriveFile.xls").Sheets(1)
    'ActiveWindow.WindowState = xlMaximized
    ErrorHandler:
    MsgBox "Please select a file", vbInformation, "unable to continue" '& Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub

---

Hi thanks to both of u. My program works fine.:):)

By the way, in this forum, how to show appreciation to referenced user who have helped, like adding reputation for them.

That isn't available here, but you can marked the thread solved.

At the top of this thread, look at the Thread Tools Dropdown.

IF you use the Reply rather than Quick Reply, you can rate the thread.

One could use a Post Icon for your message from a list.

These options are below the reply box.

You should also mark your thread solved as suggested.