Append Query Involving Spreadsheet

Re: Append Query Involving Spreadsheet

Hi

In your place, I would tackle this project from Access, largely because that is where you want the data. Also, if you do it from Excel, you are going to have to insert the VBA into every spreadsheet that you receive (or it has to be in it before you get it).

The appending part is easy - that just involves an append query. Its actually putting this into a useable tool that is going to create the most challenge.

My idea would be to build an Access form which enables you, or any other user, to simply enter a path and file name for the spreadhseet. Once you have it working you can get a little more advanced and add in the ability to browse for a file. But to start with, keep it simple - less to debug. The form would need an OK button and an OnClick procedure behind it to do the work.

The procedure in the OnClick event of the button will then need to establish a data connection to the spreadsheet specified on your form, to query the spreadsheet and to create a recordset from the data. Alternatively, you could save the spreadsheet as a CSV file and read that into a recordset. If you go straight for the spreadsheet, don't forget that a spreadsheet could have multiple worksheets. You could get real fancy and offer the user a choice, but lets just do one to start with - learn to walk before you run and reuse the code that you have already written. I would go with talking to the spreadsheet method and assume that the data will be in the first worksheet (you can get fancier once you have that working).

With the data in a record set it is relatively easy to use that recordset as the source of an append query or to open the table as a recordset and to write the new records into the table.

Does this sound like what you are after? If you don't already have the Access/VBA skills to do this are you interested in learning? There is plenty of information here on the forum to do this if you break it down into little pieces and tackle each part seperately, but I am more than happy to guide you through it. Others may learn from this thread as well.

So if you want build this yourself, rather have someone else do it for you, let me know and also your level of skill.

Looking forward to helping you.

Regards

Rowan

Re: Append Query Involving Spreadsheet

or for a simpler approach each time you receive the spreadsheet save it in a static location and link the table?