From Excel to ASCII

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

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

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

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

-rh

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.

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.

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

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.

See...

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

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

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

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