• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 363
  • Last Modified:

Create fixed length AIX word pad file

I have an xL file that i need to convert into a fixed length AIX file in xL or Word Pad.
example below:
3M 021200-43178      6
3M 021200-71120      32
3M 048011-04028      55
3M 048011-04029      116
3M 051115-26230      296
3M 051131-52750      6
Below are the parameters:
Field                      Start Position                 Length           Format
Item Code                1                                  26               Character
Quantity                 28                                 11                Decimal 99999999.99
1 Solution
Nico BontenbalCommented:
I think this macro is pretty close to what you need:
Option Explicit

Sub SaveAsText()
    Dim rw As Range
    Dim lngFile As Long
    Dim strLine As String
    lngFile = FreeFile
    Open ActiveWorkbook.FullName & ".txt" For Output Access Write Shared As #lngFile
    For Each rw In ActiveSheet.UsedRange.Rows
        strLine = Left(rw.Cells(1, 1).Value & String(26, " "), 26)
        strLine = strLine & rw.Cells(1, 2).Value
        Print #lngFile, strLine
    Close #lngFile
End Sub

Open in new window

It creates a file with same name as the .xls but with the extension .txt.
TracyVBA DeveloperCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now