Map excel columns to access

[sean42]

Good morning, interesting problem I hope. (I am using Office 2007)

At the moment, I have an Access table which is basically a spec for how different retailers raw excel files can be read into an Access Table. It lists the Field Name that will be used when importing into the Access table, the first data row etc.

E.g.
Retailer_No 1
File Name C:\RawFiles\Retailer1.xls
First_Import_Row 4
Unit_Sales A
Value_Sales D

I then use some code I found to open the File and then loop through to map what ever is in Column A to Unit Sales for example. Although it works, it relies on the user opening the excel file, writing down the column numbers and then editing the table above. What I would like to happen, is for Access to ask the user to select the Excel file, and then loop through the Key fields required (Unit Sales, Value sales etc) and ask the user to highlight the column for that field e.g. User highlights Column A and then this is used for Unit_Sales.

Can anyone help please?

 

[gemma-the-husky]

you could do this in code

import the table, inspect the columns to determine the column names - and go from there.

 

[sean42]

you could do this in code

import the table, inspect the columns to determine the column names - and go from there.

Unfortunately not, because the headings in the raw data are different retailer to retailer and the headings do not match the master headings I want to use when importing. Thats why I need to map them

 

[gemma-the-husky]

yes

import to a temporary table

then you can say

dim fld as field
for each fld in currentdb.tabledefs("yourtable").fields
'store fld.name in an array
next


now you have an array of the column names, that you can use for other purposes.

 

[sean42]

Good idea and one which I considered but there aren't always header names in the raw file. I was hoping there might already be a "ready made" wizard type function available.

Eg, Choose Excel Workbook, preview the chosen book.....Choose column for Unit Sales...choose column for Value Sales etc