create a new table from Excel (1 Viewer)

[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 ?

 

[Pat Hartman]

Open Access and import the spreadsheet. File/Get External Data/Import

I should point out that relational databases work very differently from spreadsheets and creating a separate table for each spreadsheet, assuming the formats are identical, would be considered poor practice. Tell us more about what you need to do with the data once it is in Access.

 

[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).

 

[Pat Hartman]

Importing files can be totally automated. The question is is the name always the same?

Look up the TransferSpreadsheet method. You can include the TransferSpreadsheet in a code loop if necessary.