Automating an Excel import

[Tony Williams]

Once a day we recive an Excel spreadsheet that contains data for certain (but
not all) the fields in one of the tables of a database. I know that it is
possible to import these using the wizard but I wondered whether there was
any way to automate the process. For example, ideally I would like a command
button that I clicked which opened up the Windows Explorer Browse window,
allowed me to browse for the file and, when found, the data would be
automatically added to the table when, say, the Explorer window is closed.
I'm assuming that I would have to create an append query to add the data
although some (but again not all) of the data would be added to existing
records and some data could be new records. There will be a field in the
Excel data that matches a field in the table. I'm not too hot on VBA code so
if that is the root an ABC description of the steps would be appreciated.
Many thanks
Tony

 

[pietlinden]

Once a day we recive an Excel spreadsheet that contains data for certain (but
not all) the fields in one of the tables of a database. I know that it is
possible to import these using the wizard but I wondered whether there was
any way to automate the process. For example, ideally I would like a command
button that I clicked which opened up the Windows Explorer Browse window,
allowed me to browse for the file and, when found, the data would be
automatically added to the table when, say, the Explorer window is closed..
I'm assuming that I would have to create an append query to add the data
although some (but again not all) of the data would be added to existing
records and some data could be new records. There will be a field in the
Excel data that matches a field in the table. I'm not too hot on VBA codeso
if that is the root an ABC description of the steps would be appreciated.
Many thanks
Tony    

You could use TransferSpreadsheet to import the data and use a saved
import specification. Then you could use the OpenFile API to allow
the user to choose an Excel file to import... See www.mvps.org/access/api
.... and then the first article in the section.

 

[Tony Williams]

Hi thanks for that pointer. However as I mentioned I'm not too hot on VBA and
this looks a little scary but I'll have a go at trying to understand what's
happening in the code. Would you mind helping me along the path though? You
say use the Transferspreadsheet to import the data and then the Openfile API,
but how do I link them together? I've looked at Help on that action and I
assume the data import code would be on the Onclick event of the command
button but I'm struggling with how do I trigger the OpenfileAPI within that
onclick event?
Thanks for this, really appreciate your help.
Have a great New Year!
Tony

 

[Roger Carlson]

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "ImportSpreadsheet.mdb" which illustrates how to do this. It
does a little more than you want to do, but if you look at the code behind
the forms, you can trace what is happening. You can find the sample here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=339

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Tony Williams]

Thanks Roger I'll have a look at your example. From past experience ofyour
solutions I'm sure it'll be just what I want. being a VBA novice though I may
be back for some guidance.
Happy New Year!
Tony