Convert Excel spreadsheet to fixed width ASCII text file : MACRO

Hello ... here's my issue.  I am trying to convert several different spreadsheets in a workbook to fixed width text files.  The actual data changes with each user.

When I run the Macro it appears that the .txt file is created correctly. I do a manual check of the data in WordPad and the data looks correct except for some blank rows which I manually delete up to the last record then I resave the file.

The problem appears when I use a utility (WVutil) to create a .dat file from the .txt file. The utility only reads or sees one record and stops there.

Here is what I need to occur:
1. I need to always remove Column J2 (SEGX)
2. I need to delete columns that may contain garbage.
3. I need to delete rows that may contain garbage/blanks OR better yet not read past the
last record (then I would not need to manually delete blank lines).

I’ve attached a copy of the Macro I used for this particular spreadsheet. I am a novice trying to learn my way through this.  Any help you can provide would be greatly appreciated.

Please Note: I manually copy the excel spreadsheet from a workbook into a new workbook which has the macro.  I’ve created a button on the new spreadsheet to push to run the Macro.

MakeFixedWidth.doc
Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
I suspect this has something to to with the line separator that is used by the FileSystemObject.  Perhaps the WVutil program is seeing this separator code as the end of the file.
You could try using good old VB file I/O instead.  Instead of:

Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.CreateTextFile(FileName, True)
...
ts.WriteLine TheLine
...
ts.Close
try this:
Open FileName For Output As #1
...
Print #1, TheLine
...
Close #1
--
Graham
Commented:
What does WVUtil do ?  Cant find it on the net.

What format is it's output ?  (.dat is very generic name)

The FSO WriteLine method should be exactly the same as Print#,

here is my slightly modified version that removes the array arr()  to be sure there is no memory issue and a slightly more efficient string pad method.

Added a check for blank lines.

Dim Sizes As Variant
'Dim arr As Variant'
Dim rngUsed As Range

Dim r As Long, c As Long
Dim fso As Object
Dim ts As Object
Dim TheLine As String
Dim TestStr As String

Sizes = Array(35, 2, 1, 3, 4, 3, 4, 5, 6, 1, 4, 5, 1, 1, 48, 13, 1, 1, 2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)

Set rngUsed = ActiveSheet.UsedRange

Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.CreateTextFile("c:\ee\xglmast.txt", True)

For r = 5 To rngUsed.Rows.Count
TheLine = ""
For c = 1 To rngUsed.Columns.Count
TestStr = Left$(Trim$(CStr(rngUsed.Cells(r, c).Value)) & String\$(Sizes(c - 1), " "), Sizes(c - 1))
TheLine = TheLine & TestStr
Next c
If Trim(TheLine) = "" Then
'ignore it
g = 1
Else
ts.WriteLine (TheLine)
End If
Next r
ts.Close

Set ts = Nothing
Set fso = Nothing

MsgBox "Macro Completed"


Experts Exchange Solution brought to you by