unable to create a file if already exiists

B

Bobby

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
 
J

JLatham

Looks to me like you're creating the file and THEN testing to see if it
exists, so even the very first time you run the macro, the file exists when
you test for it. Perhaps as simple as moving the fso.CreateTextFile
fileName, overwrite:=True statement down below your first If ... End If block?

I don't use the fso very often, choosing to do it the old fashioned way, so
if my suggestion doesn't work for you, give a shout and I'll provide some old
fashioned, tried and true, code to test if it exists using DIR$() and ancient
commands like OPEN and WRITE.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top