Code to export new data from Excel to Access

[AnnK]

I am trying to figure out a way to export any new data entered into an Excel 2003 spreadsheet into an Access 2003 database from the Excel application. I've seen lots of code originating in the Access application, but I want to avoid having to go into Access to do it. The users will be entering data into Excel daily, but no one will be in Access daily- and the info needs to be up to date because it is a source for another application(an Infopath 2003 form)which will also be used daily.

Only the newly entered data would need to be copied, and the primary key would not be in the Excel sheet.

I'm a newbie to VBA, so any help is really appreciated. If I don't figure this out- it's going to end up with me having to MANUALLY transfer the data everyday, which just seems wrong!

[beacon]

Hi Anna, welcome to Bytes!

I suggest just creating a link to the Excel table from within Access...that way you don't have to worry about any messy code.

[AnnK]

Hi Anna, welcome to Bytes!

I suggest just creating a link to the Excel table from within Access...that way you don't have to worry about any messy code.

Thanks for responding Beacon!
Unfortunately, linking won't work because doing so doesn't actually transfer the data. In order for InfoPath to pull it's data from the Access table, the data has to be resident in Access- it won't pull it from Excel via the link. I've been astonished at how hard it is turning out to be to make these 3 MS programs to work together!!

[NeoPa]

This wouldn't be straightforward Ann. It involves various issues that need to be handled correctly. Among wihich are :

1. Application Automation.
2. Managing which cells (presumably rows) are new and which already exported.
3. Exporting the data from Excel in the first place (There is no option to export directly into Access) or somehow ensuring Access imports only the new data if it imports from Excel.

Is this something you're up for?

[AnnK]

This wouldn't be straightforward Ann. It involves various issues that need to be handled correctly. Among wihich are :

1. Application Automation.
2. Managing which cells (presumably rows) are new and which already exported.
3. Exporting the data from Excel in the first place (There is no option to export directly into Access) or somehow ensuring Access imports only the new data if it imports from Excel.

Is this something you're up for?

Yeah- I get that it's going to be much more complex than I expected when I took on this task. I've gotta do what I've gotta do... The only limiting factor is that the end user has to have a simple way to trigger the actions.

I didn't think of automation! Do you think it is feasable to create a command button (on the Excel sheet) with a macro to open Access and run code from that program? Access has the TransferSpreads heet action (which Excel- the spreadsheet- seems to lack, go figure). I've seen other posts recommend the use of a 'date of update' field to filter out old data, which sounds like it would work.

Do you think it will cause problems if the spreadsheet and database are not in the same folder on the network (the permissions should be identical)?

[NeoPa]

AnnK: Yeah- I get that it's going to be much more complex than I expected when I took on this task. I've gotta do what I've gotta do... The only limiting factor is that the end user has to have a simple way to trigger the actions.

Well. There is also the setting up of the system in the first place. If you're confident you can do whatever is required though, that's got to be a good starting attitude.

AnnK: I didn't think of automation! Do you think it is feasible to create a command button (on the Excel sheet) with a macro to open Access and run code from that program? Access has the TransferSpreads heet action (which Excel- the spreadsheet- seems to lack, go figure). I've seen other posts recommend the use of a 'date of update' field to filter out old data, which sounds like it would work.

Yes. Feasible, but possibly not too simple. Remember, there is a limit to how much I can help from across a forum page. I'll help where I can of course.

AnnK: Do you think it will cause problems if the spreadsheet and database are not in the same folder on the network (the permissions should be identical)?

No. As long as you have a way to determine where the database is then you can find where your spreadsheet is held from within code.

[MikeTheBike]

I am trying to figure out a way to export any new data entered into an Excel 2003 spreadsheet into an Access 2003 database from the Excel application. I've seen lots of code originating in the Access application, but I want to avoid having to go into Access to do it. The users will be entering data into Excel daily, but no one will be in Access daily- and the info needs to be up to date because it is a source for another application(an Infopath 2003 form)which will also be used daily.

Only the newly entered data would need to be copied, and the primary key would not be in the Excel sheet.

I'm a newbie to VBA, so any help is really appreciated. If I don't figure this out- it's going to end up with me having to MANUALLY transfer the data everyday, which just seems wrong!

Hi

As NeoPa has indicated all this can be achieved by automation.

However, I always write code in Excel using ADO connection/recordsets for doing what you describe, but I suspect this would be a very steep learning curve for you if you are not familiar with the ADO object connection/recordset etc. or writing sql queries in code(?). It also seems to run faster in Execel that running similar code using automation in Access (but not proven).


The one big concern for me is that you have indicated the primary key is not available in in the Excel data. If that really is the case, then it would not be possible to determine what data exists and what is new!!

Please note, the primary key is not necessarily an ID field (autonumber or otherwise). It should be the field, or combination of fields, that uniquely identify the record, which I hope applies in your case.

Just my two penny worth.

MTB

[NeoPa]

In Excel you may have Row numbers to indicate which items are new. If a single cell held the value of the last row ex/imported then a PK would not be mandatory. Data is very rarely better maintained without a PK, but without knowing your data I hesitate to state anything categorically.

[AnnK]

Hi

As NeoPa has indicated all this can be achieved by automation.

However, I always write code in Excel using ADO connection/recordsets for doing what you describe, but I suspect this would be a very steep learning curve for you if you are not familiar with the ADO object connection/recordset etc. or writing sql queries in code(?). It also seems to run faster in Execel that running similar code using automation in Access (but not proven).


The one big concern for me is that you have indicated the primary key is not available in in the Excel data. If that really is the case, then it would not be possible to determine what data exists and what is new!!

Please note, the primary key is not necessarily an ID field (autonumber or otherwise). It should be the field, or combination of fields, that uniquely identify the record, which I hope applies in your case.

Just my two penny worth.

MTB

Actually, that is the route I was leaning towards. I found some code on another site to use as a starting point. It looks like it will work for the transfer. I found code for both an ADO and a DAO transfer. The DAO looks like it will do it directly to my existing database, while the ADO looks like it requires more steps between the two with the whole Microsoft.Jet portion.

The DAO seems more straitforward, and I have reference libraries for both in the VBA portion of Excel. Is it worth the extra effort to go ADO?

I'm also trying to add a second sub that would run sequentially after the export (so the end user could hit one command button that would carry out both actions) to do something like add an "E" to a previously blank column after the first sub runs. Then I could add a filter to the export code that would only export rows without the "E" (and then they would have the "E" added and in so doing, not be exported the next time the user hits the button). It seems like it would eliminate duplicate data exportation...b ut I haven't gotten it to a point where I can test it yet, so I'm not sure.

Finding basic VBA (no pun intended) instructions seems to be far more cumbersome than any other program I've used. But I'm plugging away at it!!

[NeoPa]

Good for you Ann. That all sounds very sensible.

I normally work to the rule that anything Access specific is better done using DAO, and anything less specifically Access is better done with ADODB. I'm not sure where Excel fits in to this though. I expect Jet (and therefore DAO) would be more appropriate, but not positive.

[MikeTheBike]

Good for you Ann. That all sounds very sensible.

I normally work to the rule that anything Access specific is better done using DAO, and anything less specifically Access is better done with ADODB. I'm not sure where Excel fits in to this though. I expect Jet (and therefore DAO) would be more appropriate, but not positive.

Hi Neopa

I must admit I have not used DAO recordset for 10 year in Acces or Excel, not since Microsoft recommended using ADO instead of DAO in future projects!!

I believe in Access that

CurrentProject. Connection

is an ADO connection object?


MTB

[NeoPa]

MS certainly did attempt to steer things that way some while back. Unfortunately for them, they were quite out of touch with the situation and the reaction in the developers community seems to have forced them to step away from that course.

There are certain Access specific things that ADODB simply doesn't handle.