MrExcel Publishing
Your One Stop for Excel Tips & Solutions

From Excel to ASCII


Posted by lisa on February 15, 2002 1:41 PM

I am trying to save my excel spreadsheet as an ASCII file. I save it as a fixed width file (.prn) which works on only the first 239 characters. The row is 344
characters long. The remaing characters end up and the bottum of the file groped together. It looks like page preview the way it cuts it off. Please help.


Posted by Dreamboat on February 15, 2002 1:48 PM


I generally suggest opening the Excel file, using Word, and then save the file as MS-dos text.

Posted by Mark W. on February 15, 2002 1:51 PM

What are you trying to accomplish by saving in this format?

Posted by Russell Hauf on February 15, 2002 1:56 PM

Re: What are you trying to accomplish by saving in this format?

Try saving it as Text (MS-Dos). It worked for me.

-rh

Posted by Lisa on February 15, 2002 2:06 PM

Re: What are you trying to accomplish by saving in this format?

When I save it that way it moves the fields closer together and then the file cant't be read. This file has a spacing positions. I save each colunm to a specified width.

Posted by lisa on February 15, 2002 2:11 PM

Once the file is created in Excel I have to save it and when I save it as a txt MS-dos it moves the information over and the file can no longer be read. It changes the spacing.

Posted by Mark W. on February 15, 2002 2:17 PM

I ask again... What are you trying to accomplish by saving in this format?

Posted by Lisa on February 15, 2002 2:27 PM

Re: I ask again... What are you trying to accomplish by saving in this format?

The file will be exported to a 401k company and it has to be in the exact spacing and format so their system can read the information. For example the first field has to be 9 characters then field 2 has to be 9 blank characters then field 3 so on and so on. Where the blank fields have to be the information is shifted over that area.

Posted by Mark W. on February 15, 2002 2:33 PM

See...

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q131554

Posted by mkk on February 16, 2002 6:56 AM

Lisa,

If you are not adverse to running a macro, the following code may help. Paste it into a module of the workbook with the sheet you are trying to save, modify the variables in Sub Test() to suit your your needs, then run TEST.

To align things as you see them in Excel, you'll need to make sure all the columns formatted as TEXT. You'll also need to supply spaces in blank cells as well to fill gaps between cells.

Good luck.

Option Explicit
Sub Test()

'Lisa - set the next 6 variable to what you need, then run test.

Const sFilePathAndName = "C:\TEST.TXT"
Const sWorksheetToExport = "Sheet1"
Const nFirstRowOfDataToPrint = 1
Const nLastRowOfDataToPrint = 20
Const nFirstColumnOfDataToPrint = 1
Const nLastColumnOfDataToPrint = 255

Call SaveWorksheetAsTextFile(sFilePathAndName, _
sWorksheetToExport, _
nFirstRowOfDataToPrint, _
nLastRowOfDataToPrint, _
nFirstColumnOfDataToPrint, _
nLastColumnOfDataToPrint)

End Sub
Sub SaveWorksheetAsTextFile(ByVal psFilePathAndName As String, _
ByVal psWorksheetToExport As String, _
ByVal pnFirstRowOfDataToPrint As Long, _
ByVal pnLastRowOfDataToPrint As Long, _
ByVal pnFirstColOfDataToPrint As Integer, _
ByVal pnLastColOfDataToPrint As Integer)

Dim nFileHandle As Integer
Dim sOutputString As String
Dim nRowCounter As Long
Dim nColCounter As Long

nFileHandle = FreeFile

Open psFilePathAndName For Output As #nFileHandle

With ActiveWorkbook.Worksheets(psWorksheetToExport)

For nRowCounter = pnFirstRowOfDataToPrint To pnLastRowOfDataToPrint
sOutputString = ""
For nColCounter = pnFirstColOfDataToPrint To pnLastColOfDataToPrint
sOutputString = sOutputString _
& .Cells(nRowCounter, nColCounter).Value
Next
Print #nFileHandle, sOutputString
Next
End With
Close #nFileHandle

End Sub

Posted by mkk again on February 16, 2002 7:06 AM

Lisa,
Here is the code again, with indentation hopefully fixed.
mike


Sub Test()

Const sFilePathAndName = "C:\TEST.TXT"
Const sWorksheetToExport = "Sheet1"
Const nFirstRowOfDataToPrint = 1
Const nLastRowOfDataToPrint = 20
Const nFirstColumnOfDataToPrint = 1
Const nLastColumnOfDataToPrint = 255

Call SaveWorksheetAsTextFile(sFilePathAndName, _
sWorksheetToExport, _
nFirstRowOfDataToPrint, _
nLastRowOfDataToPrint, _
nFirstColumnOfDataToPrint, _
nLastColumnOfDataToPrint)
End Sub

Sub SaveWorksheetAsTextFile(ByVal psFilePathAndName As String, _
ByVal psWorksheetToExport As String, _
ByVal pnFirstRowOfDataToPrint As Long, _
ByVal pnLastRowOfDataToPrint As Long, _
ByVal pnFirstColOfDataToPrint As Integer, _
ByVal pnLastColOfDataToPrint As Integer)

Dim nFileHandle As Integer
Dim sOutputString As String
Dim nRowCounter As Long
Dim nColCounter As Long

nFileHandle = FreeFile

Open psFilePathAndName For Output As #nFileHandle

With ActiveWorkbook.Worksheets(psWorksheetToExport)
For nRowCounter = pnFirstRowOfDataToPrint To pnLastRowOfDataToPrint
sOutputString = ""
For nColCounter = pnFirstColOfDataToPrint To pnLastColOfDataToPrint
sOutputString = sOutputString _
& .Cells(nRowCounter, nColCounter).Value
Next
Print #nFileHandle, sOutputString
Next
End With
Close #nFileHandle
End Sub

Posted by lisa on February 20, 2002 12:30 PM

I'm having a problem with the code. I don't know if it from something I have to change that is specific to my worksheet.
I keep getting a runtime error "1004"
Application-defined or object-defined error.

when I hit debug it highlights
sOutputString = sOutputString _
& .Cells(nRowCounter, nColCounter).Value

I'm not that great with VB

Thanks
Lisa