Delete old information when transferring to excel

[Stockwell43]

Hello,

I have several queries that with a click of a button transfers the data to
Excel and works fine. Here is the Code:

Private Sub cmdExportC_Click()
On Error GoTo Do_Nothing

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7,
"qryprocstatus", txtExportFileC
'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7,
"qryregion", txtExportFileC
'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7,
"qrymonthenddetail", txtExportFileC
'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7,
"qrybankcenter", txtExportFileC
'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7,
"inventory", txtExportFileC
'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7,
"inventory_details", txtExportFileC
'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7,
"qryissues", txtExportFileC
'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7,
"qryerrorcode", txtExportFileC
'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7,
"qryissues", txtExportFileC
'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7,
"qryerrorcode", txtExportFileC

MsgBox "The tables have been successfully exported to " & txtExportFileC
& "."

Exit Sub

Do_Nothing:
MsgBox "Export has failed. An error occurred or the user terminated the
operation."
End Sub

However, when I click the button to run the query again and put in a
different date range, it adds it to the old instead of replacing it. How can
I get the new information to overide the old or on the click somehow delete
the old to capture the new?

I have several queries set up but may not use them all so I comment some
out. I am still in the designing phase.

Thanks Folks!!!!

 

[Jeanette Cunningham]

Hi
one way would be to check for any existing spreadsheets with the name
txtExportFileC and either delete them or rename them.
You could instead give a different name to your latest e xport by adding the
time and date to the name of the file each time you export.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Stockwell43]

Hi Jeanette,

Thanks for responding.

I understand changing the name but I don't want a lot of spreadsheets and
have to delete the sheets individually each time. I just want to click the
button and have the new export overwrite was is in the designated worksheet.
When importing from excel to access I can make a delete query but now I want
to go the other way. Does that make sense?

Thanks!!

 

[Douglas J. Steele]

How about code that looks in the workbook for a worksheet of a given name
and deletes it if it exists?

Something like:

Sub DeleteWorksheet( _
WorkbookName As String, _
WorksheetName As String _
)

On Error GoTo ErrHandler

Dim objExcel As Object

If Len(Dir(WorkbookName)) > 0 Then
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Open WorkbookName
objExcel.ActiveWorkbook.Worksheets(WorksheetName).Delete
objExcel.ActiveWorkbook.Close SaveChanges:=True
objExcel.Application.Quit
Set objExcel = Nothing
End If

EndIt:
Exit Sub

ErrHandler:
Select Case Err.Number
Case 9 ' Subscript out of range: worksheet doesn't exist
Resume Next
Case Else
MsgBox Err.Number & ": " & Err.Description
Resume EndIt
End Select

End Sub

You'd use this as

DeleteWorksheet txtExportFileC, "qryprocstatus"

 

[Stockwell43]

Hi Doug,

Thanks for responding.

I must have done something wrong. I placed your code above mine for
transferring the query and when I click the button I get an Compile Error
"Expected End Sub" and this is highlighted in yellow:

Private Sub cmdExportA_Click()

Was I suppose to use a Module instead?

Thanks!!

 

[Douglas J. Steele]

The sub could go in a stand-alone module (in which case it could be called
from any form in your application). It could also go into the class module
associated with your form.

It sounds as though you might have just tried copying the sub inside your
existing sub. If you're putting it into the form's class module, it needs to
be outside of all existing subs (and functions).

 

[Stockwell43]

You were right, I pasted it in my existing sub.

I put it in a Stand alone module and works fine, my bad.

Thank you Sir! As always you're help is most appreciated!!