fixed length strings

[Hap]

I am trying to read/write and address value to a text file that is space delimited. I assume there is a good approach using fixed length strings but I haven't figured it out yet. What is the best way to write to the file that makes it easier to read. And as a result, how to read it?

Thank you

 

[Scott Huish]

Where do you want the spaces? Before the text or after?
You can open the text file in any app that supports text files such as Notepad or did you want a macro to read it back into Excel or what?

 

[Joe4]

If you do a File SaveAs and select the Formatted Test (Space delimited) (*.prn) option, it will save your file as a space delimited text file (which is usually pretty easy to read using NotePad or any other text editor).

 

[Hap]

I'm actually writing the text file with something like this:

Print #1, object.address1 & " " & object.address2 & " " & object.city & " " & object.state & " " & object.zip

I was reading the file by getting the line of text and then using the split function. Problem is spaces in the address text. I temporarily changed the delimiter for this particular line to "$$" but I think there is a better way to do this.

 

[Scott Huish]

I would read it in using the Workbooks.OpenText Method (see VBA help file)

Why are you using space as the delimiter anyway?

If it's for readability you could use Chr(160) as your delimiter instead.

 

[Hap]

Maybe I should have mentioned that the program has to be compatible with xl2000. Also, the file that I am writing the text for is used by another program that reads a space delimiter. That program somehow encapulates the field with quotations and reads a fixed length string. That was why my question was worded as such.

Thank you

 

[Armando Montes]

Perhaps:
http://www.databison.com/index.php/export-excel-as-fixed-width-text-file-format-using-vba/