Solved

Importing an Excel 11 spreadsheet using OLEDB command in VB.Net into an Access 2003 table

Posted on 2009-05-11
14
338 Views
Last Modified: 2012-08-13
Hey Experts!!

As the question states:
I am trying to import an Excel spreadsheet into an Access table using an OLEDB command in VB.net.  

I cannot find anything on the internet that will help.  Basically I want to do the import like I would through the File>Import in Access, except I want to do it in VB.net.  

Thanks in advance,
0
Comment
Question by:LZ1
  • 6
  • 2
  • 2
  • +1
14 Comments
 
LVL 15

Expert Comment

by:mohan_sekar
ID: 24358349
0
 
LVL 30

Author Comment

by:LZ1
ID: 24358654
I have looked at that one already.  Unfortunately that won't help me.  
Anything else anyone????  
0
 
LVL 15

Expert Comment

by:mohan_sekar
ID: 24358875
Why wouldn't that help you?
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 30

Author Comment

by:LZ1
ID: 24364376
It is throwing an ISAM error
0
 
LVL 30

Author Comment

by:LZ1
ID: 24364732
Basically what's happening with this solution is when it runs, it's treating an alpha/numeric field in Excel as a "null" field in Access.
0
 
LVL 30

Author Comment

by:LZ1
ID: 24365031
Correction to my last post:
A SELECT statement against the Excel sheet that has a field that contains alpha AND numeric data, the numeric data is returned as null.  
This import method is not working for this reason.  I do apologize for mistake above.  I am basically a third party relaying info.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 24396225
Here is a class that I use with Excel:


Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.Text
 
Public Class ExcelDataTable
 
  Private m_adapter As OleDbDataAdapter
  Private m_connection As OleDbConnection
  Private m_connectionString As String = ""
 
  Private m_sheetName As String
  Private m_workBook As String
 
  Public Sub New(ByVal workBook As String, ByVal sheetName As String)
    m_workBook = workBook
    m_sheetName = sheetName
    m_connectionString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source={0};" & _
        "Extended Properties=""Excel 8.0;HDR={1};IMEX=1""", _
        workBook, "Yes")
  End Sub
 
  Protected Overrides Sub Finalize()
    MyBase.Finalize()
    If Not m_adapter Is Nothing Then
      m_adapter.Dispose()
    End If
  End Sub
 
  Public ReadOnly Property WorkBook() As String
    Get
      Return m_workBook
    End Get
  End Property
 
  Public ReadOnly Property SheetName() As String
    Get
      Return m_sheetName
    End Get
  End Property
 
  Public Function GetData() As DataTable
 
    Dim table As New DataTable(SheetName)
 
    Dim commandText As String = String.Format("Select * From [{0}]", SheetName)
 
    m_connection = New OleDbConnection(m_connectionString)
    m_adapter = New OleDbDataAdapter(commandText, m_connection)
 
    m_adapter.Fill(table)
 
    Return table
 
  End Function
 
  Public Sub Insert(ByVal ParamArray parameters As OleDbParameter())
    Dim sb As New StringBuilder
    Dim param As OleDbParameter
    sb.Append("INSERT INTO [" & SheetName & "] (")
 
    Dim names As New StringBuilder()
    For Each param In parameters
      names.Append(param.ParameterName & ",")
    Next param
    sb.Append(names.ToString().TrimEnd("?"c))
    sb.Append(") VALUES (")
 
    Dim values As New StringBuilder()
    For index As Integer = 1 To parameters.Length
      values.Append("?,")
    Next index
 
    sb.Append(values.ToString().TrimEnd("?"c))
    Using command As New OleDbCommand(sb.ToString(), m_connection)
      If m_connection.State = ConnectionState.Closed Then
        m_connection.Open()
      End If
      For Each param In parameters
        command.Parameters.Add(param)
      Next param
      command.ExecuteNonQuery()
    End Using
  End Sub
 
  Public Sub Update(ByVal ParamArray parameters As OleDbParameter())
    Dim sb As New StringBuilder
    sb.Append("UPDATE [" & SheetName & "] SET ")
    For Each param As OleDbParameter In parameters
      sb.Append(param.ParameterName & " = " & param.Value.ToString() & ",")
    Next param
    Using command As New OleDbCommand(sb.ToString(), m_connection)
      If m_connection.State = ConnectionState.Closed Then
        m_connection.Open()
      End If
      command.ExecuteNonQuery()
    End Using
  End Sub
 
End Class

Open in new window

0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 24396352
0
 
LVL 30

Accepted Solution

by:
LZ1 earned 0 total points
ID: 24412839
This is actually what ended up working:
I am personally not sure if this applies to any of the above answers as I am doing this for my programmer.  So if this is somehow relevant to any of the above posts, I will gladly award points.  I am however not versed at all in programming.  

Function ImportXLSFile(TableName As String, FilePath As String, FileName as string) As Boolean 
  
        If File.Exists(FilePath & "db.mdb") Then File.Delete(FilePath & "db.mdb") 
  
        'Open Access database 
        Dim 2Access as Access.Application = new Access.Application() 
        2Access.NewCurrentDatabase(FilePath & "db.mdb") 
        2Access.DoCmd.TransferSpreadsheet(Access.AcDataTransferType.acImport,Access.AcSpreadSheetType.acSpreadsheetTypeExcel9,TableName,FilePath & FileName,True,) 
        2Access.CloseCurrentDatabase() 
        2Access.Quit(Access.AcQuitOption.acQuitSaveAll) 
  
        Return True 
 End Function 

Open in new window

0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 24414106
You will need Access installed to run that code, and the end result is totally different than what you asked for (since you asked for OLEDB), but if you are happy, don't worry about me.
0
 
LVL 30

Author Comment

by:LZ1
ID: 24414230
@ TheLearnedOne:
I may have misdirected/interpreted my own question.  I was doing this after receiving the information from our resident programmer.  The question asked was originally for OLEDB because that's what he requested.  I just don't want to award points just to close the question in case anyone else ever has the issue.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 24414279
If the solution you have posted works for you and you have no problem with it(it requires ACCESS installed on the system), then we have no problem with you closing the question.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question