We help IT Professionals succeed at work.

How do I over write a CSV file without getting errors?

rfgraham
rfgraham asked
on
When I try to rename a file I get an error that says 'file already exists'. Is there any way that I can force it to over write the existing file?
I know I can do it by using this code:
newFileName = "C:\Program Files\Microsoft Visual Studio\VB98\Projects\SystemIDs\filename.csv"
Kill newFileName
But I would prefer to just force it to over write it by using the code below.

Thanks
oldFileName = "C:\Program Files\Microsoft Visual Studio\VB98\Projects\SystemIDs\SystemName.txt"
newFileName = "C:\Program Files\Microsoft Visual Studio\VB98\Projects\SystemIDs\SystemName.csv"
    
Name oldFileName As newFileName

Open in new window

Comment
Watch Question

Commented:
Hello rfgraham,

what function are you using to create the file?

Thanks,

dis1931

Commented:
What operating system and rights for the folder containing the file you want to rename?
CERTIFIED EXPERT

Commented:
you have to check for its existance (the file that is) and open it and overwrite it.

so I assume the code will  test for it being there, if not then create the file otherwise open the file and overwrite

Commented:
I can't remember the functions I've used but some built in file creation functions can be passed a parameter to overwrite file if it exists...

Author

Commented:
Basically I open a text file and write to it in csv format, I then close it and rename it as a csv file.
The function I'm using to create is: Open "C:\filename.txt" For Append As #1
finalData = rs(0) & "," & rs(1)
Print #1, finalData
rs.MoveNext
Wend
Close #1
rs.Close
Set rs = Nothing
oldFileName = "C:\filename.txt"
newFileName = "C:\filename.csv"     'I get error at this point, 'file already exists'.

I'm running XP Pro with admin priv

Thanks
CERTIFIED EXPERT

Commented:
well, on error delete file thne carry on.
Commented:
If the error is what is the problem and the files are updated properly.
You could write your own custom error trap and control if the program stops or not and how it handles errors.

When VB6 was new many people had problems with File I/O and wrote custom wrappers to handle Opening, Closing, Deleting, etc..  If you want a copy of the one I wrote let me know.

Although it is not considered good code you could use the 'On Error Resume Next' which tells VB to go until the Blue Screen of Death sez different.  If you do that make sure you remember to add 'On Error Goto 0' before the procedure or function ends to turn error checking back on.

Author

Commented:
I went ahead and created an error trap, this is the code I used:

Dim fso As New FileSystemObject, fil1

On Error GoTo ErrorHandler
Set fil1 = fso.GetFile("C:\filename.csv")
fil1.Delete
oldFileName = "C:\filename.txt"
newFileName = "C:\filename.csv"
Name oldFileName As newFileName
Exit Sub

ErrorHandler:
Dim strError As String
strError = ("filename.csv file must exist in working directory ")
MsgBox strError

End Sub

Thanks for your help - 23637269:

Commented:
"Open "C:\filename.csv" For Output As #1"

should also have worked...you can write to a .txt extension just as you can a .csv extension no need to rename just write to the filename.csv file with Output instead of Append.  Output will overwrite existing file, while append will write to the next line of the file if it exists or create a new one if it doesn't exist.

I know you accepted already but this could help someone else...