Saving xls file as fixed width

**MissMac** · 02-22-2010, 06:43 AM

I am trying to created test data files. The data is currently in Excel and the widths of the columns have been set to that of the fields within a database. I want to save the Excel file so that the widths of the columns remain. I have tried saving as .prn and this works for some files however .prn is limited to 240 characters per line so any files which have more than this automatically wrap the columns. This is no good as when the file is read the columns are counted therefore the load gets confused.
I have tried saving as .csv and getting rid of the commas however this does not save the column widths.

How can I save the file so that the widths are saved to some sort of text file?
Other info - ultimately I need to change the file so that it is .dat and this is simple enough by changing the file extension.

Thanks in advance

**Andy Pope** · 02-22-2010, 07:39 AM

Given the usual way of setting column widths and saving to prn does not work you will have to use VBA to write the file.

**Andy Pope** · 02-22-2010, 07:50 AM

Given the usual way of setting column widths and saving to prn does not work you will have to use VBA to wrhttp://www.exceltip.com/exceltips.phpite the file.

Here is some code to get you started.

Please Login or Register  to view this content.

**MissMac** · 02-23-2010, 06:00 AM

Thanks for your suggestion Andy. I managed to find a simple macro, details below, yesterday which worked a treat after I edited it to suit my data.

Thanks again,
Lisa

https://www.blogger.com/comment.g?bl...12465133508496