Results 1 to 10 of 10
  1. #1
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    682

    How to Import multiple excel files (or worksheets) automatically

    Here's an import routine I designed to import multiple excel files in a specified folder (it has a Browse button on the form to easily select the folder).

    It's all automated via a popup import form.

    It will cycle through the folder and import any and all excel files it finds in that folder automatically to tables. It can also easily be configured to import different worksheets in an excel file as well as xlsx files (it currently imports xls files).



    It will create a 'log' type table showing the names of each excel file it imported along with the date/time.

    I hope someone finds this useful.

    *** Make sure to add this website to your intranet security in IE to be able to open any MSAccess files within a zip download.
    .
    Last edited by pkstormy; 08-27-2010 at 06:51 PM.

  2. #2
    na persze is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    1

    Run time error 2455

    Hi PK,

    I tried to use your program but got a run time error 2455 saying you entered an expression that has an invalid reference to the property FileSearch. What am I doing wrong?

    Thx!

  3. #3
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    682
    Not sure. You may want to try running the docmd.transfer... code by itself until you get it to work on a single file and then put it back into the routine. Usually these types of errors are related to spaces or odd characters in the folder/filename.

  4. #4
    Nazier is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2012
    Posts
    1
    I get a compile error telling me that the code in this project should be updated for 64-bit systems

  5. #5
    dhdoom is offline Novice
    Windows XP Access 2003
    Join Date
    Dec 2012
    Posts
    1
    It is working for me, and saving me considerable time. I am importing from files with multiple worksheets so unfortunately (because of my ignorance of how to alter the code) I am copying out the other sheets to their own workbooks.

    many thanks

  6. #6
    florencia is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    1
    I'm having trouble openning the file... Actually I'm working on a DoCmd.TransferSpreadsheet and wanted to see how you'd done that formula. Is there another way for you to share the file?? Thanks in advance!

  7. #7
    burrina's Avatar
    burrina is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2012
    Location
    Freeport,Texas
    Posts
    1,383
    You need to add PtrSafe to your modules Example: Private Declare PtrSafe Function apiGetUserName Lib "advapi32.dll" Alias _

  8. #8
    help1985 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Oct 2013
    Posts
    2
    Hi,

    Hi All,

    I'm new to this forum - though I have been following it for some time now and find it very useful.

    I'm trying to use the above database, but I also get the 2455 error, the following bit is highlighted..

    With Application.FileSearch

    Any ideas? Desperately need to import a number of excel worksheets all in the same folder

    Thanks,
    James

  9. #9
    help1985 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Oct 2013
    Posts
    2
    I think I know what the problem is but don't know how to fix it. Apparantly the line in red no longer exists - what can i replace it with? Sorry! I'm not a programmer, so this is all learning for me:

    Function FindAndImportFiles(vDirectory As Variant, vFileSpec As Variant)
    'PK - 11/10/09
    '//////////////////
    'Note: Cycles through files in a given folder/directory and filespec.
    'Imports all files (see ImportTheFile).
    '//////////////////
    'Note: directory and filespec passed from ImportForm
    'Directory = "F:\Data\Databases\Heather\ImportFiles"
    'FileSpec = "xls"
    Dim QI As Integer
    QI = MsgBox("Confirm importing all xls files from directory: " & vDirectory, vbYesNo)
    If QI = vbNo Then Exit Function
    Directory = vDirectory
    FileSpec = vFileSpec
    Dim NumFound As Long
    With Application.FileSearch
    .NewSearch
    .LookIn = Directory
    .FileName = FileSpec
    If .Execute() > 0 Then
    For i = 1 To .FoundFiles.Count
    Call ImportTheFile(.FoundFiles(i))
    Next i
    End If

    End With
    'Now append all the newly imported file names to a table.
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "ImportedFileListingMake"
    DoCmd.SetWarnings True
    MsgBox "Import Routine complete! All tables in the designated folder have been imported!"
    End Function

  10. #10
    Aloupha is offline Novice
    Windows XP Access 2003
    Join Date
    Nov 2009
    Posts
    26
    Awesome tool! Thank you. I was just having issues using Access's TransferText. I was using it to import csv files, but it was giving me problems by dropping leading zeros of SSN's even though. I was looking for a way to import .xls files, and this tool will work flawlessly. I'll have to modify it to my needs (as soon as I figure out how). Thank you!

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

Similar Threads

  1. Import multiple text files automatically
    By instructorTek in forum Import/Export Data
    Replies: 30
    Last Post: 10-20-2012, 04:50 PM
  2. Cannot import excel or text files
    By donald_s in forum Access
    Replies: 2
    Last Post: 04-13-2010, 11:48 PM
  3. Replies: 3
    Last Post: 11-02-2009, 04:33 AM
  4. Import Excel Worksheets into Access 2003
    By KramerJ in forum Programming
    Replies: 0
    Last Post: 03-18-2009, 04:11 PM
  5. Import multiple dbf files into existing table?
    By sbg2 in forum Import/Export Data
    Replies: 0
    Last Post: 06-12-2006, 02:06 PM

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