[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 195
  • Last Modified:

writting to Excel spreadsheet using vb.net

I am trying to open and write to an existing Excel worksheet but I get the error "ComExecption was unhandled, Exception from HRESULT: 0x800A03EC"  when I try to write to a cell.
I haven't been able to find out what this means.
 Imports Excel = Microsoft.Office.Interop.Excel

 Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim DGV1 As DataGridView = DataGridView1
        Dim app As New Excel.Application
        Dim wb As Excel.Workbook = app.ActiveWorkbook
        'Dim sht As Excel.Worksheet
        Dim NumCols As Integer = DGV1.Columns.Count
        Dim filePath As String = System.IO.Path.Combine(My.Computer.FileSystem.SpecialDirectories.MyDocuments, "Master.xls")

        wb = app.Workbooks.Open(filePath)
        app.Visible = True

        'copy header names into spreadsheet.
        For i As Integer = 0 To NumCols
            For Each col As DataGridViewColumn In DataGridView1.Columns
                wb.Sheets(1).Cells(i + 6, 0) = col.HeaderText
            Next
        Next
    End Sub

Open in new window

All the code examples that I have found seem fairly similar to my code.  I'm guessing I made a simple mistake but I can't figure out where.  Any and all help is welcome.
0
NevSoFly
Asked:
NevSoFly
  • 4
  • 3
1 Solution
 
NevSoFlyAuthor Commented:
Thanks that was a stupid oversight on my part, but it didn't solve my problem.  I am using the nested loops to copy data from a datagridview into the appropriate cells in the worksheet.
0
 
NevSoFlyAuthor Commented:
To be more accurate I'm trying to transfer over the column names from the datagrid to the spreadsheet and I needed to make sure that any changes to the datagrid was reflected in the spreadsheet.
0
 
anarki_jimbelCommented:
Disregard my previous comment. I've deleted it.

Do not use zero index. Start from "1". I tried - it works,.

        Dim worksheet As Excel.Worksheet = wb.Sheets(1)

        'copy header names into spreadsheet.
        For i As Integer = 1 To NumCols
            For Each col As DataGridViewColumn In DataGridView1.Columns

                worksheet.Cells(i + 6, 1) = col.HeaderText
            Next
        Next

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
NevSoFlyAuthor Commented:
I still get the same error.  I commented out the loops and just used:

wb.Sheets(1).Cells(6, 0) = "xxxxx"

and I still get the same error.
0
 
anarki_jimbelCommented:
Please read carefully: "Do not use zero index."

In my code above there are no '0' indexes. Not for sheets, not for cells. '0' index causes the error.
0
 
NevSoFlyAuthor Commented:
thank you very much I did misread your post.
0
 
anarki_jimbelCommented:
Glad to help! :)
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now