How to Access Visual Basic to Import Excel Data

Written by jaime avelar
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Access Visual Basic to Import Excel Data
Use Visual Basic to import Excel spreadsheets to Access. (binary numbers image by Photosani from Fotolia.com)

Visual Basic is a Microsoft programming language that is relatively easy to use and Microsoft Access and Excel are applications offered in the Microsoft Office suite. Visual Basic.NET code is essential when you're working with Excel spreadsheets and need to import the data to an Access database. The data may be needed in Access for further data manipulation using database tools.

Skill level:
Moderate

Other People Are Reading

Instructions

  1. 1

    Start a new Visual Basic.NET project and add a "Button" to your form. Select the "Project" menu and click "<projectname> Properties." Select "References" and click "Add." Select "COM" and click "Microsoft Office 12.0 Object Library." Select "OK."

  2. 2

    Create a new Excel spreadsheet and add the following data:

    Field 1 Field 2

    Data Data 2

    Data Data 2

    Save it to "C:\" as "ExcelToImport.xls."

  3. 3

    Double-click "Button1" on your form to open "Form1.vb." Type the following under "Button1_Click" to create a sub call:

    importAllData("C:\ExcelToImport.xls", "C:\AccessFile.mdb")

        MessageBox.Show("The import is complete!")
    
  4. 4

    Type the following to create a new sub called "importAllData" that will create a new database file for importing:

    Private Shared Sub importAllData(ByVal file_path As String, ByVal db_path As String)

        Dim accessDB As Microsoft.Office.Interop.Access.Application
    
        accessDB = New Microsoft.Office.Interop.Access.ApplicationClass()
    
        accessDB.Visible = False
    
        accessDB.NewCurrentDatabase(db_path, Microsoft.Office.Interop.Access.AcNewDatabaseFormat.acNewDatabaseFormatAccess2007)
    
        accessDB.CloseCurrentDatabase()
    
        accessDB.Quit(Microsoft.Office.Interop.Access.AcQuitOption.acQuitSaveAll)
    
        accessDB = Nothing
    
        Dim con As OleDb.OleDbConnection = MakeExcelConnection(file_path)
    
    
    
        FillAccessDatabase(con, db_path, file_path)
    

    End Sub

  5. 5

    Type the following to create a new sub called "FillAccessDatabase":

    Private Shared Sub FillAccessDatabase(ByVal con, ByVal db_path, ByVal file_path)

        conn = "Provider=Microsoft.ACE.OLEDB.12.0;" &amp; "Data Source=" &amp; file_path &amp; ";" &amp; "Extended Properties=Excel 12.0;"
    
        Dim connection As OleDb.OleDbConnection = New OleDb.OleDbConnection(con)
    
        Dim _command As OleDb.OleDbCommand = New OleDb.OleDbCommand()
    
        _command.Connection = connection
    
    
    
        _command.CommandText = "SELECT * INTO [MS Access;Database=" &amp; db_path &amp; "].[Sheet1] FROM [Sheet1$]"
    
        connection.Open()
    
        _command.ExecuteNonQuery()
    
        connection.Close()
    

    End Sub

  6. 6

    Type the following to create a new function called "MakeExcelConnection":

    Private Shared Function MakeExcelConnection(ByVal filename As String) As OleDb.OleDbConnection

        Dim con As String
    
        conn = "Provider=Microsoft.ACE.OLEDB.12.0;" &amp; "Data Source=" &amp; filename &amp; ";" &amp; "Extended Properties=Excel 12.0;"
    
        Dim connection As OleDb.OleDbConnection = New OleDb.OleDbConnection(con)
    
        Return connection
    

    End Function

  7. 7

    Run your program and click on "Button1" to import "Sheet1" of your Excel spreadsheet to "Sheet1" table in Access.

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.