I have bunch of excel files with data , please see sample excel file.
I need to transfer that data into access database using access vba, where columnar data in excel needs to be transferred as row data in database.
Description of Excel file, and transfering rules
A1 - A4 Contains data for MetNumber AccNumber OEBID Address . It should be common to to row 1-6.
B1-C1 - contains Year and UOM common to respective rows.
Column D -O represent monthly data from July(07) - June(06) . Each data must be transferred as a row in Access database
All records are alike and every 8th is a new MetNumber.
In sample access file I have a sample table which have transferred data from first 6 rows in sample excel file, so you can understand the pattern. I also have fields which have file name and row# in that file. So I can know where the particular data can be found in original excel file.
I have about 100 files with many rows, so would like to use access vba to select folder and it should go thorough files to update data. I am a bit lost how to convert column data into rows...
Any help would be appreciated