Exporting a space delimited file from Excel

[KGee]

I have a custom program that reads data from a space delimited (padded?) text file and performs a number of tasks for us, such as importing the data into our system. Starting next month, I will be receiving a second file from a separate system and need to consolidate the two files into one before running our program. I've managed to do that with macros and code I've pulled from the forum so I'm all set on that piece. My problem is I need to export the results using the same space specifications as the source files or else our program wont be able to process the file.

Can this be done from Excel? The specs from the source file are shown below with the column or field name and the starting and ending position for each field. Each line is 132 characters long with a carriage return/line feed at the end. Spaces need to be padded to the right of the value for each field. For example, the ITEM is always 10 characters long and need to be padded with a space after the field value. The TYPE is always one character and needs a space after each value. The VAL is either one or two characters and must be padded accordingly with either 4 or 5 spaces up to the 19th character. And so one for the remaining fields.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]ITEM[/TD]
[TD]TYPE[/TD]
[TD]VAL[/TD]
[TD]$[/TD]
[TD]QTY[/TD]
[TD]DESC[/TD]
[/TR]
[TR]
[TD]Start[/TD]
[TD]1[/TD]
[TD]12[/TD]
[TD]14[/TD]
[TD]20[/TD]
[TD]33[/TD]
[TD]41[/TD]
[/TR]
[TR]
[TD]End[/TD]
[TD]11[/TD]
[TD]13[/TD]
[TD]19[/TD]
[TD]32[/TD]
[TD]40[/TD]
[TD]132[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

Earlier this morning I updated the code to select all six columns and formatted them as text with courier new as the font. I saved the output as formatted text and could have sworn the output looked fine. I had to make a few more changes just now so I modified the code and when I looked at the output I noticed the overall length varied on each line. The sixth field is the description and no longer has the padding after the description, the line just ends after the last character.

Just wondering if you saw the code I posted in Message #8 yet (you do not have to do anything with formatting or font selection using it)?

 

[KGee]

Just wondering if you saw the code I posted in Message #8 yet (you do not have to do anything with formatting or font selection using it)?

So I removed those steps from the code and added Rick's code which I tested successfully several times.

Hi Rick I actually did end up using your code and it works great . I was just curious as I thought I had the results I needed by doing the formatted text export but it seems I was mistaken and it never padded the final column. I don't plan on changing anything as it works as I needed. Thanks again