SkyKing162
asked on
Exporting Fixed-Width Text From Excel
I'm working with Excel 98 on a Mac. I'm having the darndest time figuring out how to export fixed-width text. The only way I've come across is to get the file into Access and go from there. But I don't have Access, which is a problem. I have Filemaker, but I don't think that can do it either. I'm sure it could be done with a macro, but I think that's beyond my skills in that area. Thanks in advance for the help. ~Sky
weed
Fixed width text? That's a property of the font used. Nothing to do with excel, access, or filemaker. Just export it as text, and change the font to a fixed width typeface.
ASKER
Ok, maybe I wasn't as clear as I should have been. I want to format my Excel spreadsheet such that when I save it as space-delimited and view it with fixed-width font it will appear as a table without any extra formatting. If I have a column in Excel that has a list of names, I want Excel to export each entry from that column with as many characters as the longest entry, adding an appropriate number of blank spaces onto any names that are shorter than the longest name so that every entry contains the same number of characters. Make more sense?
Good luck. The closest youll get is an HTML formatted table.
ASKER
No one out there has a macro that will do this?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That's awesome. Could that be easily modified to handle numbers, too? I come across problems if I format numbers as text, because then I get all the unwanted decimal places.
SkyKing162,
Please post an example of what you have for data, and how you want it to appear.
Brad
Please post an example of what you have for data, and how you want it to appear.
Brad
SkyKing162,
The following macro will pad text with blanks to the right and numbers with blanks to the left. It assumes that all numbers are integers (it ignores digits after the decimal point).
I tested the macro on Excel 2000, so please advise if it doesn't recognize a keyword.
Option Explicit
Sub FixedWidth()
Dim i As Integer, MaxLen As Integer, _
myCell As Range, myColumn As Range, myData As Range, myLen As Integer, temp As String
Set myData = ActiveSheet.UsedRange
For Each myColumn In myData.Columns
MaxLen = 0
For Each myCell In myColumn.Cells
temp = myCell.Value
MaxLen = Application.Max(MaxLen, Len(temp))
Next myCell
For Each myCell In myColumn.Cells
If IsNumeric(myCell) Then
temp = Format(myCell, "#")
myCell.NumberFormat = "@"
myCell = Application.Rept(" ", MaxLen - Len(temp)) & temp
End If
myLen = Len(myCell.Value)
If myLen < MaxLen Then myCell = myCell & Space(MaxLen - myLen)
Next myCell
Next myColumn
End Sub
The following macro will pad text with blanks to the right and numbers with blanks to the left. It assumes that all numbers are integers (it ignores digits after the decimal point).
I tested the macro on Excel 2000, so please advise if it doesn't recognize a keyword.
Option Explicit
Sub FixedWidth()
Dim i As Integer, MaxLen As Integer, _
myCell As Range, myColumn As Range, myData As Range, myLen As Integer, temp As String
Set myData = ActiveSheet.UsedRange
For Each myColumn In myData.Columns
MaxLen = 0
For Each myCell In myColumn.Cells
temp = myCell.Value
MaxLen = Application.Max(MaxLen, Len(temp))
Next myCell
For Each myCell In myColumn.Cells
If IsNumeric(myCell) Then
temp = Format(myCell, "#")
myCell.NumberFormat = "@"
myCell = Application.Rept(" ", MaxLen - Len(temp)) & temp
End If
myLen = Len(myCell.Value)
If myLen < MaxLen Then myCell = myCell & Space(MaxLen - myLen)
Next myCell
Next myColumn
End Sub