Access query to append many, many spreadsheets

It's not really our job to provide you with complete, ready-to-run code. YOu're expected to build it, and we'll help with it.

Here's how you look in a folder:

Dim sFile as String

sFile = Dir("FUll path to your directory\*.xls")

Do Until Len(sFile) = 0
  DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tbl" & sFile, "FullPath to Your Directory & "\" & sfile, True
  sfile = Dir
Loop

This would import every spreadsheet found in the directory to a new table named after the incoming spreadsheet.

Hmm, I'm no programmer and all of my other questions have always been answered completely. If I have to do this myself, then I don't even know how to insert code into Access. Can you tell me how to do that step first.

Some of the volunteer Experts will do your work for you, but not me. Looks like you've managed to do this with others (like here: https://www.experts-exchange.com/questions/27564789/Query-data-out-of-access.html), but your question here reads less like a request for help and more like a "here's the requirements for my project, do this for me.".

If you'd like you can certainly Delete this question and repost, and perhaps one of the others will just do it for you, or I'll be glad to help you do this yourself. It's always good to know how something works (for when it breaks).

No I would rather learn to do it myself. Here's what I did so far. I created a folder on my desktop and put the excel sheets into it. I created a new, blank, .mdb file and opened a module and inserted your code into it exactly (with my full path to my file directory where you said to). But it didn't work...

Okay - add a Form to your database, and then add a Button to that form (name the button cmImport). While still in Design view, select the button and open the Properties dialog. Select the Events tab, and then select the Click event. Next to that event, select the [Event Procedure] option in the dropdown, and then click the Build button (the button just to the right of that textbox). This will open the VBA Editor, and will create the event "stubs" for you. It should look something like this:

Private Sub cmImport_Click()

End Sub

Your code goes between those two lines, and Access will "fire" any code it finds between those lines each time a user clicks that button.

Add the code from my earlier comment between those lines. It should look something like this:

Private Sub cmImport_Click()
    Dim sFile  As String

    sFile = Dir("H:\Folder\Folder2\Folder3\*.xls")

    Do Until Len(sFile) = 0
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tbl" & sFile, "H:\Folder\Folder2\Folder3\" & sFile, True
        sFile = Dir
    Loop

End Sub

You would obviously need to change the "H:\Folder\Folder2\Folder3\" to point to the directory you wish to search.

I tested this code, and it works on my machine. It will import whatever .xls files it finds in the directory you specify to new tables in your database.

OMG it totally worked, you helped me make a button that makes something happen!! Thank you! That was so much more fun going through the steps myself.

The only problem is that it brought all of the spreadsheets in but it didn't append them to each other, I just wanted one table. Does that make sense? It is simple to modify the code to make that happen?

!!!!

Are all of the spreadsheets in exatly the same format, with exactly the same column names and such? If so, just put them into the same table:

 DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "YourTableName, "H:\Folder\Folder2\Folder3\" & sFile, True

If they are not then this is much, much more difficult. In cases like that, you would move them into the tables as you're doing now, and then you'd have to determine which columns to "merge" and which columns should remain.

Yes they are completely identical inside except the actual spreadsheet is named differently. So I made a table and called it "Ints" and it has all of the column names and everything. It's blank. And it sits in the .mdb file.

My code says    
Dim sFile  As String

    sFile = Dir("C:\Documents and Settings\Desktop\Ints\*.xls")

    Do Until Len(sFile) = 0
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Ints" & sFile, "C:\Documents and Settings\Desktop\Ints\" & sFile, True
        sFile = Dir
    Loop

But when I run it the spreadsheets don't append to the "Ints" table, they just upload separately...

It's just putting the name "Ints" in front of the file names as they are uploaded....

It worked, thanks so much for being so patient and being such a good teacher!!!