Excel to .txt file but with predetermened text length

Rash981

New member
Joined
Sep 15, 2018
Messages
2
Reaction score
0
Points
0
Excel Version(s)
2010
HI everyone,
for a while i am struggling to find solution for below described issue. Any help i would appreciate
I need to create .txt file from excel with predetermined length of fields which will be later used for upload to my local bank web site.
So for example in .txt file from 1-18 field should be bank account, from 19 to 53 field should be name of beneficiary, from 54 to 63 city etc etc...
Attached is sample of wanted .txt file as well as settings.
Thanks for any help:smile:
Rash
 

Attachments

  • txt set up.docx
    17.6 KB · Views: 8
  • Domestic file example - New version.txt
    1,002 bytes · Views: 9
The following macro exports all worksheets in a workbook to fixed-width files. The code is commented for use in either of two modes, to determine the columns widths based on the:
• text width in Row 1; or
• value in Row 1
in the worksheet being exported. The second version also allows for left/right alignments to be set on the basis on a value in row 2.
Code:
Sub TextFileExport()
'The next row tells Excel where to save the output. Modify as needed, keeping the trailing backslash.
Const FilePath = "C:\"
Dim WkSht As Worksheet, ff As Long
Dim CurrentRow As Long, CurrentCol As Long, MaxRow As Long, MaxCol As Long
Dim strOutput As String
'Loop through all worksheets.
For Each WkSht In ActiveWorkbook.Worksheets
  ff = FreeFile
  'Open a text file using the current worksheet's name in the nominated path.
  Open FilePath & WkSht.Name & ".txt" For Output As #ff
  MaxRow = WkSht.Range("A65536").End(xlUp).Row
  MaxCol = WkSht.Range("IV1").End(xlToLeft).Column
  'The next code line determines the start & end rows. If using the row 1 to hold the column widths
  'and row 2 to hold alignment properties, start at row 3. Otherwise start at row 1.
  For CurrentRow = 3 To MaxRow
    strOutput = ""
    'The next line determines the start & end columns.
    For CurrentCol = 1 To MaxCol
      'The next 7 code lines use the value in row 1 to determine column widths and
      'the value (if any) in row 2 to determine alignments.
      If Left(Trim(UCase(WkSht.Cells(2, CurrentCol))), 1) = "R" Then
        strOutput = strOutput & Right(Space(255) & WkSht.Cells(CurrentRow, CurrentCol), _
        WkSht.Cells(1, CurrentCol))
      Else
        strOutput = strOutput & Left(WkSht.Cells(CurrentRow, CurrentCol) & Space(255), _
        WkSht.Cells(1, CurrentCol))
      End If
      'The next two code lines use the text width in row 1 to determine column widths and
      'adds a space between them.
      'Delete the '1+' if the extra space isn't needed
      'strOutput = strOutput & Left(WkSht.Cells(CurrentRow, CurrentCol) & Space(255), _
      '1 + Len(WkSht.Cells(1, CurrentCol)))
      'Uncomment the next line to add a pipe separator
      'If CurrentCol < MaxCol Then strOutput = strOutput & "|"
    Next CurrentCol
    'Write the line to the file.
    Print #ff, strOutput
  Next CurrentRow
  'Close the file.
  Close #ff
Next WkSht
Set WkSht = Nothing
End Sub
 
THanks Paul,
this looks helpful. However I am not really VBA expert but I hope will crack this.
Rasa
 
You'd better post the Excel file that has to be converted.
 
Back
Top