Member Avatar for juster21

I am trying to program the last part of an application which needs to take data from an Excel sheet and add it to an Access table.
I'm using Excel 2003 on XP. Any and ALL help is appreciated.
The problem I'm running into seems to be on connecting to the Excel data. I'm not sure of the best way to make that connection for the purposes I need in this application. Thanks!!

  • 2 Contributors
  • 2 Replies
  • 311 Views
  • 4 Hours Discussion Span
  • Latest Post Latest Post by Oxiegen
Member Avatar for juster21

where I'm at...

Try
    Dim strConn as string = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile & ";""Extended Properties=Excel 11.0;HDR=yes;IMEX=1;"""
    Dim objConn as New OleDbConnection(strConn)
    objConn.Open()
    Dim myCmd as New OleDbCommand("SELECT * FROM [Sheet1$]", objConn)
    Dim da as New OleDbDataAdapter
    da.SelectCommand = myCmd
    Dim myDataSet as New Dataset
    da.Fill(myDataSet, "XLData")    
    myDataGrid.DataSource = myDataSet.Tables(0).DefaultView
    objConn.Close
Catch


End Try

I'm getting very weird errors on the connection...

Member Avatar for Oxiegen

a) What are the errors?
and
b) Have you ever heard of or tried GemBox Spreadsheet?
With it you can read the Excel file and import the data into a DataTable. From there you can transfer it to any database, including Access. Import/Export DataTable

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.