How to print to a csv file?

[Nathan Gutman]

I want to export a print area to a csv file but when I use the Print
to File option it creates a .prn file which has not resemblance to
anything readable. I looks that it uses a current printer to do that
because changing printers creates different files..
I want to take the designated print area and print/save that to a csv
file.
I know that I can use the Save As option but this saves the whole
worksheet not just the print area.
Thanks for any help.
Nathan

 

[JE McGimpsey]

one way:

Public Sub SavePrintAreaToCSV()
Const DELIMITER As String = ","
Dim rPrint As Range
Dim rRecord As Range
Dim rField As Range
Dim sOut As String

Open "Test.txt" For Output As #1
With ActiveSheet
On Error Resume Next
Set rPrint = Range(.PageSetup.PrintArea)
On Error GoTo 0
If rPrint Is Nothing Then
If Application.CountA(.Cells) = 0 Then
MsgBox "Nothing to Save"
Exit Sub
Else
Set rPrint = .UsedRange
End If
End If
End With
For Each rRecord In rPrint.Rows
With rRecord
For Each rField In Range(.Cells, _
Cells(.Row, Columns.Count).End(xlToLeft))
sOut = sOut & DELIMITER & rField.Text
Next rField
Print #1, Mid(sOut, 2)
sOut = Empty
End With
Next rRecord
Close #1
End Sub


Note that if the print area is not set and the sheet is populated, the
entire UsedRange is saved to CSV. Change the path and filename for
Test.txt to suit.

 

[Dave Peterson]

Another option is to copy that range you want to save to a worksheet in a new
workbook.

Then save it as .csv from there.

 

[Earl Kiosterud]

Nathan,

The Text Write Program at www.tushar-mehta.com can write a selected area to
a text file.

 

[Nathan Gutman]

Thank you all... now back to work.