Results 1 to 6 of 6

Thread: Access VBA - Writing to excel with Late Binding

  1. #1

    Access VBA - Writing to excel with Late Binding



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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?

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

  3. #3
    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?
    Last edited by Bob Phillips; 2012-03-09 at 06:48 PM. Reason: Added code tags

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

  5. #5
    Thanks for the help Bob. Also, do you know of any comparisons between early and late binding specific to Excel?

  6. #6


    Register for a FREE account, and/
    or Log in to avoid these ads!

    Comparisons in what respect?

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •