Late Binding: extract Access to Excel with formatting

[roelandwatteeuw]

Hi all

Situation:
I'm extracting an Access query to Excel with VBA.
In this excel, I let VBA do a few things like formatting the cells.

I made the code work and the sheet looks good, but I'm not sure if it's all versions proove.

For my code I have put a check in the references at 'Microsoft Excel 16.0 Object Library'
And this all works.
If I uncheck the reference, VBA returns errors.
No problem so far... just keep it on!?

Problem:
But what if an other user doesn't have it checked? Or has an older version of the Object Library?
It would give an error for him, wouldn't it?

I read things about early and late binding, where the early binding could give problems with this.
So I tried to change it to a late binding (using Objects).

When unchecking the Excel Object Library box, the code will still return errors.
Did I do something wrong? (Probably!)


Extra info:
sQuery = Name from the Query
sPath = Filepath where the sheet needs to be added
Both come from other sub

This is my code:

Private Sub cmdTransfer(ByVal sQuery As String, sPath As String)
                                                                                
On Error GoTo SubError

    Dim xlApp           As Object   
    Dim xlBook          As Object
    Dim xlSheet         As Object
    
    Dim rsl             As DAO.Recordset
    Dim iCols           As Integer
    Dim i               As Integer
    Dim bExcelOpened    As Boolean
    
    'Change cursor to hourglass
    DoCmd.Hourglass (True)
    
    '**********************************************************
    '                     GET DATA
    '**********************************************************

    'Put sQuery in recordset
    Set rsl = CurrentDb.OpenRecordset(sQuery, dbOpenSnapshot)

    'If empty > Exit Excel
    If rsl.RecordCount = 0 Then
        MsgBox "no data"
        GoTo SubExit
    End If
    
    '**********************************************************
    '                  SPREADSHEET BUILD UP
    '**********************************************************
    
    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")    'Bind to existing instance of Excel
 
    If Err.Number <> 0 Then    'Could not get instance of Excel, so create a new one
        Err.Clear
        On Error GoTo SubError
        Set xlApp = CreateObject("Excel.Application")
        bExcelOpened = False
    Else    'Excel was already running
        bExcelOpened = True
    End If
    
    xlApp.Visible = False
    xlApp.screenupdating = False
    
    'Open Workbook on sPath
    Set xlBook = xlApp.Workbooks.Open(sPath)
    'Set sheet as first sheet
    Set xlSheet = xlBook.sheets.Add(Before:=xlBook.sheets(1))
    
    With xlSheet
        'General formatting sheet
        .Name = "List"
        .Tab.ColorIndex = 6
        .Cells.Font.Name = "Calibri"
        .Cells.Font.Size = 11
        
        'Add titles from table
        For iCols = 0 To rsl.Fields.Count - 1
            .Cells(3, iCols + 1).Value = rsl.Fields(iCols).Name
        Next
        
        'Format titles from tabel
        With xlSheet.Range(xlSheet.Cells(3, 1), _
            xlSheet.Cells(3, rsl.Fields.Count))
            .Font.Bold = True
            .Font.ColorIndex = 2
            .HorizontalAlignment = xlCenter
        End With
        
        'Add BIG title in A1
        With .Range("A1")
            .Value = "This sheet contains the list"
        End With
        
        'Format BIG title
        With xlSheet.Range(xlSheet.Cells(1, 1), _
            xlSheet.Cells(1, rsl.Fields.Count))
            .Merge
            .Cells.Font.Size = 15
            .Font.Bold = True
            .Font.ColorIndex = 1        'See: https://analysistabs.com/excel-vba/colorindex/
            .Interior.ColorIndex = 36
            .HorizontalAlignment = xlCenter
        End With
                
        'Get Data from sQuery
        .Range("A4").CopyFromRecordset rsl
  
        'Format first column
        With xlSheet.Columns("A:A")
            .VerticalAlignment = xlCenter
            .Font.Bold = True
        End With
        
        'Define range from Data sQuery
        Dim indexLastColumn As Integer
            indexLastColumn = .Cells(4, .Columns.Count).End(xlToLeft).Column
        Dim indexLastRow As Integer
            indexLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        Dim RangeTabel As Range
            Set RangeTabel = xlSheet.Range(xlSheet.Cells(3, 1), xlSheet.Cells(indexLastRow, indexLastColumn))
                
        'Name Table
        Dim TableName As String
            TableName = "Tbl_List"
        
        'Format data sQuery as Table and name it
        xlSheet.ListObjects.Add(xlSrcRange, RangeTabel, , xlYes).Name = TableName

        'Format Table
        With xlSheet.ListObjects(TableName)
            .TableStyle = "TableStyleMedium2"
            .Range.AutoFilter
        End With

        'ReDefine LastRow
        indexLastRow = indexLastRow + 4
        
        'Add text in new last line + merg, colour and borders
        With xlSheet.Range(xlSheet.Cells(indexLastRow, 1), _
            xlSheet.Cells(indexLastRow, indexLastColumn))
            .Merge
            .Value = "For more information, contact me"
            .Interior.ColorIndex = 36
            .Borders(xlEdgeRight).LineStyle = XlLineStyle.xlContinuous
            .Borders(xlEdgeLeft).LineStyle = XlLineStyle.xlContinuous
            .Borders(xlEdgeTop).LineStyle = XlLineStyle.xlContinuous
            .Borders(xlEdgeBottom).LineStyle = XlLineStyle.xlContinuous
        End With
      
        'Autofit rows and columns
        .Columns("B").ColumnWidth = 120        'first wide to prevent early text wrap
        .Rows.AutoFit
        .Columns.AutoFit
        
        'Select first cel
        .Range("A1").Select
        'Select first sheet in workbook
        xlBook.sheets(1).Select
    End With
    
    xlApp.DisplayAlerts = False
        xlBook.Close True, sPath    'Save and close the workbook
    xlApp.DisplayAlerts = True
    
    'Close excel if is wasn't originally running
        If bExcelOpened = False Then
            xlApp.Quit
        End If
 
SubExit:
On Error Resume Next
    DoCmd.Hourglass False
    xlApp.Visible = True
    rsl.Close
    Set rsl = Nothing
    Set xlBook = Nothing
    Set xlSheet = Nothing
    xlApp.screenupdating = True
    Set xlApp = Nothing
    
    Exit Sub
    
SubError:
Error_Handler:
    MsgBox "An error occured:" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: cmdTransfer" & vbCrLf & _
           "Error Description: " & Err.Description _
           , vbOKOnly + vbCritical, "Error!"
     Resume SubExit
End Sub

Many thanks for helping me clearing this out!

Greetz
R

 

[Micron]

If you can't specify the oldest version you'd expect a user to have, then yes, likely would raise an error about a missing reference. Late binding should work as long as you don't invoke any intrinsic functions (i.e. methods, properties, events) that don't exist in the oldest version being used. You should always point out which line causes the error though, as it's not obvious here. If you did that, I missed it.

 

[roelandwatteeuw]

I know, it's quite a long code, so that makes it hard for others to point the specific error.
The errors are on multiple lines, that's why I think I'm doing the basic wrong.

This are the errors if I uncheck the Excel Object Library in the references:

ERRORS BEFORE THE SUB ACTUALY RUNS:

With xlSheet.Range(xlSheet.Cells(3, 1), _ xlSheet.Cells(3, rsl.Fields.Count)) .Font.Bold = True .Font.ColorIndex = 2 .HorizontalAlignment = xlCenter End With

>> ERROR << Compile error: Variable not defined >> This error is several time in the code. Replace it by xlSheet.xlCenter?

Dim indexLastColumn As Integer indexLastColumn = .Cells(4, .Columns.Count).End(xlToLeft).Column

>> ERROR << Compile error: Variable not defined >> Replace it with xlSheet.xlToLeft? >> Same for xlUp >> xlSheet.xlUp?

Dim RangeTable as Range

>> ERROR << Compile error: User-defined type not defined >> Don't know how to fix it

Skipping the previous one would result in undefined variable Set RangeTable = xlSheet.Range(xlSheet.Cells(3, 1), xlSheet.Cells(indexLastRow, indexLastColumn))

>> ERROR << Compile error: Variable not defined >> Don't know how to fix it

xlSheet.ListObjects.Add(xlSrcRange, RangeTable, , xlYes).Name = Tabelname

>> ERROR << Compile error: Variable not defined >> Replace it by xlSheet.xlSrcRange? >> RangeTable same problem as above, variabel not defined

With xlSheet.Range(xlSheet.Cells(indexLaatsteRij, 1), _ xlSheet.Cells(indexLaatsteRij, indexLaatsteKolom)) .Merge .Value = "Bij twijfel, vragen of opmerkingen kunnen jullie steeds de dienst HR-Rapportering contacteren." .Interior.ColorIndex = 36 .Borders(xlEdgeRight).LineStyle = XlLineStyle.xlContinuous .Borders(xlEdgeLeft).LineStyle = XlLineStyle.xlContinuous .Borders(xlEdgeTop).LineStyle = XlLineStyle.xlContinuous .Borders(xlEdgeBottom).LineStyle = XlLineStyle.xlContinuous End With

>> ERROR << Compile error: Variable not defined >> Replace it by xlSheet.XlLineStyle.xlContinuous and xlSheet.xlEdgeRight/Left/Top/Bottom?

ERRORS WHEN SUB RUNS:

Return from my error handler: Error Number: 438 Error Source: cmdTransfer Errpr Description: Object doesn't support this property or method >> Error occures after the line where I replaced xlCenter to xlSheet.xlCenter >> So bad solution

 

[Micron]

OK, next time please just post the code within code tags (as you did) and use comments within to detail the error lines. That's because when you flag an error as in that last post, we're scrolling up and down to see how things were declared, checking the syntax etc. etc. It's a lot of up and down.

ERRORS BEFORE THE SUB ACTUALY RUNS:

I guess that means those errors are compile errors, so...
I don't do a lot of late binding but from what I recall, it usually won't compile because of the lack of a reference. You need to test/run on the development machine as well as on other machines if they have earlier versions. AFAIK, you only need to do this on the one with the earliest version as any later version would be forward compatible if the oldest is compatible.

Variable not defined could mean that those references are being interpreted as variables and you are using Option Explicit (I always do).
This I don't see in your code so can't offer an idea: xlSheet.xlCenter

Perhaps add the reference and see if it compiles. If so, your syntax should be good. Then remove the reference and test again without compiling.

 

[roelandwatteeuw]

*Close*

 

[Peter_SSs]

*Close*

I assume that means you have a solution.

If you would like to post the solution then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.

 

[RoryA]

For anyone else in a similar situation, if you late bind, then you cannot use constants from the relevant library without declaring them yourself - for example:

Const xlCenter As Long = -4108

and, since that is what late binding means, you also need to declare all objects from the relevant library as a generic Object type - so:

Dim RangeTable as Object

 

[james_lankford]

and as a side note -- we have some ridiculous settings in our anti-virus software at work where we CANNOT do late binding

this line will caught and flagged by the AV software

Set xlApp = CreateObject("Excel.Application")

the security group's solution was to make a C:\scripts folder on any pc that "needed" it (they decide who needs it)
and tell people that any program that does CreateObject should be put in that folder
because they put an exclusion for that rule in that folder

PITA

 

[Micron]

That makes no sense to me because that is the syntax whether or not you late or early bind?

The difference is in the declaration?

Use early binding and late binding in Automation - Office

Explains the types of binding available to Automation clients, and weighs both sides of each method.

learn.microsoft.com

 

[james_lankford]

in that first one you don't need createobject if you referenced the excel library