Using Excel To Export A Text File With Fixed Columns And No Tabs
The example below shows how to format and export a text file with fixed column widths. The text file is built in a separate worksheet and then exported as text. Since there is only one column in the formatted data (Column A), the export will not include any tab characters and is suitable for applications that require lines of text with no tab characters followed by a carriage return and line feed.
Program Code
Option Explicit
Public Sub CreateTextExport()
' *************************************************************
' Written by Rich Locus, Logicwurks, LLC
' www.logicwurks.com 7/23/2012
' *************************************************************
' *************************************************************
' Define Workbook and Worksheet Variables
' *************************************************************
Dim wkbBarcodeData As Workbook
Dim wksBarcodeData As Worksheet
Dim wksWorkArea As Worksheet
' *************************************************************
' Define Other Variables
' *************************************************************
Dim lngNumberOfDataRows As Long
Dim lngNumberOfColumnsInCurrentRow As Long
Dim lngCurrentFormattedRow As Long
Dim strFormattedTextLine As String
Dim strFileNameToSave As Variant
Dim lngCurrentRow As Long
Dim lngCurrentCol As Long
lngCurrentFormattedRow = 0
' *************************************************************
' Turn Off Screen Updating
' *************************************************************
Application.ScreenUpdating = False
' *************************************************************
' Initialize Workbook and Worksheet Variables
' *************************************************************
On Error GoTo MissingWorkSheet
Set wkbBarcodeData = ActiveWorkbook
Set wksBarcodeData = wkbBarcodeData.Sheets("DATA")
Set wksWorkArea = wkbBarcodeData.Sheets("WorkArea")
' *************************************************************
' Reset Standard Error Checking
' *************************************************************
On Error GoTo 0
' *************************************************************
' Allow Hidden Worksheet To Be Viewed
' *************************************************************
wksWorkArea.Visible = True
' *************************************************************
' Validate Row Position in the Data Area
' *************************************************************
If wksBarcodeData.Cells(2, 1).Value <> "barcode" Then
MsgBox ("The Caption 'barcode' Must Be In Row 2, Column A" & _
vbCrLf & "Of the DATA Worksheet")
wksWorkArea.Visible = False
Application.ScreenUpdating = True
Exit Sub
End If
' *************************************************************
' Set The Barcode Column To Text To Prevent Zeroes From
' Being Eliminated
' *************************************************************
wksBarcodeData.Columns(1).NumberFormat = "@"
' *************************************************************
' Remove Any Data From The WorkArea And Format As Text
' *************************************************************
With wksWorkArea
.Cells.Delete Shift:=xlUp
.Columns("A:A").NumberFormat = "@"
.Columns("A:A").ColumnWidth = 46
End With
' *************************************************************
' Determine The Number of Data Rows
' *************************************************************
lngNumberOfDataRows = wksBarcodeData.Cells(Rows.Count, "A").End(xlUp).Row
' *************************************************************
' Process Each Barcode Row
' *************************************************************
For lngCurrentRow = 3 To lngNumberOfDataRows
' *************************************************************
' Determine The Number of Data Columns In Current Row
' *************************************************************
lngNumberOfColumnsInCurrentRow = wksBarcodeData.Cells(lngCurrentRow, Columns.Count).End(xlToLeft).Column
' *************************************************************
' Create The Formatted Text String For Each Column In The
' Barcode Row
' *************************************************************
For lngCurrentCol = 2 To lngNumberOfColumnsInCurrentRow
If wksBarcodeData.Cells(lngCurrentRow, lngCurrentCol) <> "" Then
strFormattedTextLine = "STT" & _
Format(wksBarcodeData.Cells(2, lngCurrentCol), "0000") & _
"1234567890" & _
Format(wksBarcodeData.Cells(lngCurrentRow, 1), "!@@@@@@@@@@@@@@") & _
Format(wksBarcodeData.Cells(lngCurrentRow, lngCurrentCol), "0000000")
' *************************************************************
' Add The Formatted String To The Workarea Worksheet
' *************************************************************
lngCurrentFormattedRow = lngCurrentFormattedRow + 1
wksWorkArea.Cells(lngCurrentFormattedRow, 1).Value = strFormattedTextLine
End If
Next lngCurrentCol
Next lngCurrentRow
' *************************************************************
' Open A Dialog Box For The User To Save The File
' *************************************************************
strFileNameToSave = Application.GetSaveAsFilename("BarcodeData", _
"Text Files (*.txt), *.txt", , "Save Barcode Text Data")
' *************************************************************
' Make Sure A Valid Name and Path Were Selected
' *************************************************************
If strFileNameToSave = False Then
MsgBox ("You Did Not Select A File Name For Export" & vbCrLf & _
"Please Start Over")
wksWorkArea.Visible = False
Application.ScreenUpdating = True
Exit Sub
End If
' *************************************************************
' Save the Filename as .txt
' *************************************************************
wksWorkArea.Select
Application.DisplayAlerts = False
wkbBarcodeData.SaveAs Filename:=strFileNameToSave, _
FileFormat:=xlText, CreateBackup:=False
Application.DisplayAlerts = True
' *************************************************************
' Clean Up And Remove Data From the WorkArea
' *************************************************************
wksBarcodeData.Select
With wksWorkArea
.Cells.Delete Shift:=xlUp
.Columns("A:A").NumberFormat = "@"
.Columns("A:A").ColumnWidth = 46
End With
' *************************************************************
' Let The User Know The File Was Saved
' *************************************************************
MsgBox ("Your Text File, " & strFileNameToSave & vbCrLf & _
"Was Saved - This Excel Application Will Now Close")
wksWorkArea.Visible = False
Application.DisplayAlerts = False
ActiveWorkbook.Close Savechanges:=False
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Exit Sub
' *************************************************************
' Worksheet Missing Errors
' *************************************************************
MissingWorkSheet:
MsgBox ("You Are Missing Worksheet 'DATA' or 'WorkArea'" & _
vbCrLf & "Please Correct This Error")
Err.Clear
wksWorkArea.Visible = False
Application.ScreenUpdating = True
Exit Sub
End Sub