Deleting sheet from a template

  • Thread starter Thread starter mcescher
  • Start date Start date
M

mcescher

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
 
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
Click to expand...


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

Chris M.
 
Back
Top