Ok, I'm fairly comfortable using VBA with Excel but now I am starting to use Access more. That said, I have a learning curve there.

Here's what I am trying to do. I use a software system that allows me to export data to Excel. It does have a built-in function to export to Access but it doesn't work correctly so I am stuck using Excel. At any rate, I run a daily report that I would like to append each day from Excel to an existing table in my database.

The database pathname is: P:\Phoenix\Workforce Mgt\Workforce Mgt Database.mdb and the table that I want to append data to is: Spec Ref Adherence Data.

The table has six fields:

1) ID (which is just an autonumber field)
2) Agent Name
3) Phone Hrs
4) Missed Hrs
5) Adherence
6) Work Date

The pathname that I store the Excel file in is located:

H:\Adherence Data for Spec Ref - data dump.xls

Basically, the field names in the Table match the field names in the Excel spreadsheet, minus the ID field.

Currently I am using Microsoft Access 2007.

Thanks for your help.

Jake

Start with a TransferSpreadsheet method to LINK to the Excel sheet. Then run an APPEND query to add the records to your Access table.

We are talking about TWO lines of code. Plus you create the Append query.

Quote:

---

Originally Posted by ScottGem

Start off with a TransferSpreadsheet method to LINK to the Excel sheet. Then run an APPEND query to add the records to your Access table.

We are talking about TWO lines of code. Plus you create the Append query.

---

Scott - I'd like to do this in Excel if possible. Once I download the data to Excel from my system, I have a macro that applies some formatting and removes columns that I don't need. The last line of that macro I'd like to be the part which tells Access to run the Append query without me manually having to open Access and then run the TransferSpreadsheet method.

I work from the Access side, Access VBA is much easier to work with and, more important, much easier to debug. So I'm not sure how or if you can do this from Excel.

One thing you can do, is create a front end specifically to run the TransferSpreadsheet. Run it from an autoexec macro. Then run that front end using the Shell command.