Version Angst

[BikeToWork]

I have a database that is opened by users with different versions of Access. The reference to MS Excel 15.0 shows up after an Office 2013 user has opened it and the database will not open in earlier versions of Access afterwards until I changed the Excel reference back to an earlier version. I have tried to change all references to Excel in the code to be late binding:

Function OpenExcel(strFileName As String)
Dim xl  As Object
 
Dim WKB As New Excel.Workbook
 
    Set xl = CreateObject("Excel.Application")
    xl.Visible = True
    Set WKB = xl.Workbooks.Open(strFileName)
    Set xl = Nothing
    Set WKB = Nothing
 
End Function
 

This code is just a sample. There are hundreds of references to Excel in the project. That does not seem to make any difference. Is there some way of late binding Excel so that it won't make it change the reference to Excel 15.0 or some workaround to deal with users who have 2007, 2010 and 2013? Any advice is much appreciated.

[MikeTheBike]

Hi

Looking at the sample coder you still seem to be early binding as you have a variable declared as Excel.Workbook.

If you do not get a compile error then it would seem you also have reference set to the Excel object library. This will need removing. Any references to the Excel library will then cause a compile error and you can then change the variable to an Object type.

However this does impose a strict discipline requiring you to explicitly refer to the Excel application's object, properties and methods.

ie

XlApp.Cells(1,2)= "some value"

I never use early binding in Access when automation Excel.

HTH


MTB

[jforbes]

To program Excel with Late Binding, MikeTheBike has you covered:

1. Remove all references to Office
2. Declare your variables as Objects

Late Binding works just fine, but you'll loose Intellisense which is a drag. Another option is to split the Front End and Back End and then deploy a Front End to each of your users, so they have their own FE database that they can destroy without affecting your other users. You may already know all about splitting the database, but just in case: http://bytes.com/topic/access/insigh...ront-back-ends

[BikeToWork]

The code I put in the original post was a bad example. What I'm having trouble with is calling functions that use the worksheet object, to write text to a spreadsheet. For example the following code:

Sub ChoiceReport(ByRef RS As DAO.Recordset, strWKB As String, strCaption As String)
'Dim wks As Worksheet
 
 
Dim lngRow As Long
Dim rrow As Integer:      rrow = 3
Dim actionFlag As String: actionFlag = ""
Dim xlAPP As Object
Dim WKB As Object
Dim WKS As Object
 
Set xlAPP = CreateObject("Excel.Application")
Set WKB = xlAPP.workbooks.Open(strWKB)
 
 
 
 
 
    Set WKS = WKB.Worksheets("ChoiceRpt")
 
 
    writeCellText WKS, rrow - 2, 1, strCaption, actionFlag, "Center"
 

This calls the "writeCellText" function:

Sub writeCellText(w As Object, ssRow As Integer, ssCol As Integer, someText, actionFlag As String, alignment As String)
 
    '   -------------------------------------------------------
    '   Write the value into the cell - that's the easy part...
    '   -------------------------------------------------------
    w.Cells(ssRow, ssCol) = IIf(IsNull(someText), "", someText)

It does not write any text to the spreadsheet. I think the problem is that the "writeCellText" function doesn't know what the WKS object is that is passed to it as an object. How does one get around this? Do I need to send the string name of the WKS and then set it up with the xlApp application variable? Thanks for advice.

[jforbes]

This is working for me:

Sub ChoiceReport()
 
    Dim rrow As Integer: rrow = 3
    Dim actionFlag As String: actionFlag = ""
    Dim xlAPP As Object
    Dim WKB As Object
    Dim WKS As Object
 
    Set xlAPP = CreateObject("Excel.Application")
    Set WKB = xlAPP.workbooks.Open("C:\Temp\Bytes\sampleTemp.xls")
    Set WKS = WKB.Worksheets("ChoiceRpt")
 
    writeCellText WKS, rrow - 2, 1, "Test Text", actionFlag, "Center"
 
    WKB.Save
    WKB.Close
End Sub
 
Sub writeCellText(w As Object, ssRow As Integer, ssCol As Integer, someText, actionFlag As String, alignment As String)
     w.Cells(ssRow, ssCol) = IIf(IsNull(someText), "", someText)
End Sub

Are you getting an error? Also, you can get into that situation where the file is opened but not closed and then things start acting weird. Maybe close Access and attempt to rename the Excel File and try again.

[BikeToWork]

JForbes, thanks for your help. That does work. Now that I am using late binding on this Access project, the spreadsheets seem to take extra long to generate. Is that to be expected?

[jforbes]

It will take longer. The calls themselves are supposed to take about twice as long to make and depending on how busy your application is, it could start adding up.

Here is a pretty good article on what you are doing: https://support.microsoft.com/en-us/kb/245115