Split Data in a column in Excel, via VBA Code in Access

[mkc80]

I have an excel file I need to read and update a column in an access table. But the data I need is in a combined format like AA-000, BB-001 etc.
I need to separate the values AA, BB as one column and 000,001 as another column.
How do I write code to accomplish this in a module in VBA.

So the tasks I need to do is:
read in the Excel file


split the data in a column in to two columns
update the already existing access table with the data in the two new columns.

Please advise.

[June7]

You import the records into a table that includes the combined field? Run an UPDATE query that uses that field as the source of data. The UPDATE can use string manipulation functions to extract and recombine the data parts. Will the alpha parts always be 2 letters? Will the number parts always be 3 digits?

Part1: Left([fieldname],2) & ", " & Mid([fieldname],InStr([fieldname],",")+2,2)
Part2: Mid([fieldname],4,3) & ", " & Mid([fieldname],InStr([fieldname],",")+5,3)

This looks like trying to save multiple values to the same field. A non-normalized data structure. How do you plan to use the data?

[ssanfu]

Do you mean UPDATE as in replace existing data in the table or
Add a a new record with the new data?

I would link to the excel file. Then the file looks like an Access table.
In code, open a recordset on that "Table" (the Excel file).
Use the Split() function.
Create an Update/Append query to change/add the data
loop through the recordset.

Post back with code attempts/field names if you have problems