Solved

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

Posted on 2009-05-11
14
328 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
Comment Utility
0
 
LVL 30

Author Comment

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

Expert Comment

by:mohan_sekar
Comment Utility
Why wouldn't that help you?
0
 
LVL 30

Author Comment

by:LZ1
Comment Utility
It is throwing an ISAM error
0
 
LVL 30

Author Comment

by:LZ1
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 96

Expert Comment

by:Bob Learned
Comment Utility
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
Comment Utility
0
 
LVL 30

Accepted Solution

by:
LZ1 earned 0 total points
Comment Utility
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
Comment Utility
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
Comment Utility
@ 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
Comment Utility
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

744 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now