Macro adding extra space to space delimited file

[sellis]

I found a great macro on Microsoft's website to get me from an Excel 2007 file to a space delimited file, but I am having one problem. The macro is not allowing there to be no space between columns. For example, by first three columns are MEM, R, 51246. They should read MEMR51246 once the file processes however the macro is putting a space between them. It is like it is not allowing column width - text to = 0. Below is the applicable portion of the macro (I think - macro is realllllly long). If anyone can help with this or has a suggestion for an extra line to add, I would really appreciate it.
Loop through every cell, from left to right and top to bottom.
For RowNum = 1 To TotalRows
For ColNum = 1 To TotalCols
With Selection.Cells(RowNum, ColNum)
ColWidth=Application.RoundUp(.ColumnWidth, 0)
' Store the current cells contents to a variable.
Select Case .HorizontalAlignment
Case xlRight
CellText = Space(Abs(ColWidth - Len(.Text))) & .Text
Case xlCenter
CellText = Space(Abs(ColWidth - Len(.Text))/2) & .Text & _
Space(Abs(ColWidth - Len(.Text))/2)
Loop through every cell, from left to right and top to bottom.
For RowNum = 1 To TotalRows
For ColNum = 1 To TotalCols
With Selection.Cells(RowNum, ColNum)
ColWidth=Application.RoundUp(.ColumnWidth, 0)
' Store the current cells contents to a variable.
Select Case .HorizontalAlignment
Case xlRight
CellText = Space(Abs(ColWidth - Len(.Text))) & .Text
Case xlCenter
CellText = Space(Abs(ColWidth - Len(.Text))/2) & .Text & _
Space(Abs(ColWidth - Len(.Text))/2)
Case Else
CellText = .Text & Space(Abs(ColWidth - Len(.Text)))
End Select
End With
Case Else
CellText = .Text & Space(Abs(ColWidth - Len(.Text)))
End Select
End With

 

[p45cal]

The code you've provided deals only with the text in a single cell, there's nothing there to show whether they're being concatenated before being added to the destination file (in space-delimited file, each value has to be separated by a space, by definition!)
You may also come across problems if you're using a proportional font - how are the column widths set? By using whole numbers, or by autowidth?
If the destination file is going to be a fixed width file, you'll know what each field length should be - is this the case?
Can you provide a link to the original Microsoft code offering?
How is the file finally put together?
Otherwise, things to check out are:
TRIM (in case people have put extra spaces either side of the text)
LSET and RSET.

 

[sellis]

Here is the Microsoft weblink. http://support.microsoft.com/kb/249885 I have done what it says and gone with the fixed width font of courier. I have set my width to a specified width as given to me by the company I am preparing my file for, and no, I do not believe that space delimited files have to have a space between each column or else I am naming it incorrectly. Lets call it fixed width using space separators if you want. What I need is for a column to be 30 characters whether the column has 3 text characters, 35 text characters or 0 text characters. The file can either be a prn or txt. The problem I have found with the prn is that it only likes the first 240 characters, and I do not really have a program that likes to look at it. I am trying to do it to a txt file and open it in wordpad or something so I can see it before I send it over. The file ends up being 650 characters in length with any variations or characters, numbers and some normal characters (&,.#) I think those would be about it. Thanks

 

[p45cal]

I do not believe that space delimited files have to have a space between each column or else I am naming it incorrectly.

You are.

Lets call it fixed width using space separators if you want.

rather:Lets call it fixed width using no separators.
So try removing & delimiter from
CellText = CellText & delimiter
(assuming you don't want quotes, if so remove the same from the line 2 lines above too.)

An alternative fix it to change the delimiter line:
delimiter = " "
to:
delimiter = ""

Note, that if the column widths required remain the same all the time, then you can use LSET and RSET to help get the resultant strings to the right length, then you wouldn't need to worry abount fonts, column widths and the like.

 

[sellis]

I tried removing the & delimiter, and it still has spaces. My techs have a temporary work around, so if you wish to give up on me you can. Otherwise I would love more suggestions.

 

[p45cal]

Post sample data sheet and required column widths on the interweb somewhere, say box.net, skydrive, wikisend, whatever.

 

[sellis]

Will do, and I will send another message once done. Thanks for everything so far.

 

[p45cal]

Will do, and I will send another message once done. Thanks for everything so far.

given up?

 

[sellis]

No, just having to work on another project. It will be a couple days before I can get back to this. Sorry to make you wait on me.