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

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

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


.