Below is a macro that will export the table starting in cell A1 to a file named C:\Excel\Scala
Export.prn.

You just need to change the widths array size and values to suit your needs.

HTH,
Bernie
MS Excel MVP


Sub ExportToPRN()

Dim fName As String
Dim WholeLine As String
Dim FNum As Integer
Dim RowNdx As Long
Dim ColNdx As Integer
Dim StartRow As Long
Dim EndRow As Long
Dim StartCol As Integer
Dim EndCol As Integer
Dim Widths(1 To 4) As Integer

Widths(1) = 8
Widths(2) = 9
Widths(3) = 10
Widths(4) = 10

fName = "C:\Excel\Scala Export.prn"

Application.ScreenUpdating = False
On Error GoTo EndMacro:
FNum = FreeFile


With Range("A1").CurrentRegion 'Change A1 to upper left cell of range
StartRow = .Cells(1).Row
StartCol = .Cells(1).Column
EndRow = .Cells(.Cells.Count).Row
EndCol = .Cells(.Cells.Count).Column
End With

Open fName For Output Access Write As #FNum

For RowNdx = StartRow To EndRow
WholeLine = ""
For ColNdx = StartCol To EndCol
WholeLine = WholeLine & Left(Cells(RowNdx, ColNdx).Text & _
Application.WorksheetFunction.Rept(" ", Widths(ColNdx)), Widths(ColNdx))
Next ColNdx
Print #FNum, WholeLine
Next RowNdx

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #FNum

End Sub



"Mikael Lindqvist" wrote in message
...
Hi,

I have a basic excel file with a few columns with data.

Now I need to import this data to my financial system (iScala).

It seems iScala can only import textfile where each value starts at a
predefined position; hence it does not support "tab" or "space" delimited
values.

Does anyone know how to create a text file where all records are
"surrounded" by space and each record starts (at the same) predefined
position?

Example:

3 columns (A, B, C):

Name1 071001 452
Name123 071201 1200
Nam1 070905 51

Notice that "date" (column B) always start at position 9 and column C at
position 18... How to make this from a excel-file?

Kindly,
Mikael
Sweden