Comma Delimited Export

[Mike Kiekover]

I use the following macro to export data in a comma delimeted fashion as
opposed to save as .csv due to necessary quotation marks in the data cells.
This works great, except that I have to do a SAVE AS on the file before the
macro will function. Just a SAVE doesn't do it either. Once I SAVE AS to the
same name, the macro works for as long as I have the file open. But if I
close Excel and re-open the macro doesn't populate the destination file. I
tested the macro on a blank workbook as well with the same results, so it
doesn't seem to be file related.

Can anyone see anything that would change this, and allow me to open the
file and run the macro without the "save as" step.

**************
Public Sub TextNoModification()
Const DELIMITER As String = "," 'or "|", vbTab, etc.
Dim myRecord As Range
Dim myField As Range
Dim nFileNum As Long
Dim sOut As String

nFileNum = FreeFile
Open "isogen_atts.att" For Output As #nFileNum
For Each myRecord In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
With myRecord
For Each myField In Range(.Cells(1), _
Cells(.Row, Columns.Count).End(xlToLeft))
sOut = sOut & DELIMITER & myField.Text
Next myField
Print #nFileNum, Mid(sOut, 2)
sOut = Empty
End With
Next myRecord
Close #nFileNum
End Sub
***************

 

[Mike Kiekover]

One thing that does work for me is to add the "Save-As" in the macro.
However, this file will be used as a template and saved with different names
and paths. Is there a way to change the path in the following string to
force a "Save-As" to the current file name and path.

************
ActiveWorkbook.SaveAs Filename:= _
"Q:\COP\06-243 FCC Energy Recovery Detailed\ENGINEER\Lists\PN
2005044 Piping Line List Rev 0.xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
************

 

[Earl Kiosterud]

Mike,

Try the Text Write Program at http://www.smokeylake.com/excel/text_write_program.htm