Automatically connect all IDs from Multiple tables from an Excel Sheet (1 Viewer)

Hello All,

I hope this is the right section for my question,

I'm wondering if it is possible to have one Excel sheet with over 3000 entries and their attributes to be exported into multiple tables and have all their table IDs be connected with their related tables in Access. Can VBA perform such a task or is there s simpler option? I'm more then eager to learn if it is possible i'll just need the right steps and resources to follow.

Thank You

While in theory you can certainly do some sort of import and then split the contents into multiple tables, the question is somewhat ambiguous. WHY do you need to split the worksheet into multiple tables? We would need somewhat better a description of the dataset. But PLEASE use language, not code or jargon, to describe this dataset.

This is DEFINITELY a case where "the devil is in the details" - which is why I'm asking for a better description of what kind of things are in the dataset.

Well unfortunately I'm not the who has requested this action. Primarily I believe the person who wants to split the data, wants to create a user interfaces and have specific tables for their data, all their data was put in one excel sheet and now they want to convert it into access and believe there is a way to code it all to represent whats on the excel sheet and it will all be connect on access automatically

It's a very common problem moving stuff from Excel into Access, and people so often don't get it right.

I have blogged about it here:-
https://sites.google.com/site/msaccess457966vmfjg/articles-by-toeny-hine-awf-vip/excel-in-access

There is also a free tool to help you make the transition...

Sent from my SM-G925F using Tapatalk

Hello Uncle Gizmo,

Thanks for the website, however some of the images are not shown asking for to update the account, and the tool to convert excel to access doesn't seem to have any instructions, could I email you directly to see how the tool works? Will it automatically import into IDs across tables that are borrowing those IDs from another tables?

I believe one issue I have is that the spread sheet has fields that I would like to be tables, however there are a number of repetitive values, I would like to only have unique names not duplicates is that still possible to perform on the tool you brought up?

Thank You

In general, you can take any of a few approaches to deconvolute a spreadsheet such as you describe.

First, you could map the spreadsheet as an external table (since you are not planning to update the spreadsheet). This would ONLY work well if the spreadsheet doesn't have a lot of cell splits and cell joins that would lead to irregular grids.

Or in the same vein, you could use an Excel Application Object to gain a VBA viewpoint into the spreadsheet. Again, the more complex the grid, the trickier this gets.

Doing this another way, you can import the spreadsheet to a table for which ALL of the columns of the spreadsheet are declared to be of type text. Then you can use queries to extract pieces of each table.

In each case, the goal would be the same. In a series of operations, you would populate your final tables by extracting from the Excel-based data. Depending on the amount of data to be extracted and the complexity of the current spreadsheet with regard to split or joined columns, one of the above methods might work for you. The VBA method gives you the most flexibility in deciphering the worksheet but is technically the hardest. Queries that use the sheet as a mapped or imported data source would be easier but you would have greater issues with split/joined cells.