Append excel data with command button

[evanhughes]

Hi All

I have searched the forums and can not find anything that suits my needs.

What these are as follows.
I need a command button on a form that will allow me to chose an .xls from a directory and add to the existing data in an access .mdb table to an access mdb file.

I am using access 2007 but db is saved in 2003 mdb format and using excel 2007 but import is from 2003 format.

Database name = rental.mdb
Database table name = bookings
There are approx 25 fields

The field names in the excel worksheet are the same as the field names in the mdb table although some fields will be blank.

I have created the form command button (cmdbutton1) but can't get any code to work.

Any help would be appreciated.

Evan

 

[John Big Booty]

Perhaps the DB posted in this thread will give you some pointers.

 

[evanhughes]

Thanks John for the quick reply.

This looks OK but is a bit complicated for me also I need to be able to select the xls to append to table.

Can't this be done with some import vba code and just add records to table.

Evan

 

[evanhughes]

Thanks John for the quick reply.

This looks OK but is a bit complicated for me also I need to be able to select the xls to append to table.

Can't this be done with some import vba code and just add records to table.

Evan

 

[evanhughes]

Thanks to all for your help.
I found a bit of code that works on the links on this page.
I might need some help later but it seems to work fine at present.

Evan

 

[John Big Booty]

Glad you found a solution

Whether or not the DoCmd.TransferSpreadsheet ask for the location of the file is dependent on amount of information you give the command;

DoCmd.TransferSpreadsheet(TransferType, SpreadsheetType, TableName, [B][COLOR="Red"]FileName[/COLOR][/B], HasFieldNames, Range, UseOA)

If you leave out the FileName argument from the above code then you will get the dialogue box asking for the file name and it's location that you want to import.