Import Excel File into Access VBA code

[Sim0n]

Hey Everyone,

I am very new to access and do not have much of programming background behind me in VBA. I am unsure if what I want to do is even possible but from searching and finding help I still have not come across the issue I'm trying to take on. Basically I am trying to Import an Excel sheet into the Access Database System. (Click a button it asks for the excel file location, and Bam it adds the new information to the existing Table).

My issue is, to do this successfully I must add all the Columns into the Excel File that are not present and populate them myself with what Access requires. The Excel File (Which is a extract / export from an online program) only has 5 Columns required to populate the Access Table. The remaining information can be pulled from existing tables but I cannot get to that point.

I've hit the point now where I'm considering creating an Excel Macro that will change the formatting to the correct manner with populating the extra columns and required information. Would it be easier to try the Excel Macro or keep fiddling with scripting. Any thoughts and opinions will be greatly appreciated.

 

[DavidAtWork]

I'd suggest doing this in steps. If there are issues with the data types not matching, import the Excel file with 5 columns into a temp table first which has a text data type for each field, then move this data into another staging table that has the correct data types, transforming the data as you go and lastly you can append this data along with the missing data which you say can be got from existing tables, to your master table.
This can be done by minimal use of VBA and using append queries

David

 

[kumark]

Hi,

Import the excel file using the docmd.acimport, and u could write a appending query to insert the data to your main table..

Or

rename the excel headers to your standard after importing the file.

Regards
Kumar