Automated Export Excel 2010 worksheet to Access 2010 Table (1 Viewer)

I can import the excel spreadsheet into the Access table using the External Data tab in Access. I'm trying to find a way to automate the process for my client. In other words, when I use the External Data import, the user still has to go through browsing to the correct Excel workbook, choosing the worksheet, then choosing the table to append to.

What I would like to do is write some kind of macro either on the Excel end or the Access end, doesn't matter, whichever works, that will automate the import into Access without the user having to go through all the steps. I'm basically trying to make this a "push-button" function for the user.

From what I've read this may be possible with VB or MySQL, but I am not familiar with either. If there are steps to do this and you can tell me EXACTLY how to do it, I'm game for learning something new.

In any case, any help will be greatly appreciated.

If it's the same excel file each time, create a "Named range" in Excel and then link to that named range in access, then just use a query to pull that info?

Would work but this particular spreadsheet is downloaded on a daily basis with new and previous info and could be 12 rows or 500 rows so a named range wouldn't really work. I'm going to try to learn VB and see if there's anything I can do there. I don't know any VB but if I'm going to continue in this field I'm going to have to learn it. Thanks for the response!

Did you ever get this to work? I'm trying the same thing and using the following code (called by pressing a command button) and can't get it to work to save my life! No compile errors but when I hit hte button, it opens the vb code window.

Public Sub UseTransferSpreadsheet(strTableName As String, strFilePath)
DoCmd.TransferSpreadsheet acImport, , "Denials extract", "H:\Patient Financial Services\Denials extract.xls", True

End Sub

Mooredk, that was a long time back so I'm trying to remember what I did. I did it within Access because I don't know VB. This may be a long answer because there was some extensive programming involved on the Excel end.

Maybe if I explain my spreadsheet, etc, it will help to then understand my steps.

This database was created for a real estate client, so in the database itself the main identifier for everything is the Street Address and City (both have to match).

Every day the client downloads new data from a website and we leave that as it is.

I created a NEW spreadsheet called Properties. Then I created macros to do the following:

1. CLEAR OLD DATA -- this macro clears the data in the main worksheet (also called Properties) and deletes another worksheet called Converted. That's for two reasons --
a. Obviously we want to get rid of the old data to prepare for the new.
b. Once data is pulled from the downloaded spreadsheet, I have to "clean" it, meaning it downloads with addresses abbreviated and in order to avoid duplicates I have to do a conversion to make it spell out all the addresses (EX: 123 Main St becomes 123 Main Street). Hence the worksheet called Converted. The Converted spreadsheet is where you get the correct fields and spelled out addresses for the export, but the Converted worksheet comes later in the process, so for now, it just needs to be removed, hence the Delete Converted worksheet included in the Clear Old Data macro.

2. GET NEW DATA -- this macro goes out and gets the new data from the downloaded spreadsheet and posts it into the Properties spreadsheet in the Properties worksheet. I have another worksheet that permanently resides in the Properties spreadsheet called Clean Addresses. This is where I put the formulas to change the addresses from abbreviated to spelled out, so Clean Addresses is directly linked to Properties, but only via the Street Address and City fields. The other fields in this worksheet are formulated to find the abbreviations and correct them.

But...since the new data could be 25 records or 500 records, I have to allow for up to 500 records, so my formulas always begin with -- IF(Properties!StreetAddr=" ", " ", IF(Propreties!StreetAddr<>" ", Properties!StreetAddr)).

This is so that it will "hide" those nasty #VALUE# posts in the cells where there is no data to be linked from the main worksheet Properties. This will become a necessity when exporting later.

3. CONVERT -- okay, so this macro creates a worksheet called Converted, changes the field names and data types to match those we will need for the export into Access, AND brings in the address and city correctly from the previous Clean Addresses worksheet. The rest of the data is linked to the Properties worksheet except those two fields which are linked to the Clean Addresses worksheet.

(I hope this is not TMI and that I have not confused you to this point) Please do write me back if you need help understanding or if I've overdone the explanation, whatever.

The Converted macro also creates a Named Range that will be the range that is exported into Access.

These formulas, too, all begin with -- If(Properties!Field1=" ", " ", If(Properties!Field1<> " ", Properties!Field1 --- or two or three, whatever, except you need to use the cell reference...Field1 here is just for example.

Again, that is so that when you look at the spreadsheet Converted, any data that does not exist in the main worksheet Properties won't give you those "=#Value# results. And again, this is very import for the export!

Okay, so at this point the macros have all been run, the range with corrections selected, and the spreadsheet close. Now you move on to the Access side or this monster.

First, I created a table called Properties in the database with field names and data types needed by the client.

Then I created an Access macro that includes the following:

1. Delete query to delete any current records in the Properties table.
2. Import/Export spreadsheet (created via a Saved Import)
3. Delete Null Records Query (this is where all those formulas to leave the cells blank in Excel if there was no matching data in the main worksheet comes into play. If those cells in Excel showed #Value# or anything else, they would be considered data and would not be deleted here.
4. Append query to append all the records just posted in the Properties table to the Main table.
5. Find Duplicates Query to find duplicates based on Street Address AND City.

That one macro was written as the event procedure for a button I put on the menu for the client that is called Import Property Data.

So, the client DOES have to open his spreadsheet and quickly run the three macros, which only takes seconds, then run this macro in Access and voila! all is done for him.

After writing this novel, I hope I answered your question. A lot of information, I know, but only way I knew how to explain what I did. If I can help further, let me know. Thanks! Sherry aka Hey Lucy!

Hey Lucy..I thank you for getting back to me!! And I'm a very detailed person so it didn't bother me however, I didn't need the excel explanation. Basically I want an automated way to import a daily excel file into Access w/ a command button. In previous versions of Access (pre-2010) I could set up a macro to reference the file and import specs. Well that is no longer available in 2010. Or I cannot find it anywhere and all of the online help/forums I have looked at can't help either. I found code (referenced in my previous post) that I thought would work. I wrote the code and then referenced the vba code in a macro and then another macro ran that macro. I then accessed the macro through a command button but it isn't working. It doesn't tell me why. It actually just takes me to the vba code. Thanks for trying. Were you using Access 2010 or previous version?

Thanks so much and I love detailed people!

Hi Mooredk,

Thanks for your remark about detailed people! LOL ....guess that I am...maybe a little OCD carrying over? Oh, and yes, I am using Access 2010. They made a lot of changes to it and I'm not sure I like all of them!!

I think I have the answer as to how to create a macro and then a subsequent button to run the macro that will do what you want, but I do have a couple of questions.

Will your Excel file contain the exact same number of records each day?
Will you need to retain each day's import in the database or overwrite the previous day's records?

Write me back and let me know so I can analyze further and try to help you out. Sorry I don't know any VB. I've been limping along within Access all these years and have not had the time to learn it.

Hey Lucy

Believe me..I've been limping too. I know a little vb but probably enough to hurt myself. The file will be overwritten each day. The number of records will vary from day to day.

I have tried everything and so frustrated b/c this was a VERY simple task before. And I AM NOT A FAN OF 2010. I have been working on this database off and on for months and have people waiting on me. This isn't my only issue but would feel better to get it resolved! I have to also create a switchboard and security. I'm also working on 2 combo boxes that when a value is selected in one it will only allow the values in the 2nd to show that are associated with the selection in the 1st combo box..clear as mud? I do have the author of one of the books I bought trying to help with that. I think I just make things more complicated.

Thanks again! And my name is Debbie!

LOL Debbie...I KNOW I complicate things then kick myself in the rear when I realize how simply I could have done something.

I'm Sherry, but all my friends call me Lucy, because of my red hair and the funny things that happen to me...I've ALWAYS got some 'splaining to do, but I will answer to either name.

Now, I have the macro steps to import your spreadsheet into Access and that should work fine if you've overwriting. However, to use this macro function you have to import a Named Range, and that might be where the problem lies. Here are the Macro steps: (Make sure when you create the macro that "Show All Actions" is active, or the action to do this won't be in the list.

1. Select Import/Export Spreadsheet in the list.
2. Enter the name of the table you wish to import the data into.
3. In the File Name field you will actually have to enter the path to the Excel file, so you will have to look at the properties of the Excel file to get that. (It seems to me that they should have written a "Browse" feature into that to make it easier, but noooooo). Be sure to include the file extension in the file name.
4. Select Yes or No depending on whether your spreadsheet has field names.
5. Enter the Named Range to import.

As in my case, where my Named Range in Excel had to allow for a certain number of records, yours will too, since you do not have a definite number each day. So, let's say you have allowed for 50 records in your Named Range, but today's records are only 25. It is going to try to import all 50 rows from Excel, but since the last 25 have no data, you may get an Import/Export error in Access.

Therefore, you would end up having 25 null records in your table. You can manually delete them, but then that would not be part of the automated process.

So...I added another step to my macro to run a delete query to delete null records. In my case, since my records are focused on the Address field, I simply added a " " criteria to the address field in the query, and it deletes all records that contain a null address.

Try these steps and see if it works and let me know.

I know how frustrating it is to hit a brick wall when programming a database. I recently spent four months developing a very complex one and I hit so many walls I have dents in my head. Happy to help in any way I can!

Love, Lucy.
(Desi did! haa)

Ha ha ha..you make me laugh!!

I will defnitely try this. Unfortunately my other job duties are taking priority at the moment so it may not happen until Monday. I will definitely try though and let you know. THE delete query is a good idea! Thanks again!!!

Mooredk, I did see one possible issue in your code here:


You left out the second argument. I know it is supposed to be optional, but I've never gotten it to work with Excel 2007 or later without that argument.

Have you tried using whichever of these applies?

Excel 2007

Excel 2010

(I don't know the constant for Excel 2013, but the numeric value is most likely 11.)

I know it's code rather than macros, but the macro would be much the same.

Thanks for the advice. I may try this. I didn't really think it mattered b/c it said optional.

I will get back to you if it works.

Thanks,

Debbie

Hey Lucy,

I did the show actions and see the import/export action. I thought i had done that already. I knew it still had to be there! I ran it and it worked but I iwll have to create a delete query for that table b/c it is appending and not overwriting. I"m not sure which is better code or this macro. But macro seems to be easier. I may try Frothingslosh's advice on the vb code too. It is more to see if I can get it to work. I know I'm smarter than this..

Thanks to both of you again!!!!

Hey Debbie! Glad you got that part to work. I did leave out the part about including the delete query...sorry...when I went back and looked at my macro that was my first step. Hope you get it going the way you need it!!

Lucy

Hey Lucy! I think I did. I'm annoyed at myself for this taking so much of my time. Seriously how did I miss it. Oh well it is done now. Now on to cascading combo boxes

Debbie

Oh boy!!! lol