Dim objXL As Excel.Application
Dim objXLWrkBk As Excel.Workbook
Dim objXLWrkSht As Excel.Worksheet
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM tblProposals WHERE ProposalID= " & lngProposalID)
If rs.BOF Then
'record not found?
Else
Set objXL = CreateObject("Excel.Application")
Set objXLWrkBk = objXL.Workbooks.Open(strCalcFilePath)
Set objXLWrkSht = objXLWrkBk.Worksheets("Original")
objXL.DisplayAlerts = False
objXL.Visible = False
objXLWrkSht.Range("A1").Activate
'first store the quote number
objXLWrkSht.Range("D1").Value = Nz(rs("QuoteNumber").Value)
'project description & rfq
objXLWrkSht.Range("J1").Value = Nz(rs("Description").Value) & "-" & Nz(rs("CustomerRFQ").Value)
'set year of delivery
objXLWrkSht.Range("Q1").Value = Nz(rs("DeliveryDate").Value)
'set originator
objXLWrkSht.Range("X1").Value = Nz(rs("Originator").Value)
'set customer
objXLWrkSht.Range("J2").Value = Nz(DLookup("CustomerName", "tblCustomers", "CustomerID= " & rs("CustomerID").Value)) & "-" & Nz(DLookup("Sites", "tblSites", "SiteID= " & rs("SiteID").Value))
'set commission
objXLWrkSht.Range("D3").Value = Nz(DLookup("Commission", "tblCommissions", "CommissionID= " & rs("CommissionID").Value))
'set country
objXLWrkSht.Range("Q2").Value = Nz(DLookup("CountryName", "tblCountries", "CountryID= " & rs("CountryID").Value))
'set quote due date...
objXLWrkSht.Range("X2").Value = Nz(rs("QuoteDueDate").Value)
objXLWrkBk.Close SaveChanges:=True
'objXLWrkBk.Save
'objXLWrkBk.Close
objXL.Quit
End If
Done:
Set rs = Nothing
Set db = Nothing
Set objXL = Nothing
Set objXLWrkBk = Nothing
Set objXLWrkSht = Nothing
Exit Function
Err_Handler:
MsgBox Err.Description, vbCritical, "Error #: " & Err.Number
Resume Done