We help IT Professionals succeed at work.

Access query to append many, many spreadsheets

Coaster_brook_trout
Coaster_brook_trout used Ask the Experts™
on
Hi I have two standardized data collection spreadsheets (attached, not named in any standardized way), that come in every month from 50 different people.

So I am constantly having to take data out of new spreadsheets and append them to a matching master table in MS Access (2003).

Can someone create a button in Access that looks in the folders (one for each spreadsheet type) where I am keeping the spreadsheets and appends all of them at once? Bonus points for the query being smart enough not to append duplicate data.

Thanks!
Spreadsheet-1.xls
Spreadsheet-2.xls
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
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.

Author

Commented:
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.
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
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: http://www.experts-exchange.com/Microsoft/Applications/Q_27564789.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).

Author

Commented:
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...
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
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.

Author

Commented:
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?

!!!!
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
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.

Author

Commented:
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...

Author

Commented:
It's just putting the name "Ints" in front of the file names as they are uploaded....
Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014
Commented:
Please note my syntax earlier. You have this:

"Ints" & sFile

which would add the sFile value for each sheet, and therefore create a new table each time. It should be this:

"Ints"

Author

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