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!!!!

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" <Stockwell43@discussions.microsoft.com> wrote in message
news:86AEB3F8-F107-49D3-A234-8050E311F8E5@microsoft.com...
> 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!!!!

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!!

"Jeanette Cunningham" wrote:

> 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" <Stockwell43@discussions.microsoft.com> wrote in message
> news:86AEB3F8-F107-49D3-A234-8050E311F8E5@microsoft.com...
> > 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!!!!
>
>
>

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"

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Stockwell43" <Stockwell43@discussions.microsoft.com> wrote in message
news:5BF564FD-3BBA-49B7-B2E1-06123878454C@microsoft.com...
> 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!!
>
> "Jeanette Cunningham" wrote:
>
>> 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" <Stockwell43@discussions.microsoft.com> wrote in message
>> news:86AEB3F8-F107-49D3-A234-8050E311F8E5@microsoft.com...
>> > 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!!!!
>>
>>
>>

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" wrote:

> 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"
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "Stockwell43" <Stockwell43@discussions.microsoft.com> wrote in message
> news:5BF564FD-3BBA-49B7-B2E1-06123878454C@microsoft.com...
> > 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!!
> >
> > "Jeanette Cunningham" wrote:
> >
> >> 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" <Stockwell43@discussions.microsoft.com> wrote in message
> >> news:86AEB3F8-F107-49D3-A234-8050E311F8E5@microsoft.com...
> >> > 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!!!!
> >>
> >>
> >>
>
>
>

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).

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Stockwell43" <Stockwell43@discussions.microsoft.com> wrote in message
news:B37731E2-FE81-4EC4-B31C-16DA24521E69@microsoft.com...
> 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" wrote:
>
>> 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"
>>
>> --
>> Doug Steele, Microsoft Access MVP
>> http://I.Am/DougSteele
>> (no e-mails, please!)
>>
>>
>> "Stockwell43" <Stockwell43@discussions.microsoft.com> wrote in message
>> news:5BF564FD-3BBA-49B7-B2E1-06123878454C@microsoft.com...
>> > 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!!
>> >
>> > "Jeanette Cunningham" wrote:
>> >
>> >> 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" <Stockwell43@discussions.microsoft.com> wrote in message
>> >> news:86AEB3F8-F107-49D3-A234-8050E311F8E5@microsoft.com...
>> >> > 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!!!!
>> >>
>> >>
>> >>
>>
>>
>>

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!!

"Douglas J. Steele" wrote:

> 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).
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "Stockwell43" <Stockwell43@discussions.microsoft.com> wrote in message
> news:B37731E2-FE81-4EC4-B31C-16DA24521E69@microsoft.com...
> > 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" wrote:
> >
> >> 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"
> >>
> >> --
> >> Doug Steele, Microsoft Access MVP
> >> http://I.Am/DougSteele
> >> (no e-mails, please!)
> >>
> >>
> >> "Stockwell43" <Stockwell43@discussions.microsoft.com> wrote in message
> >> news:5BF564FD-3BBA-49B7-B2E1-06123878454C@microsoft.com...
> >> > 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!!
> >> >
> >> > "Jeanette Cunningham" wrote:
> >> >
> >> >> 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" <Stockwell43@discussions.microsoft.com> wrote in message
> >> >> news:86AEB3F8-F107-49D3-A234-8050E311F8E5@microsoft.com...
> >> >> > 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!!!!
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>