Open Excel from access - Compile Error

  • Thread starter Thread starter Guest
  • Start date Start date
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
 
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)
 
Back
Top