import text file

[clui]

What's the VBA code to import a tab delimited text file to a workshee
in an existing workbook? Thanks

 

[Tom Ogilvy]

If you have Excel 2000 or later:

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 12/2/2003 by OGILVTW
'

'
With ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\Data\aaa_tab.txt",
_
Destination:=Range("F20"))
.Name = "aaa_tab"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)
.Refresh BackgroundQuery:=False
End With
End Sub

--
Regards,
Tom Ogilvy

What's the VBA code to import a tab delimited text file to a worksheet
in an existing workbook? Thanks!


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to

creating financial statements

 

[clui]

Thanks, Tom. Now what if I need to import multiple text files into
separate worksheets? I should not have to repeat the complete piece of
code for each import. I tried several ways, but none worked. Please
help.

 

[Tom Ogilvy]

Sub Importfiles()
Dim fName as variant, sSheet as Variant
Dim i as long, fn as String
Dim rng as Range
fname = Array( _
"C:\text\file1.txt", _
"C:\text\File2.txt", _
"C:\text\File2.txt")
sSheet = Array("sheet1", _
"sheet2", _
"sheet3")
for i = lbound(fname) to ubound(fname)
fn = fname(i)
set sh = worksheets(sSheet(i))
set rng = sh.Range("B9")
ImportText sh, fn, rng
Next
End sub




Public Function Importtext( sh as Worksheet, sName as String, rng as Range)

With sh.QueryTables.Add(Connection:="TEXT;" & _
sName, Destination:=rng)
.Name = "aaa_tab"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)
.Refresh BackgroundQuery:=False
End With
End Sub


--
Regards,
Tom Ogilvy

Thanks, Tom. Now what if I need to import multiple text files into
separate worksheets? I should not have to repeat the complete piece of
code for each import. I tried several ways, but none worked. Please
help.


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to

creating financial statements