Solved

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

Posted on 2009-05-11
14
331 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
 
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
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…
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

862 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

23 Experts available now in Live!

Get 1:1 Help Now