Range selection in excel sheet using vb.net

Question

This is my code:

Dim saveFileDialog1 As New SaveFileDialog()
saveFileDialog1.Filter = "Excel files (*.xls)|*.xls"
saveFileDialog1.Title = "Save File"
saveFileDialog1.RestoreDirectory = True

If saveFileDialog1.ShowDialog() = DialogResult.OK Then
    Try
        Dim ExcelApp As New Microsoft.Office.Interop.Excel.Application()
        ExcelApp.Application.Workbooks.Add(Type.Missing)
        ExcelApp.Cells.HorizontalAlignment = XlHAlign.xlHAlignLeft

        ' Change properties of the Workbook 
        ExcelApp.Columns.ColumnWidth = 15

        ' Storing header part in Excel
        For i As Integer = 1 To DataGridView1.Columns.Count
            ExcelApp.Cells(1, i) = DataGridView1.Columns(i - 1).HeaderText
        Next

        ' Storing Each row and column value to excel sheet
        For i As Integer = 0 To DataGridView1.Rows.Count - 2
            For j As Integer = 0 To DataGridView1.Columns.Count - 1
                ExcelApp.Cells(i + 2, j + 1) = DataGridView1.Rows(i).Cells(j).Value.ToString()
            Next
        Next

        Dim Destinationpath As String = saveFileDialog1.FileName
        ExcelApp.ActiveWorkbook.SaveAs(Destinationpath)
        ExcelApp.ActiveWorkbook.Saved = True
        ExcelApp.Quit()
        MsgBox("Record exported successfully", MsgBoxStyle.Information)
    Catch
        MsgBox("It is being used by another process.Please close it and retry.", MsgBoxStyle.Critical)
    End Try
Else
End If

Now how do I select the range using the above code which is populated in an excel sheet.

coder · Accepted Answer · 2013-03-07 20:43:47Z

5

Found the answer:

Dim lastrow As Range = ExcelApp.Rows.End(XlDirection.xlDown)
Dim findme As Range = ExcelApp.Range("A1:E" & lastrow.Row)
MsgBox("A1:E" & lastrow.Row)

answered Mar 7, 2013 at 20:43

coder

13.2k32 gold badges116 silver badges221 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jeremy Thompson Over a year ago

Just so you know, everything in VBA's object model is the same in VB.Net or C#. It just has slightly different syntax, so if you ever need to do VSTO stuff you can google the VBA equivalent and convert it to .Net.

Collectives™ on Stack Overflow

Range selection in excel sheet using vb.net

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related