save as PRN

Posted by CWW on September 22, 2000 2:16 PM

I have a .XLS file. It's not anything unusual. It contains 5 or 6 columns ranging from 3 characters to 40 characters.

If I click File, SaveAs, and then select Formatted Text (space delimited .PRN) it saves the file fine putting all of the columns into column A. This is the way I want it.

The problem is, in my macro if I write for it to save as a .PRN file it saves the file as a .PRN but does not move all of the data into a space delimited column A.

Any help with this would be greatly appreciated.

Posted by Tim Francis-Wright on September 25, 2000 11:45 AM

As long as you use the following statement--
ActiveWorkbook.SaveAs FileFormat:=xlTextPrinter, CreateBackup:=False
--in your code, Excel will save the file as
a space-delimited file. (You can include
a FileName:= clause to specify the name.

.PRN files don't have column formatting
already specified. Excel makes assumptions
about where it should break the text
into columns (and a user can override those
assumptions as part of the process of converting
a .PRN file to an .XLS file).

If you are automating the opening of a file,
Workbooks.OpenText FileName:="foo.prn", _
Origin:=xlWindows, _
StartRow:=1, DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 1)
will open foo.prn as a one-column spreadsheet.

HTH