Results 1 to 6 of 6
  1. #1
    gbmarlysis is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Dec 2011
    Posts
    48

    Importing alot of Excel Workbooks at once

    Hi,

    I'm a bit of an amateur.

    1. I have a file. Path is C:\Users\gbmarlysis\Documents\Data\Data in Excel Worksheets

    I have 150 excel workbooks in this folder that I would like to import into access.

    Each column is the same in each Excel workbook but the one sheet in each workbook has a different name.

    How do I set up code in Access to run.
    Table name in Access is : Archive records

    I get confused by class module, module, declarations etc. If I could get a basic step by step that would be awesome.

    Kind Regards



    Luke

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    You will have to build code to iterate all of your excel files. The following (untested) code should grab a hardcoded path, open a workbook, and display the worksheet name in a msgbox.

    The following code uses early binding. You will need to reference the Microsoft Excel XX.0 Object Library.

    Code:
    Dim xlApp As New Excel.Application
    Dim myBook As Workbook
    Dim mySheet As Worksheet
     
    Set myBook = xlApp.Workbooks.Open(C:\Test\ExcelFiles\TestFile.xlsx)
    Set mySheet = myBook.Worksheets(1)
    
    Dim strSheetName as String
    strSheetName = mySheet.Name
    msgbox strSheetName 
    
    set mySheet = nothing
    myBook.close
    set myBook = nothing
    set xlApp = nothing

  3. #3
    gbmarlysis is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Dec 2011
    Posts
    48
    Hi, Thanks for the reply.

    I dont understand what any of that means.

    Sorry

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    I was providing an example of how to retrieve the Worksheet name from an existing file on your computer.

    You made several statements that outline some rules. The only question I see is ...
    How do I set up code in Access to run.
    Table name in Access is : Archive records
    You will need to break down your objectives into small tasks. Once you have an abstract idea, test it. Getting the worksheet name seemed important to me.

  5. #5
    gbmarlysis is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Dec 2011
    Posts
    48
    If there is only one sheet in each workbook is there a way that the code doesn't need to stipulate each sheet name.
    Ideally I would love some function code to put in a module that runs a loop and enters all the excel workbooks into access in the one table.

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    I would suggest using the Import Wizard to import one of the Excel files into a new table and then save the steps as an Import Procedure. After the wizard imports you Excel file, the wizard will ask you if you want to save the process. The first time will be to create a new table. The first time say, "no". Then, delete the records from the table and use the wizard to import the same file into the table that was just created by the wizard. This second import procedure should be saved. When you save the process, give it an easy name to remember and do not use special characters or spaces.

    Now, you have a saved import procedure that you can call from VBA.

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

Similar Threads

  1. Replies: 22
    Last Post: 12-29-2015, 10:41 PM
  2. Excel Export per unique field to separate workbooks
    By Fuzz_cozens in forum Programming
    Replies: 13
    Last Post: 07-21-2014, 06:30 PM
  3. Replies: 3
    Last Post: 03-19-2014, 12:26 PM
  4. Replies: 1
    Last Post: 03-11-2014, 09:29 AM
  5. Replies: 3
    Last Post: 03-06-2014, 03:53 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