title: How to transfer a data table in VB.NET to Excel
body: <p>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:</p>
  
  <blockquote>
    <p>An exception of type 'System.Runtime.InteropServices.COMException' occurred in MeasurementFinder.dll but was not handled in user code</p>
    
    <p>Additional information: Exception from HRESULT: 0x800A03EC</p>
  </blockquote>
  
  <p>The error alerts on the following sub:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>The sub that calls the previous one is:</p>
  
  <pre><code>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 " &amp; TubeID &amp; 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
  </code></pre>
  
  <p>I am using the following imports:</p>
  
  <pre><code>Imports System.Data.Sql
  Imports System.Data.SqlClient
  Imports System.Data
  Imports System.IO
  Imports Microsoft.Office.Interop.Excel
  </code></pre>
  
  <p>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.</p>
  
  <p>Thanks in advance!</p>