Replacing a file with VBA 1

[bravo6]

Yesterday "HTH" taught me how to convert a file name in VBA. Thanks HTH!!

The problem I have now is that if the filename already exists, I have to go to that folder and delete it before runing the program. Is there a way in VBA to have it overwrite an existing file rather than giving me the error "file already exists"?

Below is the code I'm using:
Function JaSat_Macro()
On Error GoTo JaSat_Macro_Err

DoCmd.SetWarnings False
DoCmd.OpenQuery "JaSat NEW Q1", acViewNormal, acEdit
DoCmd.OpenQuery "JaSat NEW Q2c", acViewNormal, acEdit
DoCmd.OpenQuery "JaSat NEW Q3", acViewNormal, acEdit
DoCmd.TransferText acExportDelim, "JASAT Export Specification", "JaSat", "[oldfile]", False, ""

Dim OldName, NewName
OldName = "[oldfile]": NewName = "[newfile]
Name "[oldfile]" As "[newfile]"


JaSat_Macro_Exit:
Exit Function

JaSat_Macro_Err:
MsgBox Error$
Resume JaSat_Macro_Exit

End Function

 

[lameid]

The following procedure will delete a file if it exists... Just delete the file before you export to it.

Sub DeleteFile(strFile As String)
    On Error Resume Next
    Dim FSO As Object
    Set FSO = CreateObject("Scripting.FileSystemObject")
    FSO.DeleteFile (strFile)
    Set FSO = Nothing
End Sub

 

[PHV]

OldName = "[oldfile]": NewName = "[newfile]"
If Dir(NewName) <> "" Then Kill NewName
Name OldName As NewName

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[bravo6]

Hmmm, Neither of these worked. I'm not sure where I'm supposed to substitute the filename in lameid's example.

I wrote:
Sub DeleteFile [newfilename]
On Error Resume Next
Dim FSO As Object
Set FSO = CreateObject("Scripting.FileSystemObject")
FSO.DeleteFile (strFile)
Set FSO = Nothing
End Sub

What is supposed to go in "("Scripting.FileSystemObject")" and in "(strFile)

 

[lameid]

Add these to procedures and call MoveAndOverwrite

Sub MoveFile(strSource As String, strDestination)
    On Error Resume Next
    Dim FSO As Object
    Set FSO = CreateObject("Scripting.FileSystemObject")
    FSO.MoveFile strSource, strDestination
    Set FSO = Nothing
End Sub


Sub MoveAndOverwrite (strSource As String, strDestination)

      DeleteFile strDestination
      MoveFile strSource As String, strDestination
End Sub

I remember reading that Kill and Name are going to be depricated. There may have been sufficient backlash to keep it indefinitely but better safe than sorry.

 

[lameid]

OOPS, I got lazy with copy and paste... this is good.

Sub MoveAndOverwrite(strSource As String, strDestination)

      DeleteFile strDestination
      MoveFile strSource, strDestination
End Sub

 

[bravo6]

thanks is (strSource As String, strDestination)
where the [FILENAME] goes?

 

[lameid]

Yes... there are two the source and destination file. Ideally you should also include the full path in both. The OS path is often not where you expect it to be.