create a new table from Excel

[danisroyi]

HI,

I have a running application which I created in excel.
Each time a new copy of the excel sheet is worked with, I need to create a new table in an access database, that holds part of the data coming from excel.

Can you explain, or can you point me to a manual that I can read that explains how to do this ?

 

[danisroyi]

Thanks for the reply, but....

I forgot to tell that I need to the table creation in run time, it cannot be a manual process.

Is there any way to do this without involving VBA, or do I have no choice ?
If must have VBA, can I get some details how to do this ?

 

[Junkee Brewster]

You can set up a function using VB and various queries to import Excel/CSV files into existing or new Access tables. However, it isn't a fully automated process. Maybe some VB wizz could code it to execute at runtime, but I don't know how (i've never tried, maybe code for on Load or On Open.. ). What I've used in the past: an "Import" button on Access that will automatically select a file (from a location you've coded) and import it how you want it. So, SOME user input is still required - just pressing the one "import" button.

However, This is a mess around - a LOT of code and work, depending on what you want it for. If it's for small amounts of data/infrequent import, is it worth the effort?

Pat is right - what are you planning to do with the data? There might be more viable suggestions. If you're not relating the Excel data to other records in your database (i.e. A "Stand Alone" table) have you thought about inserting your Excel file as an object into you Access database? (Insert>Object>Create From File>Browse>(Select your file)>Check "Link" box).