PDA

View Full Version : Access VBA - Writing to excel with Late Binding



dpimental
2012-03-09, 02: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, 03: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, 03: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)

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 ...


.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, 07:52 PM
You need to declare all the constants, they are not available in late binding



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



With.Range("A2").

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

dpimental
2012-03-09, 09:07 PM
Thanks for the help Bob. Also, do you know of any comparisons between early and late binding specific to Excel?

Bob Phillips
2012-03-10, 12:50 AM
Comparisons in what respect?