Excel Automation

[Eric]

I am having some trouble getting this code to work for my
Access to Excel Automation. I have my refrences set to
Excel 97 (in Access 97) but the following code doesn't
work. It's pretty self-explainitory.

Public Function AuditSummary()
On Error GoTo ErrorHandler

Dim objExcel As Excel.Application
Dim msg1 As String, msg2 As String, msg3 As String

Set objExcel = Excel.Application

objExcel.Sheets("AuditSummary").Select
Range("B3").Value = "Leola"
Range("C3").Value = "04/05/2004"
objExcel.Worksheets("SummaryComparison").Activate
Range("B3").Value = "Leola"
Range("C3").Value = "04/05/2004"

'Rest of code goes here...which works.

End Sub

I am having issues with objExcel.Sheets
("AuditSummary").Select in which I get a 1004 run time
error. I'm sure that I am missing something simple, but I
am getting frustrated.

Any help is greatly appreciated.

 

[Gerald Stanley]

I cannot see where you have opened the workbook that
contains the sheet "audit Summary". I would have thought
that you would need something like

Dim xlBook As Excel.Workbook
Set xlBook = objExcel.Workbooks.Open {yourfile}

xlBook.Sheets("AuditSummary").Select

Hope This Helps
Gerald Stanley MCSD

 

[TC]

Er: where are you >starting< Excel?

You need:

dim objExcel as NEW excel.application
set objexcel = excel.application

or:

dim objExcel as excel.application
set objexcel = NEW excel.application

or preferably:

dim objExcel as excel.application
set objexcel = createobject ("Excel.Application")

http://support.microsoft.com/default.aspx?scid=kb;EN-US;244264
"When creating an instance of an Microsoft Office application, use
CreateObject instead of New. CreateObject more closely maps to the creation
process used by most Visual C++ clients, and allows for possible changes in
the server's CLSID between versions. CreateObject can be used with both
early-bound and late-bound objects."

HTH,
TC

 

[Guest]

This might help...the complete procedure.

Public Function AuditSummary()
On Error GoTo ErrorHandler

Dim objExcel As Excel.Application
Dim msg1 As String, msg2 As String, msg3 As String

Set objExcel = Excel.Application

MsgBox "Please choose Yes or OK to all of the messages
that appear. You will not damage the reports in any way.
The Reports may take a minute to run.", vbOKOnly

DoCmd.TransferSpreadsheet acExport,
8, "qryReports", "C:\QAAudit\FilmReports.xls", True, ""
DoCmd.TransferSpreadsheet acExport,
8, "qryComparison", "C:\QAAudit\FilmReports.xls", True, ""
DoCmd.TransferSpreadsheet acExport,
8, "qryrptComments", "C:\QAAudit\FilmReports.xls", True, ""

With objExcel
.Workbooks.Open "C:\QAAudit\FilmReports.xls", True
.Run "PopulateSheets"
.Worksheets("qryReports").Delete
.Worksheets("qryComparison").Delete
End With

Select Case Month(Date)
Case 1 To 5
msg2 = "C:\QAAudit\LeolaFilmReportsSpring2004.xls."
objExcel.ActiveWorkbook.SaveAs
("C:\QAAudit\LeolaFilmReportsSpring2004.xls")
Case Else
msg2 = "C:\QAAudit\LeolaFilmReportsFall2004.xls."
objExcel.ActiveWorkbook.SaveAs
("C:\QAAudit\LeolaFilmReportsFall2004.xls")
End Select

objExcel.Quit
Set objExcel = Nothing

msg1 = "Reports are complete. They are saved at"
msg3 = "You will have to exit the Film Quality Audit
Database to view the report."
MsgBox msg1 & " " & msg2 & " " & msg3

Exit Function

ErrorHandler:
If Err = 3010 Then
With objExcel
.Workbooks.Open "C:\QAAudit\FilmReports.xls",
True
.Worksheets("qryReports").Delete
.Worksheets("qryComparison").Delete
.Worksheets("qryrptComments").Delete
.Workbooks.Close
End With
Resume
Else
MsgBox Err.Number & " " & Err.Description
Err.Clear
objExcel.Quit
Set objExcel = Nothing
End If
End Function