Late Binding

[sebble1984]

Hi Guys,

I have wrote some code to export to excel, which work in access 2013 full version, but testing the system out in a runtime environment the system crashes because I am referencing the excel library in the VBA references.
I have now removed the reference and I am looking to change my code to Late binding, although I can not seem to get this working.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Dim UserDate As Date
Dim StartDate As Date
Dim EndDate As Date
Dim ForStartDate As Date
Dim ForEndDate As Date
Dim rsCount As Integer

If IsNull(Me.TxTStartDate.Value) Or IsNull(Me.TxTEndDate.Value) Then

MsgBox "Please Enter Start and End Dates for the Report to be Processed", vbInformation, "Utilergy"

Else
StartDate = Me.TxTStartDate.Value
EndDate = Me.TxTEndDate.Value
ForStartDate = Format(StartDate, "DD/MM/YYYY")
ForEndDate = Format(EndDate, "DD/MM/YYYY")


Dim strSQL As String
strSQL = "SELECT tblProjects.ProjectDateModfied, tblProjects.ProjectNumber, tblWorks.WorkNumber, tblOrders.OrderNumber, autotblProjectStatus.ProjectStatus," & vbCrLf
strSQL = strSQL & "tblWorks.WorkAddressNameNumber & ' ' & tblWorks.WorkAddressStreet & ' ' & tblWorks.WorkAddressTown & ' ' & tblWorks.WorkAddressPostcode, tblProjects.ProjectRecievedDate," & vbCrLf
strSQL = strSQL & "tblPreSiteSurveys.PreSiteSurveyDate, tblProjects.ProjectProvisionalStartDate, tblProjects.ProjectCompletionDate, tblProjects.ProjectActualStartDate," & vbCrLf
strSQL = strSQL & "tblProjects.ProjectActualCompletionDate, tblPreSiteSurveys.PreSiteSurveyStopTheClockDate, tblPreSiteSurveys.PreSiteSurveyStopTheClockReason," & vbCrLf
strSQL = strSQL & "tblPreSiteSurveys.PreSiteSurveyStopTheClockComment, tblAsBuiltDetails.AsBuiltDetailsDateSubmittedToENW, tblPreSiteSurveys.PreSiteSurveyContactName," & vbCrLf
strSQL = strSQL & "tblPreSiteSurveys.PreSiteSurveyContactNumber, tblAsBuiltDetails.AsBuiltDetailsDateJointed" & vbCrLf
strSQL = strSQL & "FROM (((tblPreSiteSurveys INNER JOIN (autotblProjectStatus INNER JOIN tblProjects ON autotblProjectStatus.[ProjectStatusID] = tblProjects.[ProjectStatus])" & vbCrLf
strSQL = strSQL & "ON tblPreSiteSurveys.[PreSiteSurveyID] = tblProjects.[FKPreSiteSurveyID]) INNER JOIN tblWorks ON tblProjects.[ProjectID] = tblWorks.[FKProjectID])" & vbCrLf
strSQL = strSQL & "LEFT OUTER JOIN tblAsBuiltDetails ON tblWorks.[WorkID] = tblAsBuiltDetails.[FKWorkID]) INNER JOIN tblOrders ON tblWorks.WorkID = tblOrders.FKWorkID" & vbCrLf
strSQL = strSQL & "WHERE tblProjects.ProjectDateModfied between #" & ForStartDate & "# AND #" & ForEndDate & "# ;"
'counts the number of records for Cell F7 of thhe spreadsheet
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
rsCount = rs.RecordCount



'Start a new workbook in Excel
Dim oApp As New Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet

Set oBook = oApp.Workbooks.Open("U:\x\Reports\UtilergyMasterUpdate.xlsm")
Set oSheet = oBook.Worksheets(1)
ForStartDate = Format(StartDate, "DD/MM/YYYY")
ForEndDate = Format(EndDate, "DD/MM/YYYY")
'Add the field names in row 1
' Dim i As Integer
' Dim iNumCols As Integer
' iNumCols = rs.Fields.Count
' For i = 1 To iNumCols
' oSheet.Cells(2, i).Value = rs.Fields(i - 1).Name
' Next
'Add the data starting at cell A2
oSheet.Range("A3").CopyFromRecordset rs

' oSheet.Range("E13").Value = ForStartDate
' oSheet.Range("E14").Value = ForEndDate


oApp.Visible = True
oApp.UserControl = True

'Close the Database and Recordset
rs.Close

How would this code be changed please for late binding rules?
Thanks in advance.:banghead:

 

[Frothingslosh]

As I don't have access to Access 13, let me ask this: can you no longer set a reference to the Excel library in 13?

 

[sebble1984]

Thanks for the reply, yes you can. Microsoft Excel 15.0 Object Library, but I dont want to include that reference as users have different versions on Excel and the database is run in a runtime environment

The database crashes in the runtime environment when the "Microsoft Excel 15.0 Object Library" is referenced.

 

[sebble1984]

Thanks.

I changed some code to this: -

Dim oApp As Object
Dim oBook As Object

Set oApp = CreateObject("excel.Application")
oApp.Workbooks.Open ("U:\xReports\OrdersReceived.xlsm")
oApp.Visible = True
Set oBook = oApp.ActiveWorkbook

Set oSheet = oBook.Worksheets(1)

And sees to be working great

 

[Frothingslosh]

Good to hear.

Sorry about not replying, had to clean up an unexpected mess at work.