Access VBA - Writing to excel with Late Binding

dpimental

New member
Joined
Mar 9, 2012
Messages
3
Reaction score
0
Points
0
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

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
You have asked that question on a VBA help forum. So did you think of posting the code here?
 

dpimental

New member
Joined
Mar 9, 2012
Messages
3
Reaction score
0
Points
0
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[/SIZE][/FONT]

How can I get this to work in late binding?
 
Last edited by a moderator:

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
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
 

dpimental

New member
Joined
Mar 9, 2012
Messages
3
Reaction score
0
Points
0
Thanks for the help Bob. Also, do you know of any comparisons between early and late binding specific to Excel?
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
Comparisons in what respect?
 
Top