Hello,
I would like to be able to open a .txt file in Excel VBA using the Text
Import Wizard. The file is Tab Delimited and used the "~" or "Other". Once
open I want to be able to change some values in fields and then save it with
the changes as a text file.

Can anyone tell me how I would save the file as a .txt file with the changes
I made?
For some reason there are four blank columns at the beginning and I need to
keep those in place when saving back to a text file.

I used the macro record feature and the code to open the text file with the
Import Wizard looks like:

Workbooks.OpenText Filename:= _
"C:\leasepayment.txt" _
, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, _
Comma:=False, Space:=False, Other:=True, OtherChar:="~", FieldInfo:= _
Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5,
1), Array(6, 1), Array(7 _
, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1),
Array(12, 1), Array(13, 1), Array _
(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1),
Array(19, 1), Array(20, 1), _
Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array(25,
1), Array(26, 1), Array( _
27, 1), Array(28, 1), Array(29, 1), Array(30, 1), Array(31, 1),
Array(32, 1), Array(33, 1), _
Array(34, 1), Array(35, 1), Array(36, 1), Array(37, 1), Array(38,
1), Array(39, 1), Array( _
40, 1)), TrailingMinusNumbers:=True

ANY HELP WOULD BE APPRECIATED!!

THANKS!

Maybe you could use a macro that writes your data:

Earl Kiosterud's Text Write program:
www.smokeylake.com/excel
(or directly: http://www.smokeylake.com/excel/text_write_program.htm)

Chip Pearson's:
http://www.cpearson.com/excel/imptext.htm

J.E. McGimpsey's:
http://www.mcgimpsey.com/excel/textfiles.html

======
Earl's may be sufficient right out of the box. He supports lots of options.


sharonm wrote:

Hello,
I would like to be able to open a .txt file in Excel VBA using the Text
Import Wizard. The file is Tab Delimited and used the "~" or "Other". Once
open I want to be able to change some values in fields and then save it with
the changes as a text file.

Can anyone tell me how I would save the file as a .txt file with the changes
I made?
For some reason there are four blank columns at the beginning and I need to
keep those in place when saving back to a text file.

I used the macro record feature and the code to open the text file with the
Import Wizard looks like:

Workbooks.OpenText Filename:= _
"C:\leasepayment.txt" _
, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, _
Comma:=False, Space:=False, Other:=True, OtherChar:="~", FieldInfo:= _
Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5,
1), Array(6, 1), Array(7 _
, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1),
Array(12, 1), Array(13, 1), Array _
(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1),
Array(19, 1), Array(20, 1), _
Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array(25,
1), Array(26, 1), Array( _
27, 1), Array(28, 1), Array(29, 1), Array(30, 1), Array(31, 1),
Array(32, 1), Array(33, 1), _
Array(34, 1), Array(35, 1), Array(36, 1), Array(37, 1), Array(38,
1), Array(39, 1), Array( _
40, 1)), TrailingMinusNumbers:=True

ANY HELP WOULD BE APPRECIATED!!

THANKS!

--

Dave Peterson