VBA from another app: Suppressing Excel confirmation dialog?

[(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?

--
PeteCresswell

[Dave Peterson]

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


"(Pete Cresswell)" wrote:
>
> 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?
>
> --
> PeteCresswell

--

Dave Peterson

[(Pete Cresswell)]

Per Dave Peterson:
>or
>just add a workbook with one sheet.
>
>xlapp.workbooks.add(1)

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