Excel Automation - Delete sheets using access VBA

[trevorC via AccessMonster.com]

I can delete a sheet using the code below, is it possible to automate this
process? To create this report takes about 5 mins to proccess all the sheets
and generate a summary, after this i need to delete the scratch pad sheets
and continue the automation and sent the report with Outlook.

Dim AppOutLook
Dim MailOutLook
Dim olmailItem
Set AppOutLook = CreateObject("Outlook.Application")
Set MailOutLook = AppOutLook.CreateItem(olmailItem)
Dim Excel_Application As Excel.Application
Dim Excel_Workbook As Excel.Workbook
Dim Current_Worksheet As Excel.Worksheet
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Weekly report
for", gg, True
Set Excel_Workbook = GetObject(gg)
Set Excel_Application = Excel_Workbook.Parent
Excel_Application.WindowState = xlMinimized
Excel_Application.Visible = True
Excel_Workbook.Windows(1).Visible = True
Excel_Application.Worksheets.Add
Excel_Workbook.Worksheets(1).Name = "Summary"
....
Excel_Workbook.Worksheets(15).Name = "Scratch Pad1"
....
Excel_Workbook.Worksheets("Scratch Pad1").Delete

The above line causes excel to popup a msgbox requesting confirmation to
delete the sheet, This stops the automation. is it possible to get around
this by using sendkeys ?

Could it be similar to the following ?
With MailOutLook
.To = to_List
.Subject = "Weekly Status Report"
.Attachments.Add gg
.Body = "Please find attached the weekly report."
.Display
SendKeys "%{ENTER}", True

 

[ryguy7272]

I copied this from my site:

It is quite easy to perform operations in Excel, and control the entire
process from Access.

Make sure you set a reference to Excel, and then run this code in an Access
module:



Option Compare Database

Option Explicit ' Use this to make sure your variables are defined

' One way to be able to use these objects throughout the Module is to
Declare them here, and not in a Sub



Private objExcel As Excel.Application

Private xlWB As Excel.Workbook

Private xlWS As Excel.Worksheet



Sub Rep()

Dim strFile As String

strFile = "C:\Documents and Settings\rshuell\Desktop\Ryan\Crosstab Query.xls"

‘Of course, this is just an example; put the actual path to your actual file
here…

' Opens Excel and makes it Visible

Set objExcel = New Excel.Application

objExcel.Visible = True

' Opens up a Workbook

Set xlWB = objExcel.Workbooks.Open(strFile)

' Sets the Workseet to the last active sheet - Better to use the commented
version and use the name of the sheet.

Set xlWS = xlWB.ActiveSheet

' Set xlWS = xlWB("Sheet1")

With xlWS ' You are now working with the Named file and the named worksheet

' Your Excel code begins here…you can record a macro and make the process
super easy!!

End With

' Close and Cleanup

xlWB.SaveAs xlSaveFile

xlWB.Close

xlapp.Quit

Set xlapp = Nothing

End Sub


HTH,
Ryan---