473,325 Members | 2,792 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

Code to export new data from Excel to Access

13
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!
Jul 21 '10 #1
11 2845
beacon
579 512MB
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.
Jul 21 '10 #2
AnnK
13
@beacon
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!!
Jul 22 '10 #3
NeoPa
32,556 Expert Mod 16PB
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?
Jul 22 '10 #4
AnnK
13
@NeoPa
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 TransferSpreadsheet 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)?
Jul 22 '10 #5
NeoPa
32,556 Expert Mod 16PB
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 TransferSpreadsheet 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.
Jul 22 '10 #6
MikeTheBike
639 Expert 512MB
@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
Jul 23 '10 #7
NeoPa
32,556 Expert Mod 16PB
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.
Jul 23 '10 #8
AnnK
13
@MikeTheBike
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...but 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!!
Jul 26 '10 #9
NeoPa
32,556 Expert Mod 16PB
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.
Jul 26 '10 #10
MikeTheBike
639 Expert 512MB
@NeoPa
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
Jul 27 '10 #11
NeoPa
32,556 Expert Mod 16PB
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.
Jul 27 '10 #12

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Steven Stewart | last post by:
I have a user who has been using Excel for a while to keep statistics and print reports. She finds using it cumbersome because of long formulas and a lot of copying and pasting. I have designed...
6
by: Robin Cushman | last post by:
Hi all, I need some help -- I'm working with an A2K database, using DAO, and am trying to read records into a Crystal Report and then export it to a folder on our network as an Excel...
5
by: Jonny | last post by:
Hello, I have created a button on my form which when pressed does the following : 1) Run a pre-defined Macro, which is picking up a query and running my data to excel. However, I need the...
1
by: Alice | last post by:
Does anybody knows how to do the above mentioned? Please see below for the codes used for VB. I need codes for VB .Net 03 doing the same thing. TIA for any help. strSQL = "SELECT...
4
by: vancehome | last post by:
I have a series of macros running everynight creating tons of excel files (using transfer spreadsheet). I then have another program that zips them up and emails them all over the place. It then...
0
by: skalra3 | last post by:
If I am using "OutputTo" Macro in Access, and I want the output with some specific export secifications, how can I do this?
1
by: mosris | last post by:
hi i need source code export data from acces to text file or csv, and import data from txt file or csv using visual basic thanks
1
by: sisieko | last post by:
I have an access adp (MS Access 2003) connecting to a SQL 200 server. From the adp a user generates reports and using the built-in access export wizard can export said reports (which are either...
1
by: Ela | last post by:
Hallo, I have next problem: When I'm traying export data from table from one column formated like longint (21,111,111.00 ) form access 2003 to the xml, number looks diferent...
3
by: =?Utf-8?B?YzY3NjIyOA==?= | last post by:
Hi all, I have a question for you. I have a .csv file which has many lines of data. Each line has many data fields which are delimited by ",". Now I need to extract part of data from this...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.