Upload selected data to Access from Excel

[ketangarg86]

Hi,

I am working on a project where I need to upload selected data from multiple sheets of an excel file. Here is an example of what I want.

1. I want to create a table in Access with around 10 columns
2. Column 1 should be populated with the date field found in A2 cell of sheet 1 of the excel file
3. Column 2-5 should be populated with the columns B2-E200 in sheet 2 of the excel file.
4. Columns 6-7 would be populated based on values from columns 1-2 of the table. Basically Column 6 should be Column 1 date plus 60 days.
5. Column 8-10 would be user generated after the excel is imported and the user should have the ability to attach around 5 files to each row.

Could you please let me know if this is possible. Would really appreciate it.

Thanks.

 

[llkhoutx]

You don't same what version of Access you're using, the method is different for A2003 and A2007/2010/2013.

 

[ketangarg86]

I am using Access 2013. Thanks.

 

[llkhoutx]

Use the "External Data" ribbon, "Excel" (icon) in the "Import & Link" portion of that ribbon.

You can do this manually, importing the various Excel sheets into separate tables, then stacking them with a query.

If you want to do this programmatically, or on a repetitive basis research the code for that.

 

[The_Doc_Man]

Something you said makes me pause... It is possible that you are dealing with denormalized tables here. Your description is ambiguous enough that I can't tell with any certainty, but it might be a good idea to look at table normalization if you haven't already done so.

If you have looked into that topic, then I apologize for misunderstanding your description.

 

[ketangarg86]

Sure, thanks a lot. I will look at it and will let you know if I have any questions. Thanks again.