Excel file to fixed width space delimited file

[sellis]

I have an excel file that is 600 characters long that I need in a fixed width file. It has 29 columns and each column needs to be a different spaced delimited width. I was using the program convert XLS, but they want 500 bucks for that thing, and I only use it for that one feature. It seems like there has to be another way to do it. I found a macro for it, but when I run the macro, it gives me an "error." It highlights ActiveSheet.Paste in yellow in the step through of the debugger. Here is the macro I have. That is kind of hard to read, but there it is. Any help trying to work something out would be greatly appreciated here as I do not know that much about macros. Thanks
<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl63 height=20 width=64>Sub MakeFixedWidth()</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 height=20>Dim MyStr As String, FirstRow As Integer, LastRow As Integer, MyRow As Integer</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 height=20>PageName = "Y:\Stephanie\Insurance\VSP\G0009606" & Format(Time, "HHMM") & ".txt" </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 height=20>FirstRow = Range("AC1553").Value </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 height=20>LastRow = FirstRow + Range("D6").Value - 1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 height=20>Open PageName For Output As #1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 height=20>For MyRow = FirstRow To LastRow </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 height=20> MyStr = ""</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 height=20> MyStr = Cells(MyRow, 1).Value & String(3 - Len(Cells(MyRow, 1).Value), " ")</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 height=20> MyStr = MyStr & String(1 - Len(Cells(MyRow, 2).Value), " ") & Cells(MyRow, 2).Value</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 height=20> MyStr = MyStr & " " & Cells(MyRow, 3).Value & String(5 - Len(Cells(MyRow, 3).Value), " ")</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 height=20> MyStr = MyStr & Cells(MyRow, 4).Value & String(30 - Len(Cells(MyRow, 4).Value), " ")</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 height=20> MyStr = MyStr & Cells(MyRow, 5).Value & String(9 - Len(Cells(MyRow, 5).Value), " ")</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 height=20> MyStr = MyStr & Cells(MyRow, 6).Value & String(30 - Len(Cells(MyRow, 6).Value), " ")</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 height=20> MyStr = MyStr & Cells(MyRow, 7).Value & String(30 - Len(Cells(MyRow, 4).Value), " ")</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 height=20> MyStr = MyStr & Cells(MyRow, 8).Value & String(9 - Len(Cells(MyRow, 5).Value), " ")</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 height=20> MyStr = MyStr & Cells(MyRow, 9).Value & String(18 - Len(Cells(MyRow, 6).Value), " ")</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 height=20> MyStr = MyStr & Cells(MyRow, 10).Value & String(12 - Len(Cells(MyRow, 4).Value), " ")</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 height=20> MyStr = MyStr & Cells(MyRow, 11).Value & String(1 - Len(Cells(MyRow, 5).Value), " ")</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 height=20> MyStr = MyStr & Cells(MyRow, 12).Value & String(3 - Len(Cells(MyRow, 6).Value), " ")</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 height=20> MyStr = MyStr & Cells(MyRow, 13).Value & String(1 - Len(Cells(MyRow, 4).Value), " ")</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 height=20> MyStr = MyStr & Cells(MyRow, 14).Value & String(1 - Len(Cells(MyRow, 5).Value), " ")</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 height=20> MyStr = MyStr & Cells(MyRow, 15).Value & String(8 - Len(Cells(MyRow, 6).Value), " ")</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 height=20> MyStr = MyStr & Cells(MyRow, 16).Value & String(8 - Len(Cells(MyRow, 4).Value), " ")</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 height=20> MyStr = MyStr & Cells(MyRow, 17).Value & String(8 - Len(Cells(MyRow, 5).Value), " ")</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 height=20> MyStr = MyStr & Cells(MyRow, 18).Value & String(8 - Len(Cells(MyRow, 6).Value), " ")</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 height=20> MyStr = MyStr & Cells(MyRow, 19).Value & String(30 - Len(Cells(MyRow, 4).Value), " ")</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 height=20> MyStr = MyStr & Cells(MyRow, 20).Value & String(16 - Len(Cells(MyRow, 5).Value), " ")</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 height=20> MyStr = MyStr & Cells(MyRow, 21).Value & String(30 - Len(Cells(MyRow, 6).Value), " ")</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 height=20> MyStr = MyStr & Cells(MyRow, 22).Value & String(30 - Len(Cells(MyRow, 4).Value), " ")</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 height=20> MyStr = MyStr & Cells(MyRow, 23).Value & String(19 - Len(Cells(MyRow, 5).Value), " ")</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 height=20> MyStr = MyStr & Cells(MyRow, 24).Value & String(2 - Len(Cells(MyRow, 6).Value), " ")</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 height=20> MyStr = MyStr & Cells(MyRow, 25).Value & String(10 - Len(Cells(MyRow, 4).Value), " ")</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 height=20> MyStr = MyStr & Cells(MyRow, 26).Value & String(10 - Len(Cells(MyRow, 5).Value), " ")</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 height=20> MyStr = MyStr & Cells(MyRow, 27).Value & String(3 - Len(Cells(MyRow, 6).Value), " ")</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 height=20> MyStr = MyStr & Cells(MyRow, 28).Value & String(50 - Len(Cells(MyRow, 5).Value), " ")</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 height=20> MyStr = MyStr & Cells(MyRow, 29).Value & String(215 - Len(Cells(MyRow, 6).Value), " ")</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 height=20>Print #1, MyStr</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 height=20>Next</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 height=20>Close #1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 height=20>Sheets("Sheet1").Range("G2").ClearContents ' </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 height=20>Sheets("Sheet1").Hyperlinks.Add Range("G2"), PageName</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl63 height=20>End Sub</TD></TR></TBODY></TABLE>

 

[Derek Brown]

You say that it is failing at the Activesheet.Paste line but that does not appear in the code that you posted. It would also be useful to know which version of Excel is being used.
For small files I use three worksheets to give me a single column that can be copy/pasted etc. as required.
Sheet1 would have the field length in Row 1, the heading in Row 2 (for information) and the data would start in Row 3.
Sheet2 would have Row 1 blank, the same headings in Row 2 (for information) and formulas starting in Row 3. The formula in A3 would be:
=LEFT(Sheet1!A3&REPT(" ",Sheet1!A1),Sheet1!A1)
When complete, you would have your 29 columns of data, each of the required fixed length, in Sheet2.
Sheet3 would have the following formula in A3:
=CONCATENATE(Sheet2!A3,Sheet2!B3,Sheet2!C3, ....etc.)

 

[sellis]

I know that I do not have that line of code in there. That is why I do not understand why the program is locking up there. I copied the macro exactly as is. Also, I am using Excel 2007. The only problem I see with using your idea is what about columns that I need completely blank? I mean some of them are filler columns. The end is 265 spaces of blanks. Thanks