Solved: how to open a .txt file using excel vba

[rafi_07max]

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,

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)

[Tinbendr]

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

David

[rafi_07max]

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.

[Tinbendr]

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.

[Kenneth Hobs]

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

Get the function from the referenced thread.
[VBA]'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 "Plese select a file", vbInformation, "unable to continue" '& Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub
[/VBA]

[rafi_07max]

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

[rafi_07max]

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

[Tinbendr]

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

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

[Kenneth Hobs]

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.