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
dgriffAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

GrahamMandenoCommented:
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
Robberbaron (robr)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"

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial
dgriffAuthor 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
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.