Results 1 to 2 of 2
  1. #1
    vibinmvenugopal is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2014
    Posts
    4

    To import Excel data to a table in access through form.

    Hi All,

    I wanted to import data in a access table using a form in access. The form should contain a browse button to browse the file and then a command button to start importing.

    Please help!.

    Thank you,


    Vibin.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Remember to add via VBE menu: tools, references : Microsoft Office 11.0 Object Library


    Code:
    Public Sub ImportXLfile()
    Dim vFil, vTargT
    Dim i As Integer
    Dim sSql As String
    Dim fso
    Dim oFolder, oFile
    On Error GoTo errImp
    vFil = UserPick1File(".xls")
    If vFil <> "" Then
           'OR import the file TO TARGET TABLE
        docmd.TransferSpreadsheet acimport, "tTABLE", vFil, True
           
             'OR link the file for import
        'docmd.TransferSpreadsheet acLink, "xlFile", vFil, True
        'docmd.openquery "qaImportData"
    End If
    endit:
    Exit Sub
    errImp:
    MsgBox Err.Description, vbCritical, "ImportAllFilesInDir():" & Err
    Resume endit
    Resume
    End Sub
    Public Function UserPick1File(ByVal psFilter As String, Optional pvPath)
    Dim strTable As String
    Dim strFilePath As String
    Dim sDialog As String, sDecr  As String, sExt As String
    With Application.FileDialog(msoFileDialogFilePicker)   '<----!!  MUST ADD REFERENCE : Microsoft Office 11.0 Object Library
        .AllowMultiSelect = False
        .Title = "Locate a file to Import"
        .ButtonName = "Import"
        .Filters.Clear
        .Filters.Add "All Files", "*.*"
        .InitialFileName = "c:\"
        .InitialView = msoFileDialogViewList    'msoFileDialogViewThumbnail
        
            If .Show = 0 Then
               'There is a problem
               Exit Function
            End If
        
        'Save the first file selected
        UserPick1File = Trim(.SelectedItems(1))
    End With
    End Function

Please reply to this thread with any new information or opinions.

Similar Threads

  1. VBA - Import Excel Data into Access Table
    By sikhinvestor in forum Import/Export Data
    Replies: 2
    Last Post: 04-28-2014, 01:43 PM
  2. Import access query data into excel file, using excel vba
    By exceljockey in forum Import/Export Data
    Replies: 3
    Last Post: 11-15-2013, 02:36 PM
  3. Import Excel data into existing Access Table
    By octsim in forum Import/Export Data
    Replies: 4
    Last Post: 10-24-2013, 07:21 AM
  4. Replies: 5
    Last Post: 05-09-2013, 06:47 PM
  5. Replies: 3
    Last Post: 12-14-2012, 11:26 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums