Import Excel data with Access VBA (complex)

[colintis]

Before asking the question I've been digging a hole that is deep enough to bury myself in google....Most of the results are simply importing the excel file to table with the same table structure.

What I need to ask, is looking for solution to import the excel data with specific column to specific fields in the tables. For example, I have excel file contains all student information, including their academic results, payments, etc..without separating into multiple worksheets. While in the access database I had split those information into tables, with new field names, ID and so on to setup the relationships.

My progress so far is able to open the excel file and get its workbook name. But I'm having trouble with importing the data into the tables one by one. Any help is greatly appreciate.

[ADezii]

1. Why can't you simply Import the Excel data in Normal fashion, and then use a series of Append Queries to push the Data wherever you want?
2. Link to the Excel Worksheet, then manipulate the Data?
3. Access the Excel Workbook directly via Automation Code, directly adding to Tables via Recordsets?

[colintis]

The excel file also contains some additional descriptions to some blank cells, which will mislead the database to recognized as part of the actual table data. It is something i want to skip, as there's also blank spaces in between the records and the descriptions.

Something new from the target user. The target user preferred to remove the excel file once everything have merged into Access, and there's some extra files that required to group all necessary data together in Access. So its breaking down those data into a new set of structure....

[ADezii]

How about a Sample Excel File, along with the Descriptions of the Access Tables (with Field Names, Data Types, etc) to which the Data will be pushed. Without a detailed explanation of all Components involved, a possible solution will not be forthcoming.

[colintis]

I had found out a method with the link table idea ADezii written in post #2. Basically I use vba to import the excel as a link table into Access, and make 2 recordsets (1 for excel 1 for access's table) to add them into the access table, after that vba will remove the link table.

I'm still organising this with more than one Access table to store the link table's data, i'll put the codes up later either it success or not