How do I import an excel spreadsheet into a Visual Basic Application

Question

I have to write an application in Visual Basic.Net that will open an excel file, run through the contents an export a test file for processing.

The application works great for me (Windows 7) but when transferred to a WinXP PC gives the following error: HResult 0X800A03EC

I can replicate this error by making the Excel Application visible so it seems like it is an issue where it can't access the file because it is open. Error occurrs when trying open the workbook.

Original Code

    Dim excel As Excel.Application
    Dim wb As Excel.Workbook
    excel = New Excel.Application
    excel.Visible = False
    excel.UserControl = False
    wb = excel.Workbooks.Open(FileLocation)
    ws = wb.Worksheets(1)
    ws.Activate()

Code To Replicate Error

    Dim excel As Excel.Application
    Dim wb As Excel.Workbook
    excel = New Excel.Application
    excel.Visible = True
    excel.UserControl = False
    wb = excel.Workbooks.Open(FileLocation)
    ws = wb.Worksheets(1)
    ws.Activate()

Answer 1

Dim cn as ADODB.Connection
Set cn = New ADODB.Connection
With cn
 .Provider = "Microsoft.Jet.OLEDB.4.0"
 .ConnectionString = "Data Source=C:\MyFolder\MyWorkbook.xls;" & _
"Extended Properties=Excel 8.0;"
  .Open
 End With

This is how you would connect to the database. Choose your approach on reading this into a DataSet or DataTable.

Don't use the interop unless completely needed I found this way works the best using a OLE connection and it's much faster. Then you have all Data in front to do what you like and you don't need MS Excel running in the back with it's messy COM objects.