Cell Padding Help

    Hello,


    This feels like a really stupid problem, but I have yet been able to find a solution. I'm working with a textfile that contains 6 columns of information and basically I need to import the text file into excel, modify column B which is a column of dollar amounts and then save the file as a text again in order for it to be used with Oracle financial it must maintain the same format.


    However when I save this file to text it does not maintain the formatting and the columns are all mixed together or the spacing has changed and I cannot go through line by line (there are a few thousand rows) to correct this.


    I believe this is happening because some of the columns contain data of different lengths (description or verification for example range from 20 to 50 characters) however I have been unsuccessful in my attempts to pad these all up to the same width using spaces.


    Can anyone possibly provide a better solution with this little amount of information?


    Thanks!

    Re: Cell Padding Help


    Given the information you have supplied (which I assume is limited by data confidentiality) I can think of a couple of possible solutions:

    1) Use VBA to take each cell, pad it to the required length to meet the required format and then write these padded values out directly to the text file.
    2) You may be able to use the xlTextPrinter output format

    Code
    ActiveWorkbook.SaveAs FileName:= _
FileName, _
FileFormat:=xlTextPrinter, CreateBackup:=False


    You need to be aware though that your column widths will define your padded field sizes so you may need to experiment with that before you get what you need.

    Re: Cell Padding Help


    Thanks for the quick reply. It seems like this may be suitable if I can manage to get the spacing exact enough. I'll experiment and post another reply.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login