# 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?
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
0
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"

0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
Thank you for your quick reply... I apologize for the delay in responding to your questions... I tried your suggestions and it took care of this particular issue.

I have 2 more questions but I will open up a new thread for those.

Thanks again
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.