mapping from excel to access

[christian09]

I am a newbie to access. I have many excel files where I want to map to access. For instance in excel: the field under the text "name" is what I want to grab as name, and the field under the text "jobNumber" is what I want to import as jobNumber into access. The text I want to import is not always in the same cell each time, but is always under the title ie. "name."



Should I first alter the excel file with a macro, in order to make it more "mapable", or is there a way to do this within access?

Thank you very much.

[June7]

You can have VBA code in Access that will modify an Excel worksheet. Without knowing specifics can't really advise. Coding for interaction between apps can be complicated.

How are you accomplishing 'mapping'? Can't you just link to the sheets?

[christian09]

Hi,
I guess I say mapping b/c I used to use ACT!. I have like 500,000 different sheets(each is a separate file), can I still link all 500k w/o doing each separately? My other issue is I am trying to Link? certain fields in each excel sheet to access. This would be no problem normally, but I will use this example sheets(I just put both sheets in a file) I have attached. My goal is to have access find the correct name, id, title and link them to name,id,title in access. In sheet 1, name is in a different field from steve. The id I want comes in the field under "id". In sheet #2 the name is in B2 instead of A2. The id is to the right, instead of under the id label and is in row 7 instead of higher up.
I hope this helps,
Steve

[June7]

500,000!! For Real!! I haven't done a lot of VBA for Excel but this does look like could get very complicated, especially since I don't really understand what you want to accomplish. Are you trying to get data into Access tables? Or do you have to maintain these spreadsheets? All I can suggest is you attempt code and when have issues post specific questions.

[Stingaway]

I would suggest trying a straight link to one of the excel spreadsheets and see what you get - just use the External data tab and link to an Excel Spreadsheet. That will at least tell you if you need to do any kind of manipulation to you Excel sheets first...