Hi, I have searched the various forums to try and find a solution to my problem, but to no avail! And was hoping that the nice people here might be able to point me in the right direction!

I will try and explain this as clearly as possible:

I have an XL sheet that we are using as a rating engine. We plug various information in, and it spits out some results to a tab within the sheet. We will end up with a few hundred of these files (one for each account). When the rating sheet is complete, I would like to be able to upload the data from the sheet into various access tables. The problem that I have is that 14 tables need populating from the individual sheets. (250 columns of data in the sheet)

What I am trying to find out is: Is there a way to automatically get the data from the sheet to update the variuos tables? As this is a mamouth task if it has to be done manually!

I am thinking that possibly the best way would be to import into various temp tables and run append queries - but I am struggling with getting the data from the XL sheet into the tables.:mad:

Any help / guidance on this would be very much appreciated.

Thanks and Regards
Dingo

Will the source data always be in the same place for each sheet?
Can you provide a sample workbook that contains the data you want to port?

David

Hi David,

Thanks for your reply.

Yes - the source data will always be in the same place within the sheet.

I have attached an XL sheet that has 2 options for looking at the data. Option 1 is breaking the data out by table to import to and option 2 is just a list of all the results (Actually 287 columns!)

Regards
Dingo

Where's the attachment?

Sorry - Tried to attach a .xlsx file!

Please see attached.

Thanks
Dingo

Either option could be done if the layour never changes and there is only 1 row for each table and the column position never changes.

My preference would be to go for option 1. Reason being it is easer for the person populating the worksheet as it is tablular.

The method I would use would be to Set named ranges on your option 1 worksheet and link them accordingly then use the links to append to Access.

David

David,

Thank you for your response.

Could you expand on your last point please? As I don't fully understand how to go about setting the named ranges and linking them accordingly, then use the links to append access - this has been the part that I am stuck on.

Thanks & Regards
Dingo

Naming Ranges in Excel:

Click on the Option 1 worksheet

Highlight the range A1:K3

In the combo box on the function command line (the one that normally tells you what cell you a visiting) type in the name of this range, such as xlsTable1 then press return.

Save your spreadsheet and close it.

Now when you link the workbook select named ranges. The range xlsTable1 should appear. Select this and continue as normal. This should now link that range in Access. You can then use this to append to the correct table in Access.

David

David,

Thank you, Thank you, Thank you. It's so easy when you know how!

Appreciate your kind help on this. Hopefully that resolves my issues and I can now finish this project!

Regards
Dingo

Glad you resolved your problem and get your project finished.:)

I am using 2007 and have the issue now of linking a macro enabled sheet (.xlsm) to Access.

The sheet is the main rating engine so has lots of macros etc built into it. If I save it to a .xls (97-2003) file, then I loose some of the info, which is not great!

Is there a way round this please?

Thanks
Dingo

Have enabled the macro content and is it in a trusted location?

Do not use 2007 alot so I am guessing here.

David