Creating a fixed width file from data and saving

cheesey

Board Regular
Joined
Dec 13, 2003
Messages
81
Hi

Seem to be going round in circles at the mo with what I thought was relatively straightforward.

I've extracted some accounts data and need to create a fixed width file so I can update it into some other software.

For example I have columns SYSTEM CODE(2), ACCNR(8),DEPT(4),POSTDATE(6),NARR(20),POSTVAL(12),PERIOD(4),SPARE(200) and these are populated with this data as below. Numbers in brackets are the lengths of each field

NLB301
130220​
TEST
120.00​
0619
NLG121
130220​
TEST
120.00​
0619

I've used the LEFT formula like LEFT(A1&" ",2) but can't seem to save just that via a PRN file so my old brain is struggling:). I get the impression this is probably not the best way of doing this

Ideally I would like to use a macro to output the file from within my raw data automatically

Any ideas/assistance would be appreciated:)

Cheesey
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Try this:
VBA Code:
Option Explicit

Private Type AccountsRecord
    SYSTEMCODE As String * 2
    ACCNR As String * 8
    DEPT As String * 4
    POSTDATE As String * 6
    NARR As String * 20
    POSTVAL As String * 12
    PERIOD As String * 4
    SPARE As String * 200
End Type


Public Sub Create_Data_File()

    Dim dataOutputFile As String
    Dim fileNum As Integer
    Dim rec As AccountsRecord
    Dim r As Long
    
    dataOutputFile = "C:\Temp\data_file.txt"
    
    fileNum = FreeFile
    Open dataOutputFile For Output As #fileNum
    
    With Worksheets("Sheet1")
        For r = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
            rec.SYSTEMCODE = .Cells(r, "A").Text
            rec.ACCNR = .Cells(r, "B").Text
            rec.DEPT = .Cells(r, "C").Text
            rec.POSTDATE = .Cells(r, "D").Text
            rec.NARR = .Cells(r, "E").Text
            rec.POSTVAL = .Cells(r, "F").Text
            rec.PERIOD = .Cells(r, "G").Text
            rec.SPARE = .Cells(r, "H").Text
            Print #fileNum, rec.SYSTEMCODE & rec.ACCNR & rec.DEPT & rec.POSTDATE & rec.NARR & rec.POSTVAL & rec.PERIOD & rec.SPARE
        Next
    End With
    
    Close #fileNum

    Shell "notepad.exe " & dataOutputFile, vbNormalFocus

End Sub
 
Upvote 0
Wow many thanks for that much appreciated:)

However there maybe multiple lines which are variable eg. could be 2 lines could be 10 lines(Debits and Credits), using the same headers/format though
NLB301130220TEST120.000619
NLG121130220TEST120.000619

I'm no VB expert but do I need to amend:-

For r = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row

TIA
 
Upvote 0
That line loops from row 2 to the last row (determined by looking in column A), so it reads a varying number of lines.

What do you mean by 'using the same headers'?

Show the layout of your data sheet if the code doesn't loop correctly.
 
Upvote 0
Thanks for your reply

An example(below) using the column headers SYSTEMCODE, ACCN, DEPT,POSTDATE, NARR, POSTVAL, PERIOD, SPARE . There could be a number of rows in addition to this but the macro only exports the 1st line and not the 2nd one.

NLB301
130220​
TEST
120​
0619
NLG121
130220​
TEST
120​
0619

Apologies if I'm not explaining myself:)

Cheesey
 
Upvote 0
Hi

Thanks for your reply. As requested the example below.

1582207113141.png
 
Upvote 0
OK, so you don't have column headings in row 1. I don't see how the macro only exports the first line (row) because it starts at row 2. To have it loop from row 1 to the last row change the For statement to For r = 1 To .Cells(.Rows.Count, "A").End(xlUp).Row
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,267
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top