Convert Excel Spreadsheet into Fixed Character Width Data Se

[lui]

Hello,

I'm trying to convert my excel spreadsheet into a fixed character width data set. That is, column 1 should be 6 characters long, column 2 should be 20 characters long, etc. Can anybody shed some light into this? Much thanks.

 

[Yogi Anand]

Hi William:

Welcome to the Board!. I read that you want to have your data in a spreadsheet that has two columns, column A should be 6 characters wide, and column B should be 20 characters wide. What is your data -- do you already have some data; does it still have to be created; do you have it on paper.

It would be helpful to post as much information as you can provide. Also, the more specific your question, the better the chances of your getting meaningful help.

Regards!

Yogi
This message was edited by Yogi Anand on 2002-08-23 17:14

 

[lui]

Thanks for the message Yogi.

Currently I have an excel spreadsheet with six columns of data. Three columns are formatted in text and the other three in numbers. My objective is to save/export the data into a fixed width ascii format. The first three columns will be six characters long and the other three twenty. An example is shown below.

[6 spaces][6 spaces][6 spaces][20 spaces]...

I know how to export the excel data into tab-delimited format, but exporting/saving into a fixed-width ascii format is another issue. Thanks very much for your help. This is a great forum!

Best Regards,

William Lui

 

[Legacy 98055]

Hi
Not too sure on exactly what you want your output to be? Continuous or with returns?
The following macro would take the example data from the worksheet below and create a textfile such as is listed below this spreadsheet. You may need to adjust the starting row. The rows which will be sent to the textfile are determined by column A and the data it contains or does not. If there are blank cells in between the first row of data to the last, in column A, then note this fact in a repy and someone will edit the code if you are unable.

FixedSave.xls
	A	B	C	D	E	F	G
1	123	123	123	abc	abc	abc
2	123	123	123	abc	abc	abc
3	123	123	123	abc	abc	abc
4
5
Sheet1

<pre>
Sub ToText()
Dim f As Integer, fileToSave, r As Long
Dim abc(1 To 3) As String * 6, def(4 To 6) As String * 20
fileToSave = Application.GetSaveAsFilename
r = 1 'first row
f = FreeFile
Open fileToSave For Output As #f
Do
abc(1) = Cells(r, 1).Value: def(4) = Cells(r, 4).Value
abc(2) = Cells(r, 2).Value: def(5) = Cells(r, 5).Value
abc(3) = Cells(r, 3).Value: def(6) = Cells(r, 6).Value
Print #f, abc(1) & abc(2) & abc(3) & def(4) & def(5) & def(6)
r = r + 1
Loop While Not Cells(r, 1).Value = ""
Close #f
End Sub</pre>
Tom


PS. I forgot this. The textfile created from the above...<pre>
123 123 123 abc abc abc
123 123 123 abc abc abc
123 123 123 abc abc abc</pre>
This message was edited by TsTom on 2002-08-24 17:34

 

[lui]

Thanks Tom for the reply. Your script worked great with my data. I will let the forum know if I run into any problems. Thanks again.

Will