Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2015

    Unanswered: Batch import Excel files to Access

    I have a folder with 2000+ excel files which I need to import into a single Access table. This would be a recurring activity (monthly) so I am looking for help to automate this task. Assuming the best way to do this is with VB but unfortunately I have no experience in this area.

    Can anyone help with code that would accomplish this task? Any explanation / references would be greatly appreciated since I have very little knowledge of VB.

    I am using Acess 2010

    Thanks in advance!

  2. #2
    Join Date
    Feb 2004
    New Zealand
    Provided Answers: 5
    Here some code that should point down the right track

    create the link first

    Function ReLink_Execl_File(ThisFile) As Boolean
        ' this just relink the the Execl file
        ' so the MYDocs folder is on a differance each User.
        Dim FilePathName As String
        FilePathName = Get_MyDocs & "\" & ThisFile 
        If Not fileExists(FilePathName) Then
        MsgBox ("C A N ' T   F I N D  " & vbNewLine & vbNewLine & ThisFile & vbNewLine & vbNewLine & "You Need to put file into Your 'My Documents' Folder")
        ReLink_Execl_File = False
        Exit Function
        End If
        If fileExists(FilePathName) Then
        DoCmd.DeleteObject acTable, "In Process"
        DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel12, "In Process", FilePathName, True
        ReLink_Execl_File = True
        End If
    End Function
    Function fileExists(s_fileName As String) As Boolean
        Dim obj_fso As Object
        Set obj_fso = CreateObject("Scripting.FileSystemObject")
        fileExists = obj_fso.fileExists(s_fileName)
    End Function
    Function Get_MyDocs() As String
        Set WshShell = CreateObject("WScript.Shell")
        Get_MyDocs = WshShell.SpecialFolders("MyDocuments")
    End Function
    then just do a update query form the execl link table to main table
    hope this help

    See clear as mud

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008

  3. #3
    Join Date
    Feb 2015

    Batch import Excel files to Access


    Thanks for the response. About 5 minutes after my original post I was able to find a piece of code which solved my problem. Unfortunately I didnt even get a chance to look at the code you provided but I will definitely save it for future use.

    Thanks again

  4. #4
    Join Date
    Mar 2015


    Can we benefit also from the Code you got as a solution to you?
    By pasting the codes here or reference us by a link?


  5. #5
    Join Date
    Feb 2015

    Excel batch import to access


    Here is the link to the code I used for the batch import (code pasted below for convenience).

    Hope this helps?

    Dim strPathFile As String, strFile As String, strPath As String
    Dim strTable As String
    Dim blnHasFieldNames As Boolean

    ' Change this next line to True if the first row in EXCEL worksheet
    ' has field names
    blnHasFieldNames = False

    ' Replace C:\Documents\ with the real path to the folder that
    ' contains the EXCEL files
    strPath = "C:\Documents\"

    ' Replace tablename with the real name of the table into which
    ' the data are to be imported
    strTable = "tablename"

    strFile = Dir(strPath & "*.xls")
    Do While Len(strFile) > 0
    strPathFile = strPath & strFile
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
    strTable, strPathFile, blnHasFieldNames

    ' Uncomment out the next code step if you want to delete the
    ' EXCEL file after it's been imported
    ' Kill strPathFile

    strFile = Dir()

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts