[RESOLVED] [2005] Late Binding

[campster]

I'm opening an Excel file and using the header row to fill a list box.

The code below works until I turn on the project setting to disallow late binding. Then line in bold is producing a problem. Is there an easier way to do what I want without having to turn off the late binding warning/error?

        Dim strList As String = ""
        Dim i As Integer = 1
        Dim oSht As New Excel.Worksheet
        Dim oWB As Excel.Workbook

        Try
            'Create an Excel instance.
            oApp = New Excel.Application
            oWB = oApp.Workbooks.Open("c:\temp\test.xls")
            oSht = CType(oWB.ActiveSheet, Excel.Worksheet)

            Do
                strList = CType(oSht.Cells(1, i).value, String)
                If strList = "" Then
                    Exit Do
                End If
                lstAllVars.Items.Add(strList)
                i = i + 1
            Loop While strList <> ""

            'close and clean up resources
            oApp.Quit()
            oApp = Nothing
        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString)
        End Try

[jmcilhinney]

What exactly is being late-bound? It's not the strList variable because you're casting the value you're assigning to it. I can only assume that the result of oSht.Cells(1, i) is type Object and therefore doesn't have a Value property. You'd have to cast that reference to the appropriate type before trying to access its Value property.

[campster]

the result of oSht.Cells(1, i) is type Object and therefore doesn't have a Value property.

I'm not in front of my code right now but I think that you're right about the oSHt.Cells reference. I believe that if I turn the late binding errror flag (don't know what it is really called) that the .value method was no longer available.

So you're saying that I should try?

CType(oSht.Cells(1, i),string).value

I'll give it a shot when I get to work tomorrow.

[jmcilhinney]

It's called Option Strict.

That's not what I'm saying. I'm saying that you need to cast the result of oSht.Cells(1, i) as the actual type it is first, then access the Value property of that type, then cast that as type String. It might look something like:

strList = CStr(CType(oSht.Cell(1, i), Excel.Cell).Value))

Having said that, I'm surprised that that Cells property doesn't return a typed reference. I've not really done much Office automation so I'm really not sure of all the specifics.

[campster]

strList = CStr(CType(oSht.Cell(1, i), Excel.Cell).Value))

I remoted into my work PC and tried your suggestion. Excel.Cell is an undefined type. I'm missing something rudimentary.

This will teach me to get half way through a project and decide to change compile options.

[campster]

strList = CStr(CType(oSht.Cells(1, i), Excel.Range).Value)

This got it. thanks

[jmcilhinney]

I just made up "Excel.Cell" because it sounded like about the right thing. Notice that I said:

It might look something like

Look at the Excel Object Model documentation and see what the appropriate type is.

Edit: Too slow