Open Excel from access - Compile Error

G

Guest

When I try to run my code (below), the message "Compile error - Can't find
project or library" appears, highlighting the line "Dim oExcel As
Excel.Application", but when I try the line on another database, it works
fine, can anyone help me to find the error in my code?

Private Sub CreateReport_Click()

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "SR_AllGWP",
"S:\Database Info\SR_Reports.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "SR_AllCount",
"S:\Database Info\SR_Reports.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "SR_SelectGWP",
"S:\Database Info\SR_Reports.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8,
"SR_SelectCount", "S:\Database Info\SR_Reports.xls", True

Dim oExcel As Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet

'Open existing workbook
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Open("S:\Database Info\SR_Reports.xls")

'Add data to cells of a worksheet
Set oSheet = oBook.Worksheets("Misc")
oSheet.Range("c2").Value = Forms!frReportFilter.B
oSheet.Range("c3").Value = Forms!frReportFilter.O
oSheet.Range("c4").Value = Forms!frReportFilter.P
oSheet.Range("c5").Value = Forms!frReportFilter.C
oSheet.Range("c5").Value = Forms!frReportFilter.U

oSheet.Range("c8").Value = Forms!frReportFilter.frMonth
oSheet.Range("c9").Value = Forms!frReportFilter.toMonth


'Open Excel
oExcel.Visible = True

'Release object reference
Set oSheet = Nothing
Set oBook = Nothing
Set oExcel = Nothing

End Sub

thanks
Sin
 
D

Douglas J. Steele

Given how little your code is doing, you could ignore setting the reference,
and simply change

Dim oExcel As Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet

to

Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object

(that's known as Late Binding)

For Early Binding (which is what you currently have), you need to ensure
that you've added a reference to Excel (under Tools | References in the VB
Editor)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top