Access VBA - Writing to excel with Late Binding

**dpimental** · 2012-03-09, 01:22 PM

All, I am writing some vba code in Access using Late Binding. Some of my users have Office 2010, Some, 2007 and others 2003.

I am having some challenges translating between the examples of code from early binding and late binding. There are lots of examples of early binding. I am having troubles with selecting a range and applying formatting to that range - such as horizontal alignment and borders. Where can I find information? Is there any comparisons between the two?

**Bob Phillips** · 2012-03-09, 02:43 PM

You have asked that question on a VBA help forum. So did you think of posting the code here?

**dpimental** · 2012-03-09, 02:57 PM

Sorry about that. I wanted to start with basic information.

First I declared the variables as object, then set the Objects (Late Binding Method)

Code:

Dim xl As Object
Dim wbk As Object

Set xl = CreateObject("excel.Application")
xl.Workbooks.OPEN (xlTemplate)
xl.Visible = True
Set wbk = xl.ActiveWorkbook
With wbk

From here I use the worksheet.cells reference to write cell values / formulas (example below writes the order id to the cell in the 6th row, 1st column
wbk.Sheets("PO Summary").Cells(6, 1) = rst.Fields("ORDER_ID")

I have been able to do some basic formatting (bold, italic); but I can't seem to select the cell or a range to apply a border to it. I know what the methodology is in early binding; but what about in late binding?

Do I even need to select the cell? What I have seen done in early binding is like the example below ...

Code:

.Range("A2").Select
    With .Selection.Borders(xlEdgeLeft)
         .LineStyle = xlContinuous
         .ColorIndex = 0
         .Weight = xlThin
   End With

How can I get this to work in late binding?

**Bob Phillips** · 2012-03-09, 06:52 PM

You need to declare all the constants, they are not available in late binding

Code:

Dim xl As Object
Dim wbk As Object
Const xlTemplate As Long = 17
Const xlContinuous As Long = 1
Const xlThin As Long = 2

Set xl = CreateObject("excel.Application")
xl.Workbooks.OPEN (xlTemplate)
xl.Visible = True
Set wbk = xl.ActiveWorkbook
With wbk

There i no need to select the range to manipulate it

Code:

    With.Range("A2").
    
        With Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .Weight = xlThin
        End With
   End With