Hi All,
I've got an Excel file formatted with several sheets that each have
some headers that I use as a template to paste my data in. One of the
sheets may or may not receive information. If I don't have any
records to paste into that sheet, I'd like to delete it. Because of
the formatted headers, Excel gives an error message. I tried a
DoCmd.SetWarnings False, but apparently that stops at Access.

strWSName = "Future"
Set rs = db.OpenRecordset("UnprocessedFuture", dbOpenDynaset)
Set xlsReport = xlbReport.Worksheets(strWSName)
With rs
.MoveLast
.MoveFirst
intRows = rs.RecordCount
If intRows > 0 Then
xlsReport.Range("A7").CopyFromRecordset rs
GoSub FormatSheet
xlsReport.Range("H4").Formula = "=Sum_Visible_Cells(J7:J" &
intRows + 6 & ")"
Else
DoCmd.SetWarnings False
'Error message appears for this line
xlsReport.Delete
DoCmd.SetWarnings True
End If
End With


Would it work to delete all the information from the sheet and then
remove it? Would I be better off just hiding the sheet, or is there a
way I can delete it.

Thanks so much,
Chris M.

I assume you have a variable that is your Excel application Object. You need
to turn off warnings using this variable

xlApp.DisplayAlerts = False
xlbReport.Worksheets(strWSName).Delete
xlApp.DisplayAlerts = True

"mcescher" wrote:

> Hi All,
> I've got an Excel file formatted with several sheets that each have
> some headers that I use as a template to paste my data in. One of the
> sheets may or may not receive information. If I don't have any
> records to paste into that sheet, I'd like to delete it. Because of
> the formatted headers, Excel gives an error message. I tried a
> DoCmd.SetWarnings False, but apparently that stops at Access.
>
> strWSName = "Future"
> Set rs = db.OpenRecordset("UnprocessedFuture", dbOpenDynaset)
> Set xlsReport = xlbReport.Worksheets(strWSName)
> With rs
> .MoveLast
> .MoveFirst
> intRows = rs.RecordCount
> If intRows > 0 Then
> xlsReport.Range("A7").CopyFromRecordset rs
> GoSub FormatSheet
> xlsReport.Range("H4").Formula = "=Sum_Visible_Cells(J7:J" &
> intRows + 6 & ")"
> Else
> DoCmd.SetWarnings False
> 'Error message appears for this line
> xlsReport.Delete
> DoCmd.SetWarnings True
> End If
> End With
>
>
> Would it work to delete all the information from the sheet and then
> remove it? Would I be better off just hiding the sheet, or is there a
> way I can delete it.
>
> Thanks so much,
> Chris M.
> .
>

On Apr 6, 5:46*am, RonaldoOneNil
<RonaldoOne...@discussions.microsoft.com> wrote:
> I assume you have a variable that is your Excel application Object. You need
> to turn off warnings using this variable
>
> xlApp.DisplayAlerts = False
> xlbReport.Worksheets(strWSName).Delete
> xlApp.DisplayAlerts = True
>


Yup, I do have an app variable. That did the trick, thanks so much.

Chris M.