CODEWITHSUNDEEP
sqlvb.netexceldata-import
Silentbob
Silentbob

Reputation: 3065

Importing data from excel sheet

Using MS VS 2013 and SQL server 2012

I am writing a console app to copy some data from excel into an SQL table. I am not getting very far. The code below opens the file then after 2-3 seconds I get an error.

There error is -

Additional information: Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.Office.Interop.Excel.Worksheet'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{000208D8-0000-0000-C000-000000000046}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).

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

Module Module1

    Sub Main()

        Dim xlApp As Application
        Dim xlWorkBookSrc As Excel.Workbook
        Dim xlWorkBookDest As Excel.Workbook
        Dim xlWorkSheetSrc As Excel.Worksheet
        Dim xlWorkSheetDest As Excel.Worksheet
        xlApp = New Excel.Application
        xlApp.Visible = True

        xlApp.DisplayAlerts = False


        xlWorkSheetSrc = xlApp.Workbooks.Open("Folder path")
        xlWorkSheetSrc = xlWorkBookSrc.Worksheets("Spectrometer")


    End Sub

End Module

As the file opens ok I am not sure why I then get the error. The excel sheet is a .xls but I also tried with an .xlsx and get the same result.

Any ideas

Upvotes: 0

Views: 661

Answers (2)

Jignesh Thakker
Jignesh Thakker

Reputation: 3698

Refer Example given in Importing Exporting Excel Files

This Example in VB.NET and I tested and it's working fine in my PC.

I suggest to use OleDB (ADO.NET) to import excel data and export that data to SQL server (using SqlConnection (ADO.NET)).

Excel To SQL Server

Upvotes: 0

simon at rcl
simon at rcl

Reputation: 7344

This line:

xlWorkSheetSrc = xlApp.Workbooks.Open("Folder path")

..is failing because its defines xlWorkSheetSrc as a Worksheet and xlApp.Workbooks.Open is returning a Workbook, which is not a Worksheet. Change it to:

xlWorkBookSrc = xlApp.Workbooks.Open("Folder path")

..and it should be OK.

Upvotes: 2

Related Questions