VBA to append MS access table with contents of anothe Excel sheet.

[Richard U]

THE SITUATION:

Each month, we get a new excel spreadsheet that we are manually loading into an access database, and appending it, column for column into an existing table.


I am looking for some VBA code that would allow a user to select an Excel spreadsheet, then append the contents of that spreadsheet to an existing table in MS access.

Is there a way to do this?

 

[ranman256]

you dont need vb.
save the spread sheet to your PC AND to the same generic name: c:\temp\Data2Import.xls

in the Access table, link the xl file as a table.
make an append query to add the data from the xl table to the main table.
put this in a macro.

then the steps are:
1.save new workbook to c:\temp\Data2Import.xls
2. run macro.
done

you could even make an xl macro to do the save temp.

 

[Richard U]

This has to be VBA without any links. This is a spreadsheet coming from an outside source.

We only have 1 Access license, so once this is set up, it needs to be a stand alone from that end. The business reasons are beyond my control, but these are the limitations that have been imposed on me.

1) No links
2) No queries in the access database.

So it has to be VBA