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
***************



--
Posted via a free Usenet account from http://www.teranews.com

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
************



"Mike Kiekover" <(E-Mail Removed)> wrote in message
news:45f07112$0$16393$(E-Mail Removed)...
>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
> ***************
>
>
> --
> Posted via a free Usenet account from http://www.teranews.com
>



--
Posted via a free Usenet account from http://www.teranews.com

Mike,

Try the Text Write Program at http://www.smokeylake.com/excel/text_write_program.htm
--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"Mike Kiekover" <(E-Mail Removed)> wrote in message
news:45f07112$0$16393$(E-Mail Removed)...
>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
> ***************
>
>
> --
> Posted via a free Usenet account from http://www.teranews.com
>