I am creating a simple text file. And appending the data to the file. i need
to run the same macro every time.
It is fine when i run the script at least once. When i am running for the
first time it is creating the file and it is showing the file is exist window.
Please help or let me know is there another way of creating a file and error
handling if file exists.

************
Set fso = New FileSystemObject
fso.CreateTextFile fileName, overwrite:=True

If fso.FileExists(fileName) Then
If MsgBox("The file " & fso.GetFileName(fileName) & " already
exists. Do " & _
"you want to replace the existing file?", vbYesNo +
vbExclamation + _
vbDefaultButton2, PROJECT_NAME) = vbNo Then
Exit Sub
End If
End If


Set ts = fso.OpenTextFile(fileName, ForWriting, True)
With Range(ActiveWorkbook.Worksheets("Anvil").Cells(1, dataColumn), _
ActiveWorkbook.Worksheets("Anvil").Cells(rowCount, dataColumn))
For Each tempCell In .Cells

If tempCell.Row < rowCount Then
tempCell.Value = Left(tempCell.Value,
Len(tempCell.Value) - 1)
Call ts.WriteLine(tempCell.Value)
Else
tempCell.Value = Left(tempCell.Value,
Len(tempCell.Value) - 1)
Call ts.Write(tempCell.Value)
End If
Next
End With

Try code like the following:

Sub AAA()

Dim FNum As Integer
Dim FName As Variant
Dim Res As VbMsgBoxResult
FName = Application.GetSaveAsFilename(vbNullString, _
"Text Files (*.txt),*.txt", , "Select File")
If FName = False Then
' user cancelled
Exit Sub
End If
FNum = FreeFile()
' if you want to delete the existing file...
Open FName For Output Access Write As #FNum
' OR, if you want to keep the existing file and append data...
Open FName For Append Access Write As #FNum
' OR if you want to ask the user....
Res = MsgBox("Do you want to delete the existing file?" & vbCrLf & _
"Click 'Yes' to delete the existing file and continue." & vbCrLf &
_
"Click 'No' to keep the existing file and append to it." & vbCrLf
& _
"Click 'Cancel' to quit.", _
vbYesNoCancel + vbDefaultButton2 + vbQuestion, "What About The
File")
Select Case Res
Case vbYes ' delete existing file
Open FName For Output Access Write As #FNum
Case vbNo ' keep existing file and append
Open FName For Append Access Write As #FNum
Case Else ' quit
Exit Sub
End Select

Print #FNum, "Hello"
Print #FNum, "World"

Close #FNum
End Sub


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)





On Thu, 18 Jun 2009 11:51:02 -0700, Bobby
wrote:

I am creating a simple text file. And appending the data to the file. i need
to run the same macro every time.
It is fine when i run the script at least once. When i am running for the
first time it is creating the file and it is showing the file is exist window.
Please help or let me know is there another way of creating a file and error
handling if file exists.

************
Set fso = New FileSystemObject
fso.CreateTextFile fileName, overwrite:=True

If fso.FileExists(fileName) Then
If MsgBox("The file " & fso.GetFileName(fileName) & " already
exists. Do " & _
"you want to replace the existing file?", vbYesNo +
vbExclamation + _
vbDefaultButton2, PROJECT_NAME) = vbNo Then
Exit Sub
End If
End If


Set ts = fso.OpenTextFile(fileName, ForWriting, True)
With Range(ActiveWorkbook.Worksheets("Anvil").Cells(1, dataColumn), _
ActiveWorkbook.Worksheets("Anvil").Cells(rowCount , dataColumn))
For Each tempCell In .Cells

If tempCell.Row < rowCount Then
tempCell.Value = Left(tempCell.Value,
Len(tempCell.Value) - 1)
Call ts.WriteLine(tempCell.Value)
Else
tempCell.Value = Left(tempCell.Value,
Len(tempCell.Value) - 1)
Call ts.Write(tempCell.Value)
End If
Next
End With

You are first creating the file everythime, then checking if it exist. Do it
the other way , so first check if it exists.

"Bobby" wrote:

I am creating a simple text file. And appending the data to the file. i need
to run the same macro every time.
It is fine when i run the script at least once. When i am running for the
first time it is creating the file and it is showing the file is exist window.
Please help or let me know is there another way of creating a file and error
handling if file exists.

************
Set fso = New FileSystemObject
fso.CreateTextFile fileName, overwrite:=True

If fso.FileExists(fileName) Then
If MsgBox("The file " & fso.GetFileName(fileName) & " already
exists. Do " & _
"you want to replace the existing file?", vbYesNo +
vbExclamation + _
vbDefaultButton2, PROJECT_NAME) = vbNo Then
Exit Sub
End If
End If


Set ts = fso.OpenTextFile(fileName, ForWriting, True)
With Range(ActiveWorkbook.Worksheets("Anvil").Cells(1, dataColumn), _
ActiveWorkbook.Worksheets("Anvil").Cells(rowCount, dataColumn))
For Each tempCell In .Cells

If tempCell.Row < rowCount Then
tempCell.Value = Left(tempCell.Value,
Len(tempCell.Value) - 1)
Call ts.WriteLine(tempCell.Value)
Else
tempCell.Value = Left(tempCell.Value,
Len(tempCell.Value) - 1)
Call ts.Write(tempCell.Value)
End If
Next
End With