Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
import text file
Hi all,
does anyone have a hint on how I can load a tab delimited file into a worksheet using VBA code??? (also how to save a worksheet into a tab del text file) I need to process a large number of files and would like to automate the loading and saving procedure. Thanks a lot. Mathias |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
import text file
turn on the macro recorder
file=Open, select the file and walk through the text import wizard. when the file is in, turn off the macro recorder. I get: Workbooks.OpenText Filename:="C:\tabtext2.txt", Origin:=437, StartRow:=1 _ , DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _ , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _ Array(3, 1)), TrailingMinusNumbers:=True You can replace "C:\tabtext2.txt " with a variable like fname and use Dim fName as String fName = application.GetOpenFileName(FileFilter:="Text Files (*.txt),*.txt") if lcase(fName) < "false" then Workbooks.OpenText Filename:=fName, Origin:=437, StartRow:=1 _ , DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _ , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _ Array(3, 1)), TrailingMinusNumbers:=True End If you could turn on the macro recorder and save a file - for example I get: ActiveWorkbook.SaveAs Filename:="C:\tabtext2.txt", FileFormat:=xlText, _ CreateBackup:=False Now you can use GetSaveAsFileName Dim fName as String fName = Application.GetSaveAsFilename() if lcase(fName) < "false" then ActiveWorkbook.SaveAs Filename:=fName, FileFormat:=xlText, _ CreateBackup:=False ActiveWorkbook.close SaveChanges:=False End if -- Regards, Tom Ogilvy "Mathias" wrote in message ... Hi all, does anyone have a hint on how I can load a tab delimited file into a worksheet using VBA code??? (also how to save a worksheet into a tab del text file) I need to process a large number of files and would like to automate the loading and saving procedure. Thanks a lot. Mathias |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
import text file
Thanks a lot!!!
-----Original Message----- turn on the macro recorder file=Open, select the file and walk through the text import wizard. when the file is in, turn off the macro recorder. I get: Workbooks.OpenText Filename:="C:\tabtext2.txt", Origin:=437, StartRow:=1 _ , DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _ , Space:=False, Other:=False, FieldInfo:=Array (Array(1, 1), Array(2, 1), _ Array(3, 1)), TrailingMinusNumbers:=True You can replace "C:\tabtext2.txt " with a variable like fname and use Dim fName as String fName = application.GetOpenFileName(FileFilter:="Text Files (*.txt),*.txt") if lcase(fName) < "false" then Workbooks.OpenText Filename:=fName, Origin:=437, StartRow:=1 _ , DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _ , Space:=False, Other:=False, FieldInfo:=Array (Array(1, 1), Array(2, 1), _ Array(3, 1)), TrailingMinusNumbers:=True End If you could turn on the macro recorder and save a file - for example I get: ActiveWorkbook.SaveAs Filename:="C:\tabtext2.txt", FileFormat:=xlText, _ CreateBackup:=False Now you can use GetSaveAsFileName Dim fName as String fName = Application.GetSaveAsFilename() if lcase(fName) < "false" then ActiveWorkbook.SaveAs Filename:=fName, FileFormat:=xlText, _ CreateBackup:=False ActiveWorkbook.close SaveChanges:=False End if -- Regards, Tom Ogilvy "Mathias" wrote in message ... Hi all, does anyone have a hint on how I can load a tab delimited file into a worksheet using VBA code??? (also how to save a worksheet into a tab del text file) I need to process a large number of files and would like to automate the loading and saving procedure. Thanks a lot. Mathias . |
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) | |||
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) | |||
Import text file into excel with preset file layout, delimeters VBA | Excel Programming | |||
Get External Data, Import Text File, File name problem | Excel Programming |