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]

here's 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 , that's 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 can't seem to put my finger on it.

[Mortious]

Ahh just figured it out.

 

Its counting all cells used.