VB 6.0 to .NET upgrade (Excel questions)

[Mortious]

In VB6.0, I have the following code. With this code Im able to get a valid count of all of the rows "populated" or as we call it a "record count", and pulling the sheet name from sheet 1.

Dim wkb As Excel.Workbook
Dim wks As Excel.Worksheet

Set wkb = Workbooks.Open(CommonDialog1.FileName)  Open your file
            
xlsWorkSheet = wkb.Worksheets(1).Name  Gives name of first worksheet.
                     
Set wks = wkb.Worksheets.Item(1)   Set up the worksheet
                     
wks.UsedRange.Select  Grab all used rows
                             
XLSCount = Selection.Rows.Count  Count the used rows
                     
wkb.Close True, CommonDialog1.FileName  Close the file when your done.

When I upgraded this code to .NET, Im having issues with this.

I have all variables already declared (I omitted them for clarity).

 

[dynamic_sysop]

heres a quick example i put together , hope it helps.

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim oDialog As New OpenFileDialog()
        oDialog.Filter = "Excel files|*.xls"
        oDialog.ShowDialog()
        Dim objExcel As New Excel.Application()
        Dim objBook As Excel.Workbook = objExcel.Workbooks.Open(oDialog.FileName)
        Dim objSheet As Excel.Worksheet = objBook.Worksheets.Item("Sheet1")
        Dim x As Integer
        Try
            For x = 1 To objSheet.UsedRange.Count
                MessageBox.Show(objSheet.UsedRange(x).value)
            Next
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            objSheet.Delete()
            objBook.Application.Quit()
            objExcel.Application.Quit()

            objSheet = Nothing
            objBook = Nothing
            objExcel = Nothing
        End Try

    End Sub

thats using excel 10.0 object library btw.

 

[Mortious]

Will this work for the Excel 9.0 library?

 

[dynamic_sysop]

yes it should , thats also for excel 97 / 2000 etc...

 

[Mortious]

Ok I have worked with this a bit and took what I needed to try and make it work, the closest I have so far..

This code snip:

objSheet.UsedRange.Count

When I evaluate it in debug, it returns a value of 3987. The excel sheet Im pulling from only has 400 rows that are actually populated, so its not quite there... Im sure Im missing one thing, I just cant seem to put my finger on it.

 

[Mortious]

Ahh just figured it out.

Its counting all cells used.