Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Replacing a file with VBA 1

Status
Not open for further replies.

bravo6

IS-IT--Management
Mar 23, 2006
83
US
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
 
The following procedure will delete a file if it exists... Just delete the file before you export to it.

Code:
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
 
OldName = "[oldfile]": NewName = "[newfile]"
If Dir(NewName) <> "" Then Kill NewName
Name OldName As NewName

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
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)
 
Add these to procedures and call MoveAndOverwrite

Code:
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.
 
OOPS, I got lazy with copy and paste... this is good.

Code:
Sub MoveAndOverwrite(strSource As String, strDestination)

      DeleteFile strDestination
      MoveFile strSource, strDestination
End Sub
 
thanks is (strSource As String, strDestination)
where the [FILENAME] goes?
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top