Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
import text file
What's the VBA code to import a tab delimited text file to a workshee in an existing workbook? Thanks ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
import text file
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:\D ata\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 "clui" wrote in message ... What's the VBA code to import a tab delimited text file to a worksheet in an existing workbook? Thanks! ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
import text file
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. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
import text file
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 "clui" wrote in message ... 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. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I import text file of cash flow to excel file then use formula | Excel Discussion (Misc queries) | |||
Text file import | Excel Worksheet Functions | |||
Set Defaults in File Import of a Text File | Excel Worksheet Functions | |||
Would Like to Automate Batch File Creation and Text FIle Import | Excel Discussion (Misc queries) | |||
Get External Data, Import Text File, File name problem | Excel Programming |