Suppressing Excel Confirmation Message

  • Thread starter Thread starter WSF
  • Start date Start date
W

WSF

Access 97
I am trying the following code to delete a table in an Excel file - from
script in an Access module.
It works fine, but how can I suppress the message to the User asking to
confirm the deletion (coming from Excel??).

Function CheckDataSheetExists()
Dim xlApp As Excel.Application, xlBook As Excel.Workbook
On Error GoTo EndCheck
DoCmd.SetWarnings False

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open(CurrentReport) ' CurrentReport is from
variable
xlApp.Windows(1).Visible = True
xlBook.Sheets(ReportName).Delete ' ReportName is from variable

xlBook.close savechanges:=True

EndCheck:
If Err.Number = 9 Then GoTo CloseCheck
If Err.Number = 0 Then GoTo CloseCheck
msgbox Err.Number & " - " & Err.Description & " - please take note of
the error number and description here and notify IT."

CloseCheck:
xlApp.Quit
Set xlBook = Nothing
Set xlApp = Nothing
End Function


Thanks in advance,
WSF
 
-----Original Message-----
Access 97
I am trying the following code to delete a table in an Excel file - from
script in an Access module.
It works fine, but how can I suppress the message to the User asking to
confirm the deletion (coming from Excel??).

Function CheckDataSheetExists()
Dim xlApp As Excel.Application, xlBook As Excel.Workbook
On Error GoTo EndCheck
DoCmd.SetWarnings False

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open(CurrentReport) ' CurrentReport is from
variable
xlApp.Windows(1).Visible = True
xlBook.Sheets(ReportName).Delete ' ReportName is from variable

xlBook.close savechanges:=True

EndCheck:
If Err.Number = 9 Then GoTo CloseCheck
If Err.Number = 0 Then GoTo CloseCheck
msgbox Err.Number & " - " & Err.Description & " - please take note of
the error number and description here and notify IT."

CloseCheck:
xlApp.Quit
Set xlBook = Nothing
Set xlApp = Nothing
End Function


Thanks in advance,
WSF
Click to expand...
Hi WSF,
try...
Application.DisplayAlerts = False

Just a thought, you might like to post excel questions to
an excel forum; even if initiating from access.

Luck
Jonathan
 
Back
Top