WOW... I've finally found a beacon of hope :-).

Q: Can I use "something" to access a file using EXCEL, then use some
"wizardry" to make changes to the file?

Let me start with, I have been a mainframe programmer for about a hundred
years, but I know next to nothing about Excel or the PC/LAN environment. But,
with some research I have figured out there may be some way to automate a
process that my team does many times a day.

I have created a bunch of mainframe stuff that results in a comma delimited
..txt file being TCPIP'ed to a LAN directory. Once it is on the LAN I open
EXCEL and manually do a series of steps. What I am hoping to accomplish is to
use WMI to watch the LAN folder to see when a new .txt file is TCPIP'ed into
it. Then I want to "magically" access EXCEL and in the background, open the
..txt file, and apply the manual steps (which I'll outline below). I have
already played with WMI a bit and have the watching the LAN directory for new
..txt files working, but I have no clue how to access EXCEL and apply the
manual steps.

Here are the manual steps:

+++ Open EXCEL
+++ File/Open/ (open .txt file to be modified)
+++ Page 1 of Text Import Wizard comes up and I select "Delimited" Next
+++ Page 2 of Text Import Wizard comes up and I uncheck "Tab" and check
"Comma" Next
+++ Page 3 of Text Import Wizard comes up and in the Data Preview I select
all columns, and select "Text" as the Column data format Finish
+++ Excel now shows the data. I do Ctrl+A to Select All
+++ Format/Column/AutoFit Enter
+++ File/Save As
+++ Change Save As Type to "Microsoft Excel 97 - Excel 2003 & 5.0/95
workbook(*.xls)" Save

So, that's it. Am I out of my mind, or is there a way to do that?

The best way to start is to record a macro and post if so it can be modified.

1) From spreadsheet go to menu - Tools - Macro - Record Macro
2) Run the steps you described below
3) Stop record Tools - Macro - Stop Recording
4) the macro will be in the VBA window under moduls. This code can be
modified


The macro can be placed in a workbook Open envent so it will automatically
run.

"Jeff" wrote:

WOW... I've finally found a beacon of hope :-).

Q: Can I use "something" to access a file using EXCEL, then use some
"wizardry" to make changes to the file?

Let me start with, I have been a mainframe programmer for about a hundred
years, but I know next to nothing about Excel or the PC/LAN environment. But,
with some research I have figured out there may be some way to automate a
process that my team does many times a day.

I have created a bunch of mainframe stuff that results in a comma delimited
.txt file being TCPIP'ed to a LAN directory. Once it is on the LAN I open
EXCEL and manually do a series of steps. What I am hoping to accomplish is to
use WMI to watch the LAN folder to see when a new .txt file is TCPIP'ed into
it. Then I want to "magically" access EXCEL and in the background, open the
.txt file, and apply the manual steps (which I'll outline below). I have
already played with WMI a bit and have the watching the LAN directory for new
.txt files working, but I have no clue how to access EXCEL and apply the
manual steps.

Here are the manual steps:

+++ Open EXCEL
+++ File/Open/ (open .txt file to be modified)
+++ Page 1 of Text Import Wizard comes up and I select "Delimited" Next
+++ Page 2 of Text Import Wizard comes up and I uncheck "Tab" and check
"Comma" Next
+++ Page 3 of Text Import Wizard comes up and in the Data Preview I select
all columns, and select "Text" as the Column data format Finish
+++ Excel now shows the data. I do Ctrl+A to Select All
+++ Format/Column/AutoFit Enter
+++ File/Save As
+++ Change Save As Type to "Microsoft Excel 97 - Excel 2003 & 5.0/95
workbook(*.xls)" Save

So, that's it. Am I out of my mind, or is there a way to do that?

COOL... Thanks Joel... I'll go do that now (well, soon as I get through my
morning folder of things to do... but yes... I'll do that today).

Jeff.

Well Joel... nothing is ever easy.

When I try to record the macro I get an error "Too Many line Continuations"
and the macro recording terminates. Below is the macro code I end up with.
But it seems important that when I open the .txt file the "Text Import
Wizard" comes up... I don't think I see anything in the macro code from when
I completed the "Text Import Wizard" pages.

ub AutoIT()
'
' AutoIT Macro
' Macro recorded 1/25/2008 by jjken
'

'
Workbooks.OpenText Filename := _
"S:\TRANSFER\SYOPMAINFRAME\Conversions\Kernmore.jj k\TEST.TXT",
Origin := _
437, StartRow := 1, DataType := xlDelimited, TextQualifier :=
xlDoubleQuote, _
ConsecutiveDelimiter := FALSE, Tab := FALSE, Semicolon := FALSE,
Comma := TRUE _
, Space := FALSE, Other := FALSE, FieldInfo :=
Array(Array(1,2),Array(2,2), _

Array(3,2),Array(4,2),Array(5,2),Array(6,2),Array( 7,2),Array(8,2),Array(9,2),
_

Array(10,2),Array(11,2),Array(12,2),Array(13,2),Ar ray(14,2),Array(15,2),Array( _

16,2),Array(17,2),Array(18,2),Array(19,2),Array(20 ,2),Array(21,2),Array(22,2), _

Array(23,2),Array(24,2),Array(25,2),Array(26,2),Ar ray(27,2),Array(28,2),Array( _

29,2),Array(30,2),Array(31,2),Array(32,2),Array(33 ,2),Array(34,2),Array(35,2), _

Array(36,2),Array(37,2),Array(38,2),Array(39,2),Ar ray(40,2),Array(41,2),Array( _

42,2),Array(43,2),Array(44,2),Array(45,2),Array(46 ,2),Array(47,2),Array(48,2), _

Array(49,2),Array(50,2),Array(51,2),Array(52,2),Ar ray(53,2),Array(54,2),Array( _

55,2),Array(56,2),Array(57,2),Array(58,2),Array(59 ,2),Array(60,2),Array(61,2), _

Array(62,2),Array(63,2),Array(64,2),Array(65,2),Ar ray(66,2),Array(67,2),Array( _

68,2),Array(69,2),Array(70,2),Array(71,2),Array(72 ,2),Array(73,2),Array(74,2), _

Array(75,2),Array(76,2),Array(77,2),Array(78,2),Ar ray(79,2),Array(80,2),Array( _

81,2),Array(82,2),Array(83,2),Array(84,2),Array(85 ,2),Array(86,2),Array(87,2), _

Array(88,2),Array(89,2),Array(90,2),Array(91,2),Ar ray(92,2),Array(93,2),Array( _

94,2),Array(95,2),Array(96,2),Array(97,2),Array(98 ,2),Array(99,2),Array(100,2), _

Array(101,2),Array(102,2),Array(103,2),Array(104,2 ),Array(105,2),Array(106,2), _

Array(107,2),Array(108,2),Array(109,2),Array(110,2 ),Array(111,2),Array(112,2), _

Array(113,2),Array(114,2),Array(115,2),Array(116,2 ),Array(117,2),Array(118,2), _

Array(119,2),Array(120,2),Array(121,2),Array(122,2 ),Array(123,2),Array(124,2), _
End Sub

See if this code works better

Sub Getfixedtext2()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const MyPath = "S:\TRANSFER\SYOPMAINFRAME\Conversions\Kernmore.jj k\"
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0


Set fsread = CreateObject("Scripting.FileSystemObject")


ReadFileName = "text.txt"


'open files
ReadPathName = MyPath & ReadFileName
Set fread = fsread.GetFile(ReadPathName)
Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)

RowCount = 1
Do While tsread.atendofstream = False
InputLine = tsread.ReadLine
For ColCount = 1 To 124
Cells(RowCount, ColCount) = Mid(InputLine, 2 * (ColCount - 1), 2)
Next ColCount
RowCount = RowCount + 1
Loop
tsread.Close
End Sub

"Jeff" wrote:

Well Joel... nothing is ever easy.

When I try to record the macro I get an error "Too Many line Continuations"
and the macro recording terminates. Below is the macro code I end up with.
But it seems important that when I open the .txt file the "Text Import
Wizard" comes up... I don't think I see anything in the macro code from when
I completed the "Text Import Wizard" pages.

ub AutoIT()
'
' AutoIT Macro
' Macro recorded 1/25/2008 by jjken
'

'
Workbooks.OpenText Filename := _
"S:\TRANSFER\SYOPMAINFRAME\Conversions\Kernmore.jj k\TEST.TXT",
Origin := _
437, StartRow := 1, DataType := xlDelimited, TextQualifier :=
xlDoubleQuote, _
ConsecutiveDelimiter := FALSE, Tab := FALSE, Semicolon := FALSE,
Comma := TRUE _
, Space := FALSE, Other := FALSE, FieldInfo :=
Array(Array(1,2),Array(2,2), _

Array(3,2),Array(4,2),Array(5,2),Array(6,2),Array( 7,2),Array(8,2),Array(9,2),
_

Array(10,2),Array(11,2),Array(12,2),Array(13,2),Ar ray(14,2),Array(15,2),Array( _

16,2),Array(17,2),Array(18,2),Array(19,2),Array(20 ,2),Array(21,2),Array(22,2), _

Array(23,2),Array(24,2),Array(25,2),Array(26,2),Ar ray(27,2),Array(28,2),Array( _

29,2),Array(30,2),Array(31,2),Array(32,2),Array(33 ,2),Array(34,2),Array(35,2), _

Array(36,2),Array(37,2),Array(38,2),Array(39,2),Ar ray(40,2),Array(41,2),Array( _

42,2),Array(43,2),Array(44,2),Array(45,2),Array(46 ,2),Array(47,2),Array(48,2), _

Array(49,2),Array(50,2),Array(51,2),Array(52,2),Ar ray(53,2),Array(54,2),Array( _

55,2),Array(56,2),Array(57,2),Array(58,2),Array(59 ,2),Array(60,2),Array(61,2), _

Array(62,2),Array(63,2),Array(64,2),Array(65,2),Ar ray(66,2),Array(67,2),Array( _

68,2),Array(69,2),Array(70,2),Array(71,2),Array(72 ,2),Array(73,2),Array(74,2), _

Array(75,2),Array(76,2),Array(77,2),Array(78,2),Ar ray(79,2),Array(80,2),Array( _

81,2),Array(82,2),Array(83,2),Array(84,2),Array(85 ,2),Array(86,2),Array(87,2), _

Array(88,2),Array(89,2),Array(90,2),Array(91,2),Ar ray(92,2),Array(93,2),Array( _

94,2),Array(95,2),Array(96,2),Array(97,2),Array(98 ,2),Array(99,2),Array(100,2), _

Array(101,2),Array(102,2),Array(103,2),Array(104,2 ),Array(105,2),Array(106,2), _

Array(107,2),Array(108,2),Array(109,2),Array(110,2 ),Array(111,2),Array(112,2), _

Array(113,2),Array(114,2),Array(115,2),Array(116,2 ),Array(117,2),Array(118,2), _

Array(119,2),Array(120,2),Array(121,2),Array(122,2 ),Array(123,2),Array(124,2), _
End Sub

By the way, you don't need the field delimiter when you are using commar
delimited. The field info is if you are reading fixed space. I think you
can simply remove the fieldinfor from you original code and it should work.

"Jeff" wrote:

Well Joel... nothing is ever easy.

When I try to record the macro I get an error "Too Many line Continuations"
and the macro recording terminates. Below is the macro code I end up with.
But it seems important that when I open the .txt file the "Text Import
Wizard" comes up... I don't think I see anything in the macro code from when
I completed the "Text Import Wizard" pages.

ub AutoIT()
'
' AutoIT Macro
' Macro recorded 1/25/2008 by jjken
'

'
Workbooks.OpenText Filename := _
"S:\TRANSFER\SYOPMAINFRAME\Conversions\Kernmore.jj k\TEST.TXT",
Origin := _
437, StartRow := 1, DataType := xlDelimited, TextQualifier :=
xlDoubleQuote, _
ConsecutiveDelimiter := FALSE, Tab := FALSE, Semicolon := FALSE,
Comma := TRUE _
, Space := FALSE, Other := FALSE, FieldInfo :=
Array(Array(1,2),Array(2,2), _

Array(3,2),Array(4,2),Array(5,2),Array(6,2),Array( 7,2),Array(8,2),Array(9,2),
_

Array(10,2),Array(11,2),Array(12,2),Array(13,2),Ar ray(14,2),Array(15,2),Array( _

16,2),Array(17,2),Array(18,2),Array(19,2),Array(20 ,2),Array(21,2),Array(22,2), _

Array(23,2),Array(24,2),Array(25,2),Array(26,2),Ar ray(27,2),Array(28,2),Array( _

29,2),Array(30,2),Array(31,2),Array(32,2),Array(33 ,2),Array(34,2),Array(35,2), _

Array(36,2),Array(37,2),Array(38,2),Array(39,2),Ar ray(40,2),Array(41,2),Array( _

42,2),Array(43,2),Array(44,2),Array(45,2),Array(46 ,2),Array(47,2),Array(48,2), _

Array(49,2),Array(50,2),Array(51,2),Array(52,2),Ar ray(53,2),Array(54,2),Array( _

55,2),Array(56,2),Array(57,2),Array(58,2),Array(59 ,2),Array(60,2),Array(61,2), _

Array(62,2),Array(63,2),Array(64,2),Array(65,2),Ar ray(66,2),Array(67,2),Array( _

68,2),Array(69,2),Array(70,2),Array(71,2),Array(72 ,2),Array(73,2),Array(74,2), _

Array(75,2),Array(76,2),Array(77,2),Array(78,2),Ar ray(79,2),Array(80,2),Array( _

81,2),Array(82,2),Array(83,2),Array(84,2),Array(85 ,2),Array(86,2),Array(87,2), _

Array(88,2),Array(89,2),Array(90,2),Array(91,2),Ar ray(92,2),Array(93,2),Array( _

94,2),Array(95,2),Array(96,2),Array(97,2),Array(98 ,2),Array(99,2),Array(100,2), _

Array(101,2),Array(102,2),Array(103,2),Array(104,2 ),Array(105,2),Array(106,2), _

Array(107,2),Array(108,2),Array(109,2),Array(110,2 ),Array(111,2),Array(112,2), _

Array(113,2),Array(114,2),Array(115,2),Array(116,2 ),Array(117,2),Array(118,2), _

Array(119,2),Array(120,2),Array(121,2),Array(122,2 ),Array(123,2),Array(124,2), _
End Sub

OK Joel€¦ this is great and THANK YOU.

I am really a fish out of water here€¦ but here is what I am able to put
together. I have the code from the first macro that was above the field info
(this is the part that converts the .txt file to a comma delimited text
format file?), and then I have added the macro code for the remainder of the
manual things I do (Select and autofit all columns, then SaveAs). Is this
what you were suggesting I do?

@@@

ub AutoIT()
'
' AutoIT Macro
' Macro recorded 1/25/2008 by jjken
'

'
Workbooks.OpenText Filename := _
"S:\xxx\Kernmore.jjk\TEST.TXT",
Origin := _
437, StartRow := 1, DataType := xlDelimited, TextQualifier :=
xlDoubleQuote, _
ConsecutiveDelimiter := FALSE, Tab := FALSE, Semicolon := FALSE,
Comma := TRUE _
, Space := FALSE, Other := FALSE
Range("A1:FB829").Select
Selection.Columns.AutoFit
ChDir "S:\xxx\Kernmore.jjk"
ActiveWorkbook.SaveAs Filename:= _
"S:\xxx\Kenmore.jjk\TESTTEST.xls", FileFormat _
:=xlExcel9795, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=False
End Sub

@@@

Is what I have close to right?

This will be so very very cool if I can get it working.

Thanks, Jeff

Recorded macro create a lot of unnecessary code. Wha tyou havve should work.
Here is a cleaned up version

ub AutoIT()
'
' AutoIT Macro
' Macro recorded 1/25/2008 by jjken
'

'
Workbooks.OpenText Filename := _
"S:\xxx\Kernmore.jjk\TEST.TXT",
Origin := 437, StartRow := 1,_
DataType := xlDelimited, _
TextQualifier := xlDoubleQuote, _
Comma := TRUE _

Range("A1:FB829").Columns.AutoFit
ChDir "S:\xxx\Kernmore.jjk"

ActiveWorkbook.SaveAs Filename:= _
"S:\xxx\Kenmore.jjk\TESTTEST.xls", _
FileFormat:=xlExcel9795
End Sub

"Jeff" wrote:

OK Joel€¦ this is great and THANK YOU.

I am really a fish out of water here€¦ but here is what I am able to put
together. I have the code from the first macro that was above the field info
(this is the part that converts the .txt file to a comma delimited text
format file?), and then I have added the macro code for the remainder of the
manual things I do (Select and autofit all columns, then SaveAs). Is this
what you were suggesting I do?

@@@

ub AutoIT()
'
' AutoIT Macro
' Macro recorded 1/25/2008 by jjken
'

'
Workbooks.OpenText Filename := _
"S:\xxx\Kernmore.jjk\TEST.TXT",
Origin := _
437, StartRow := 1, DataType := xlDelimited, TextQualifier :=
xlDoubleQuote, _
ConsecutiveDelimiter := FALSE, Tab := FALSE, Semicolon := FALSE,
Comma := TRUE _
, Space := FALSE, Other := FALSE
Range("A1:FB829").Select
Selection.Columns.AutoFit
ChDir "S:\xxx\Kernmore.jjk"
ActiveWorkbook.SaveAs Filename:= _
"S:\xxx\Kenmore.jjk\TESTTEST.xls", FileFormat _
:=xlExcel9795, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=False
End Sub

@@@

Is what I have close to right?

This will be so very very cool if I can get it working.

Thanks, Jeff