Excel to space delimited txt file with 900 characters

[ginny777]

Hi! I am a newbie and have searched for an answer to my question on the messages boards, but don't see one that quite fits.

I will be receiving a file in Excel which contains a column for the first name, last name, a date and an email address (it could possibly have more - street address, etc.- but this is the norm). This file will need to be turned into a space delimited .txt file with no tabs. The text file will need 42 "columns" each a specific length for a total of 900 characters in each row. A lot of the columns will be blank, but the spaces must be there, and some of the columns will need to be populated with the same default data in each cell.

Some help with this would be greatly appreciated, right now I'm doing this by hand, but we will have thousands in upcoming files, and it will be too time consuming!

Extra help - one of the columns will need to be a ascending number sequence - 11 characters and prepadded with zeros (00000000001,00000000002 etc.), and the date column will need to be in CCYY-MM-DD (2011-04-08).

Ginny

 

[ginny777]

open the visual basic editor and put it in under sheet one or this workbook?

 

[RSXchin]

actually, you'd be better off putting in in your PERSONAL MACRO WORKBOOK.
Google around, I'd explain it better, but it's date night and my wife is glaring at me...

 

[ginny777]

Thank you! I will let you know tomorrow how it goes.

Have a great night, and tell your wife thank you also.

 

[RSXchin]

How did it go?

 

[ginny777]

.

 

[ginny777]

Hi, sorry I did not get back to you earlier. I had some internet connection issues, that's what happens when you live in the country.

Anyway, it does not work yet. It has a compile error. "User defined type not defined" at - oFS As FileSystemObject.

Thanks for checking in,
Ginny

 

[shg]

In the VBE, Tools > References, find and tick Microsoft Scripting Runtime.

 

[ginny777]

Thanks, that got me passed that point. Now I get a run-time error '1004': AutoFill method of Range class failed.

 

[RSXchin]

delete by user

 

[ginny777]

I get the same error.