Mega Code Archive
Opening Delimited Files Simplified
<![CDATA[
Sub TestOpenDelimitedFile()
Dim myWorkbook As Workbook
Dim vFields As Variant
vFields = Array(Array(3, xlMDYFormat))
Set myWorkbook = OpenDelimitedFile("C:\tab delimited.txt", 2,xlTextQualifierNone, False, vbTab, vFields)
Set myWorkbook = Nothing
End Sub
Function OpenDelimitedFile(sFile As String, _
lStartRow As Long, _
TxtQualifier As XlTextQualifier, _
bConsecutiveDelimiter As Boolean, _
sDelimiter As String, _
Optional vFieldInfo As Variant) As Workbook
On Error GoTo ErrHandler
If IsMissing(vFieldInfo) Then
Application.Workbooks.OpenText _
Filename:=sFile, _
StartRow:=lStartRow, _
DataType:=xlDelimited, _
TextQualifier:=TxtQualifier, _
ConsecutiveDelimiter:=bConsecutiveDelimiter, _
Other:=True, _
OtherChar:=sDelimiter
Else
Application.Workbooks.OpenText _
Filename:=sFile, _
StartRow:=lStartRow, _
DataType:=xlDelimited, _
TextQualifier:=TxtQualifier, _
ConsecutiveDelimiter:=bConsecutiveDelimiter, _
Other:=True, _
OtherChar:=sDelimiter, _
FieldInfo:=vFieldInfo
End If
Set OpenDelimitedFile = ActiveWorkbook
ExitPoint:
Exit Function
ErrHandler:
Set OpenDelimitedFile = Nothing
Resume ExitPoint
End Function
]]>