Excel

Creating a fixed width textfile from a worksheet

Ease of Use

Intermediate

Version tested with

2000, 2002, 2003 

Submitted by:

mark007

Description:

The code will create a fixed width textfile from an excel worksheet. 

Discussion:

Sometimes we might want to generate a fixed width text file from an excel worksheet - for example to be an input into some legacy application. The following procedure will accomplish this having been passed the filename, worksheet to apply it to and a zero-based array of the fixed widths. 

Code:

instructions for use

			

Option Explicit Sub CreateFixedWidthFile(strFile As String, ws As Worksheet, s() As Integer) Dim i As Long, j As Long Dim strLine As String, strCell As String 'get a freefile Dim fNum As Long fNum = FreeFile 'open the textfile Open strFile For Output As fNum 'loop from first to last row 'use 2 rather than 1 to ignore header row For i = 1 To ws.Range("a65536").End(xlUp).Row 'new line strLine = "" 'loop through each field For j = 0 To UBound(s) 'make sure we only take chars up to length of field (may want to output some sort of error if it is longer than field) strCell = Left$(ws.Cells(i, j + 1).Value, s(j)) 'add on string of spaces with length equal to the difference in length between field length and value length strLine = strLine & strCell & String$(s(j) - Len(strCell), Chr$(32)) Next j 'write the line to the file Print #fNum, strLine Next i 'close the file Close #fNum End Sub 'for example the code could be called using: Sub CreateFile() Dim sPath As String sPath = Application.GetSaveAsFilename("", "Text Files,*.txt") If LCase$(sPath) = "false" Then Exit Sub 'specify the widths of our fields 'the number of columns is the number specified in the line below +1 Dim s(6) As Integer 'starting at 0 specify the width of each column s(0) = 21 s(1) = 9 s(2) = 15 s(3) = 11 s(4) = 12 s(5) = 10 s(6) = 186 'for example to use 3 columns with field of length 5, 10 and 15 you would use: 'dim s(2) as Integer 's(0)=5 's(1)=10 's(2)=15 'write to file the data from the activesheet CreateFixedWidthFile sPath, ActiveSheet, s End Sub

How to use:

  1. Copy the code above.
  2. Open Excel.
  3. Alt + F11 to open the Visual Basic Editor.
  4. Click Insert-Module from the menu.
  5. Paste the code into the window that appears at right.
  6. Adjust the second procedure to specify the fixed widths to use and sheet that should be used to create the file
  7. Close the VBE (Alt + Q or press the x in the top right corner).
 

Test the code:

  1. Goto Tools-Macros-Macros and run the macro CreateFile.
 

Sample File:

sample.zip 9.55KB 

Approved by mdmackillop


This entry has been viewed 293 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express