Excel Automation

  • Thread starter Thread starter Eric
  • Start date Start date
E

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.
 
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
 
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
 
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
 
Back
Top