how export and replace existing file

[sk88]

Below is the code I have for Export function. It works perfectly fine. But if I don't rename the file after exporting on to my PC, the file does not get replaced when exporting the file for 2nd or 3rd time. What should I add to the below codes for it to work? Please help.

Thanks

On Error GoTo Err_cmdTest_Click
'Must 1st set a Reference to the Microsoft Office XX.X Object Library
Dim dlgOpen As FileDialog
Dim strExportPath As String
Const conOBJECT_TO_EXPORT As String = "qryM"

Set dlgOpen = Application.FileDialog(msoFileDialogFolderPicker)

With dlgOpen
  .ButtonName = "Export To"
  .InitialView = msoFileDialogViewLargeIcons
  .InitialFileName = CurrentProject.Path
     If .Show = -1 Then
       'Allow for Root Directory selection: C:\, D:\, etc.
       strExportPath = Replace(.SelectedItems(1) & "\", "\\", "\")
 Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _
                               TableName:=conOBJECT_TO_EXPORT, _
                               FileName:=strExportPath & conOBJECT_TO_EXPORT & ".xls")
                                                               
 Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _
                               TableName:="qryBuilding", _
                               FileName:=strExportPath & conOBJECT_TO_EXPORT & ".xls")
       

      

       MsgBox "[" & conOBJECT_TO_EXPORT & "] has been Exported to " & strExportPath & _
               conOBJECT_TO_EXPORT & ".xls", vbInformation, "Export Complete"
     End If
     
     End With

'Set the Object Variable to Nothing.
Set dlgOpen = Nothing

DoCmd.Close
Exit_cmdTest_Click:
  Exit Sub

Err_cmdTest_Click:
  MsgBox Err.Description, vbExclamation, "Error in cmdTest_Click()"
    Resume Exit_cmdTest_Click
End Sub

[rpeare]

Without knowing what's going on your machine in particular (when I've run into this before it's usually been a permissions issue on a server but you seem to be working local drives, so as long as you've got administrator rights it *should* be overwriting) but you can do something fairly simple. Look at filesystemobject commands here:

http://www.sqldts.com/292.aspx

[sk88]

Hi rpeare,

I do not have administrator rights as this is work computer.
I also can't seem to open the link to Microsoft filesystemobject commands from http://www.sqldts.com/292.aspx
Do you have any other links? or if you have any other suggestions?

Thanks.
Much appreciated.

[rpeare]

This is the delete file method:

' Delete File 
Option Explicit  
Sub Main()      
Dim oFSO     
Dim sSourceFile      
Set oFSO = CreateObject("Scripting.FileSystemObject")      
sSourceFile = "C:\SourceFile.txt"      ' Check if file exists to prevent error     
If oFSO.FileExists(sSourceFile) Then         
     oFSO.DeleteFile sSourceFile     
End If      ' Clean Up     

Set oFSO = Nothing      
End

[sk88]

So sorry rpeare. I am not a pro in Access database. where do I add the codes you gave me?

This is what I did and it is not working

Option Compare Database
' Delete File
Option Explicit
Sub Main()
Dim oFSO
Dim sSourceFile
Set oFSO = CreateObject("Scripting.FileSystemObject")
sSourceFile = "C:\SourceFile.txt" ' Check if file exists to prevent error
If oFSO.FileExists(sSourceFile) Then
oFSO.DeleteFile sSourceFile
End If ' Clean Up

Set oFSO = Nothing
End Function



Private Sub Command4_Click()

On Error GoTo Err_cmdTest_Click
'Must 1st set a Reference to the Microsoft Office XX.X Object Library
Dim dlgOpen As FileDialog
Dim strExportPath As String
Const conOBJECT_TO_EXPORT As String = "qryExportMetrics"

Set dlgOpen = Application.FileDialog(msoFileDialogFolderPicker)

With dlgOpen
.ButtonName = "Export To"
.InitialView = msoFileDialogViewLargeIcons
.InitialFileName = CurrentProject.Path
If .Show = -1 Then
'Allow for Root Directory selection: C:\, D:\, etc.
strExportPath = Replace(.SelectedItems(1) & "\", "\\", "\")
Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _
TableName:=conOBJECT_TO_EXPORT, _
FileName:=strExportPath & conOBJECT_TO_EXPORT & ".xls")

Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _
TableName:="qryCapacityBuilding", _
FileName:=strExportPath & conOBJECT_TO_EXPORT & ".xls")




MsgBox "[" & conOBJECT_TO_EXPORT & "] has been Exported to " & strExportPath & _
conOBJECT_TO_EXPORT & ".xls", vbInformation, "Export Complete"
End If

End With

'Set the Object Variable to Nothing.
Set dlgOpen = Nothing

DoCmd.Close
Exit_cmdTest_Click:
Exit Sub

Err_cmdTest_Click:
MsgBox Err.Description, vbExclamation, "Error in cmdTest_Click()"
Resume Exit_cmdTest_Click
End Sub

[rpeare]

you just dumped the code in wherever, you'll have to adapt it to your code, declare the variables where you declare the rest of your variables, do the file existence check before you do your export, if the file is there the code will delete it and your export will work.

[sk88]

PERFECT!! IT'S WORKING! Thank you so much rpeare!