• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 348
  • Last Modified:

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

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
LZ1
Asked:
LZ1
  • 6
  • 2
  • 2
  • +1
1 Solution
 
LZ1Author Commented:
I have looked at that one already.  Unfortunately that won't help me.  
Anything else anyone????  
0
 
mohan_sekarCommented:
Why wouldn't that help you?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LZ1Author Commented:
It is throwing an ISAM error
0
 
LZ1Author Commented:
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
 
LZ1Author Commented:
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
 
Bob LearnedCommented:
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
 
LZ1Author Commented:
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
 
Bob LearnedCommented:
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
 
LZ1Author Commented:
@ 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
 
CodeCruiserCommented:
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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 6
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now