Error when creating MS Excel docs with VB 2010

Question

I'm having some trouble with looping and creating MS Excel docs, code snippet below

Private Sub selectedRowsButton_Click( _
ByVal sender As Object, ByVal e As System.EventArgs) _
Handles selectedRowsButton.Click

    Dim selectedRowCount As Integer = _
        DataGridView1.Rows.GetRowCount(DataGridViewElementStates.Selected)

    If selectedRowCount > 0 Then

        Dim sb As New System.Text.StringBuilder()
        Dim objexcel As New Excel.Application
        Dim i As Integer
        Dim FACode As Integer
        Dim Sitename As Integer
        Dim Sitecode As Integer
        Dim Address As Integer
        Dim City As Integer
        Dim State As Integer
        Dim ZIP As Integer


        FACode = 1
        Sitename = 5
        Sitecode = 2
        Address = 6
        City = 7
        State = 9
        ZIP = 10
        Dim xlWorkbook As Excel.Workbook
        xlWorkbook = objexcel.Workbooks.Open("template path")
        For i = 0 To selectedRowCount - 1

            objexcel.Visible = True
            objexcel.Range("B2").Value = DataGridView1.SelectedCells(Sitename).Value.ToString()

            objexcel.Range("B3").Value = DataGridView1.SelectedCells(Sitecode).Value.ToString()

            objexcel.Range("B5").Value = DataGridView1.SelectedCells(FACode).Value.ToString()

            Dim thisfile As Object


            thisfile = objexcel.Range("B5").Value & "." & _
            objexcel.Range("B3").Value & "." & "otherstring" & "." & "otherstring2" & "." & ".xls"

            With objexcel

                xlWorkbook.SaveAs(Filename:="c:\test\" & thisfile)
                '~~> Close the Excel file without saving
                xlWorkbook.Close(False)
            End With          

        Next i

    End If

I'm getting the error Exception from HRESULT: 0x800A03EC for the statement

  objexcel.Range("B2").Value = DataGridView1.SelectedCells(Sitename).Value.ToString()

IF I select only one row of my DataGrid before creating the program works fine, it is when I select multiple rows that this error occurs. Since I'm creating the program specifically for multiple row selections I'm stumped as to where I've gone wrong. Any help or pointers appreciated, Thanks!

Answer 1

Two things

1. You have declared objexcel As Excel.Application so you shouldn't use objexcel.Range("B2").Value. Use xlWorkbook.Range("B2").Value. Change it everywhere in your code.

2. You cannot use SaveAs like that. See the snapshot below. If you want to save as xls file then you have to use FileFormat:=56

See this code example

'~~> Save As file
xlWorkbook.SaveAs(Filename:="c:\test\" & thisfile, FileFormat:=56)

If you do not specify the file format then you will get an error message when you open the file after opening.

You might want to look at this link on how to automate Excel from VB.Net

Topic: VB.NET and Excel

Link: http://www.siddharthrout.com/vb-dot-net-and-excel/

I am not too sure what you exactly are trying to do with the DGV. Like Sean mentioned you are not incrementing the values. If you can post a snapshot of how your DGV looks and how your Excel file should look after the export then we can help you in a much better way :)