Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Wikltshire UK
    Posts
    152
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Multiple Excel Files to Access Table (2000/2003)

    Hi,

    Just wondering if anyone's got any Clues:

    I've got about 600+ Excel Files Containing Structured Data, that I want to Concatonate to an Access Table for later cross-reference and analysis.

    It will take me ages to do it by hand and the structure should be the same in all Cases.

    I'll Break down the problem as I see It:

    1) Reference Excel spreadsheet in Specified Folder in Windows
    2) Import Data into Individual Access Table (using an Import Specification)
    3) Append Data to Main Table
    4) Go to Next Spreadsheet and Repeat.

    If theres Some coding Needed (I'll adjust for the actual fields) use below:

    The Name of all the Excel Files will be unique.
    The Spread Sheet Consists of Four Columns: Name[text], Date[Date], Email[Text] and Amount[number].

    In Gratitude as Always.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Multiple Excel Files to Access Table (2000/2003)

    You can import to an existing table. If you've placed all Excel workbooks in a single folder that doesn't contain other Excel workbooks than those you want to import, you can use code like this:

    Sub ImportXL()
    ' Modify as needed, but keep trailing backslash
    Const strPath = "C:Excel"
    Dim strFile As String

    On Error GoTo ErrHandler

    strFile = Dir(strPath & "*.xls")
    Do While Not strFile = ""
    DoCmd.TransferSpreadsheet _
    TransferType:=acImport, _
    SpreadsheetType:=acSpreadsheetTypeExcel9, _
    TableName:="tblImport", _
    FileName:=strPath & strFile, _
    HasFieldNames:=True
    strFile = Dir
    Loop
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    End Sub

    Substitute the correct path of the folder.
    Substitute the correct name of the table you want to import to.
    Change HasFieldNames:=True to HasFieldNames:=False if necessary.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Wikltshire UK
    Posts
    152
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Multiple Excel Files to Access Table (2000/2003)

    Thanks HansV

    I'll give that a go and see if I can get it working.

    Graliv1

Posting Permissions

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