VBA from another app: Suppressing Excel confirmation dialog?

  • Thread starter Thread starter (Pete Cresswell)
  • Start date Start date
P

(Pete Cresswell)

After creating/formatting several worksheets from MS Access, I'd like to delete
the "Sheetn" worksheets that got put there when I did a .WorkBooks.Add.

I avoided using them because I'm not sure how/why they are created - i.e. maybe
some user's defaults would only create 1 empty sheet or none.

So, form MS Access's VBA I'd like to do:

On Error Resume Next
.Worksheets("Sheet1").Delete
.Worksheets("Sheet2").Delete
.Worksheets("Sheet3").Delete
.Worksheets("Sheet4").Delete
On Error GoTo outputFile_Finalize_err

Works, sort of, but the problem is Excel's issuing a confirmation dialog before
acting on every .Delete.

I tried theSS.SetWarnings False like I'd do in MS Access, but Excel's not buying
it. "Error# 438: Object doesn't support this property or method"

Anybody know the magic word?
 
There's an option that the user can set:

tools|options|general tab|Sheets in new workbook.

You could change this to 1.
add your workbook
change it back to the user's preference
or
just add a workbook with one sheet.

xlapp.workbooks.add(1)

The (1) means one sheet. (Don't try (2), (3),... It doesn't work that way.)

You could also use in the builtin template name or it's value

Workbooks.Add template:=xlWBATWorksheet
or
Workbooks.Add template:=-4167
 
Per Dave Peterson:
or
just add a workbook with one sheet.

xlapp.workbooks.add(1)
Click to expand...

One sheet works for me - I know that there will always be 1 sheet and for the
first one I create, I'll just rename "Sheet1".

Thanks.
 
Back
Top