Why My Method Calls in Excel Automation are Late Bound
Why My Method Calls in Excel Automation are Late Bound
Why My Method Calls in Excel Automation are Late Bound
Why My Method Calls in Excel Automation are Late Bound
Why My Method Calls in Excel Automation are Late Bound
Why My Method Calls in Excel Automation are Late Bound Why My Method Calls in Excel Automation are Late Bound Why My Method Calls in Excel Automation are Late Bound Why My Method Calls in Excel Automation are Late Bound Why My Method Calls in Excel Automation are Late Bound Why My Method Calls in Excel Automation are Late Bound Why My Method Calls in Excel Automation are Late Bound Why My Method Calls in Excel Automation are Late Bound
Why My Method Calls in Excel Automation are Late Bound Why My Method Calls in Excel Automation are Late Bound
Why My Method Calls in Excel Automation are Late Bound
Go Back  Xtreme Visual Basic Talk > > > Why My Method Calls in Excel Automation are Late Bound


Reply
 
Thread Tools Display Modes
  #1  
Old 07-27-2011, 10:23 AM
stevenw1956 stevenw1956 is offline
Newcomer
 
Join Date: Jul 2011
Posts: 21
Default Why My Method Calls in Excel Automation are Late Bound


I wonder whether someone could help me understand why some of my calls to methods in an Excel Automation project are late bound. I am new to this, so please be patient. I am using Excel XP (2002). I downloaded and installed the PIA's for Office Automation in this version. The PIA's were installed and registered by a batch file in the downloaded installation. I added a reference to Microsoft Excel 10.0 Object Library in my project. But I had to turn Option Strict Off in order to make calls like the following work. The compiler was complaining that they were late bound (and Option Strict On does not allow late binding). I've read explanations of early and late binding. But I can't understand why these calls are late bound. Can someone please explain

Thanks

Code:
'Define Microsoft Excel Objects
    Dim oXL As Excel.Application
    Dim oWB, oWBOld As Excel.Workbook
    Dim oMLSheet, oAct1Sheet, oOldSheet As Excel.Worksheet
    Dim oRng As Excel.Range

 ' Open MainList Workbook and Create Worksheet Objects
        oWB = oXL.Workbooks.Open("E:\My Documents\Excel Files\Michael Hughes\Test Folder\MainList.xls")
        oWBOld = oXL.Workbooks.Open("E:\My Documents\Excel Files\Michael Hughes\Test Folder\OldFile.xls")
        oMLSheet = CType(oWB.Worksheets("MainList"), Excel.Worksheet)
        oAct1Sheet = oWB.Worksheets("Act-1")
        oOldSheet = oWBOld.Worksheets(1) 

Do While oAct1Sheet.Cells(iAct1, 1).Value IsNot Nothing     'Read Act-1 to End of data
            If oMLSheet.Cells(iMainList, 1).Value < _
               oAct1Sheet.Cells(iAct1, 1).Value Then
                iMainList = iMainList + 1
            ElseIf oMLSheet.Cells(iMainList, 1).Value > _
               oAct1Sheet.Cells(iAct1, 1).Value Then
                iAct1 = iAct1 + 1
            ElseIf oMLSheet.Cells(iMainList, oMLSheet.Range("A2").Column).Value = _
                   oAct1Sheet.Cells(iAct1, oAct1Sheet.Range("A2").Column).Value Then
                oAct1Sheet.Rows(iAct1).Delete()    'Delete row from Act-1 because it is a duplicate
Reply With Quote
  #2  
Old 07-28-2011, 05:25 AM
DrPunk's Avatar
DrPunkWhy My Method Calls in Excel Automation are Late Bound DrPunk is offline
Senior Contributor

* Expert *
 
Join Date: Apr 2003
Location: Never where I want to be
Posts: 1,403
Default

Hopefully I'm right on this one...

It's because Worksheets([Index]) returns type Object rather than returning a Worksheet (note when you open the quotes to specify the sheet index it says there that it's returning an Object type).

You can leave option strict on by casting the returned object to the type you know it's going to be...

Code:
oOldSheet = CType(oWBOld.Worksheets(1), Excel.WorkSheet)
__________________
There are no computers in heaven!
Reply With Quote
  #3  
Old 07-28-2011, 08:01 AM
stevenw1956 stevenw1956 is offline
Newcomer
 
Join Date: Jul 2011
Posts: 21
Default

What about these lines for instance:

Code:
Do While oAct1Sheet.Cells(iAct1, 1).Value IsNot Nothing

oMLSheet.Cells(iMainList, oMLSheet.Range("A2").Column).Value

oAct1Sheet.Rows(iAct1).Delete()
They are representative of other lines that are giving the message:

Option Strict On Disallows Late Binding
Reply With Quote
  #4  
Old 07-28-2011, 09:52 AM
DrPunk's Avatar
DrPunkWhy My Method Calls in Excel Automation are Late Bound DrPunk is offline
Senior Contributor

* Expert *
 
Join Date: Apr 2003
Location: Never where I want to be
Posts: 1,403
Default

I believe the issue is Cells and Rows return an Object but you can cast them both to Excel.Range.

Code:
CType(oAct1Sheet.Cells(iact1, 1), Excel.Range).Value
__________________
There are no computers in heaven!
Reply With Quote
  #5  
Old 07-28-2011, 01:33 PM
stevenw1956 stevenw1956 is offline
Newcomer
 
Join Date: Jul 2011
Posts: 21
Default

Thanks Dr. Punk, that did indeed work on this line:
Code:
Do While CType(oAct1Sheet.Cells(iAct1, 1), Excel.Range).Value IsNot Nothing
but on this one:
Code:
If CType(oMLSheet.Cells(iMainList, 1), Excel.Range).Value < _
               CType(oAct1Sheet.Cells(iAct1, 1), Excel.Range).Value Then
it created another error to the effect that Option Strict On does not support the use of the '>' operator for operand of type Object.

I thought if there was a straightforward way to eliminate these problems, I would rather turn option Strict back on. But seeing how many difficulties there are with that in these COM automation programs, I think I will just be satisfied to turn Option Strict Off in all such programs and let the compiler deal with the necessary conversions.
Reply With Quote
  #6  
Old 08-11-2011, 06:53 PM
DennisW's Avatar
DennisW DennisW is offline
Junior Contributor
 
Join Date: Mar 2006
Location: Östersund, Sweden
Posts: 268
Default

Although we recommend to turn Option Strict On it's easier to turn it Off. To get the intellisense You may first turn it On and when debugging turn it off.
__________________
Kind regards,
Dennis

.NET & Excel | 2nd edition PED | MVP
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Why My Method Calls in Excel Automation are Late Bound
Why My Method Calls in Excel Automation are Late Bound
Why My Method Calls in Excel Automation are Late Bound Why My Method Calls in Excel Automation are Late Bound
Why My Method Calls in Excel Automation are Late Bound
Why My Method Calls in Excel Automation are Late Bound
Why My Method Calls in Excel Automation are Late Bound Why My Method Calls in Excel Automation are Late Bound Why My Method Calls in Excel Automation are Late Bound Why My Method Calls in Excel Automation are Late Bound Why My Method Calls in Excel Automation are Late Bound Why My Method Calls in Excel Automation are Late Bound Why My Method Calls in Excel Automation are Late Bound
Why My Method Calls in Excel Automation are Late Bound
Why My Method Calls in Excel Automation are Late Bound
 
Why My Method Calls in Excel Automation are Late Bound
Why My Method Calls in Excel Automation are Late Bound
 
-->