PDA

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



rafi_07max
10-28-2010, 04:56 AM
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
10-28-2010, 06:06 AM
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
10-28-2010, 07:32 AM
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
10-28-2010, 02:25 PM
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 (http://www.vbaexpress.com/kb/getarticle.php?kb_id=284) is one solution I found. Here (http://j-walk.com/ss/excel/tips/tip29.htm) is another.

Kenneth Hobs
10-28-2010, 06:29 PM
Use a folder dialog rather than a file name dialog as suggested.

Get the function from the referenced thread.
'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

rafi_07max
10-29-2010, 04:51 AM
Hi thanks to both of u. My program works fine.:):)

rafi_07max
10-29-2010, 04:54 AM
By the way, in this forum, how to show appreciation to referenced user who have helped, like adding reputation for them.

Tinbendr
10-29-2010, 07:31 AM
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
10-29-2010, 07:46 AM
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.