open a .txt delimited file in excel using VBA

Maybe you need to use the OpenText method, which allows you to specify the delimiters. Check it out in VBA Help or record a macro while doing it manually.

I recorded the macro and did a cut and paste but Im getting an error..


Sub GetImportFileName()
Dim Filt As String
Dim FilterIndex As Integer
Dim FileName As Variant
Dim Title As String

' Set up list of file filters
Filt = "Text Files (*.txt),*.txt," & _
"Lotus Files (*.prn),*.prn," & _
"Comma Separated Files (*.csv),*.csv," & _
"ASCII Files (*.asc),*.asc," & _
"All Files (*.*),*.*"

' Display *.* by default
FilterIndex = 5

' Set the dialog box caption
Title = "Select a File to Import"

' Get the file name
FileName = Application.GetOpenFilename _
(FileFilter:=Filt, _
FilterIndex:=FilterIndex, _
Title:=Title)

' Exit if dialog box canceled
If FileName = False Then
MsgBox "No file was selected."
Exit Sub
End If

' Display full path and name of the file
'MsgBox "You selected " & FileName

' Display full path and name of the file
'MsgBox "You selected " & FileName
'Workbooks.Open FileName

' Display full path and name of the file
MsgBox "You selected " & FileName
Workbooks.OpenText FileName:=FileName_, Origin _
:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote _
, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=True _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), _
Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array( _
16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), _
Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1), Array(27, 1), Array(28, 1), Array( _
29, 1), Array(30, 1), Array(31, 1)), TrailingMinusNumbers:=True

End Sub

The error being ...

I recommend not using FileName as a variable name since there is a parameter name in OpenText called Filename.

When you compile it, it should have shown the error that FileName_ was not defined if you have the option set in VBE to declare variables. If not, always use as the first line:

Oh sorry...

Cant excute code in break mode.

Then choose Run|Reset from the VBE menu.