CODEWITHSUNDEEP
vb.netexceldatatable
George
George

Reputation: 113

How to transfer a data table in VB.NET to Excel

I have a data table in VB.net that I am trying to send to a specific range in an Excel spreadsheet. However, upon running the program I get the error:

An exception of type 'System.Runtime.InteropServices.COMException' occurred in MeasurementFinder.dll but was not handled in user code

Additional information: Exception from HRESULT: 0x800A03EC

The error alerts on the following sub:

Private Sub WriteDataTableToRng(targetWs As Excel.Worksheet, anchor As Excel.Range, tbl As System.Data.DataTable)
    'This sub writes the given tbl to the targetWs as a range with its top left cell acting as anchor
    Dim wRange As Excel.Range = anchor 'wRange = write range. This range represents the cell being written to over every iteration

    For Each colm As DataColumn In tbl.Columns 'This loop writes the column names into the target ws
        targetWs.Range(wRange).Value2 = colm.ColumnName '**THIS LINE IS CALLED OUT BY THE ERROR
        wRange = wRange.Offset(0, 1)
    Next colm
    wRange = anchor.Offset(1, 0)
    For Each row As DataRow In tbl.Rows
        For Each col As DataColumn In tbl.Columns
            targetWs.Range(wRange).Value2 = tbl.Rows.Item(tbl.Rows.IndexOf(row)).Item(tbl.Columns.IndexOf(col)) '**THIS LINE IS CALLED OUT BY THE SAME ERROR IF THE PREVIOUS LOOP IS COMMENTED OUT
        Next col
    Next row
End Sub

The sub that calls the previous one is:

Private Sub ReportOnTube(TubeID As Integer)
    'This sub creates an Excel workbook that acts as a report on a tube, given its ID
    'The report has a worksheet for each measurement tied to the tube (From the Gauge DB)

    'Verify the tube is in the DB
    Dim TubeExists As Boolean
    TubeExists = VerifyTube(TubeID)
    If TubeExists Then
        'Create a new excel workbook and name/time stamp it
        Dim wb As Excel.Workbook = Me.Application.Workbooks.Add()
        wb.SaveAs("C:\Gauge Reports\Tube " & TubeID & System.DateTime.Now.ToString(" HH_mm_ss dd-MM-yyyy"))
        'Add a worksheet for each measurement tied to the tube
        Dim ws As Excel.Worksheet
        ws = wb.Worksheets.Add
        Dim aRng As Range
        aRng = ws.Range("B2")
        TubesConn.Close()
        TubesConn.Open()
        Dim selectTbl As New SqlCommand("SELECT * FROM [Tubes]", TubesConn)
        Dim rdr As SqlDataReader
        Dim aTbl As New System.Data.DataTable
        rdr = selectTbl.ExecuteReader()
        aTbl.Load(rdr)
        Call WriteDataTableToRng(ws, aRng, aTbl)
        TubesConn.Close()
    End If
End Sub

I am using the following imports:

Imports System.Data.Sql
Imports System.Data.SqlClient
Imports System.Data
Imports System.IO
Imports Microsoft.Office.Interop.Excel

What I intend to do is iterate through the given data table and write the values in the table to a range in the spreadsheet whose top left corner is given by the "anchor" range variable. I have no warnings from Visual Stuio's IntelliSense, so I don't really know where to start with this one.

Thanks in advance!

Upvotes: 0

Views: 808

Answers (1)

JNevill
JNevill

Reputation: 50263

targetWs.Range(wRange).Value2 = colm.ColumnName is redundant/wrong. The excel range object stored in wRange is already a property of the worksheet in which it's contained.

In other words, if you have it print out wRange.Parent.Name you will get the worksheet that the range is in. You can't have a range point to two different worksheets (well maybe through like a range union, I've never tried, but who would do that anyway, you probably can't do it... </streamOfConciousness>)

Instead, just use:

 wrange.value = colm.columnName

Upvotes: 1

Related Questions