Running this process through multiple records

[Mary10k]

I have 600 records in an excell spreadsheet. I need to import these into a table and run the following code to parse one field into 5 fields. Could you please let me know how to run the following code against multiple records rather than typing in the data individually?

Also, I need to create a table or export the cleansed data into a new spreadsheet when done.

Thanks.

Dim vAddr As String
vAddr = Trim(Me![Address])
Me![ZipCode] = Mid$(vAddr, InStrRight(vAddr, " ", Len(vAddr)) + 1)
vAddr = Mid$(vAddr, 1, InStrRight(vAddr, " ", Len(vAddr)) - 1)
Me![State] = Mid$(vAddr, InStrRight(vAddr, " ", Len(vAddr)) + 1)
vAddr = Mid$(vAddr, 1, InStrRight(vAddr, " ", Len(vAddr)) - 1)
Me![City] = Mid$(vAddr, InStrRight(vAddr, " ", Len(vAddr)) + 1)
Me![Street] = Mid$(vAddr, 1, InStrRight(vAddr, " ", Len(vAddr)) - 1)

 

[tvett]

assuming you have imported the spreadsheet into a table.

dim recin as adodb.recordset
set recin = new adodb.recordset
dim recout as adodb.recordset
set recout = new adodb.recordset

with recin
.open "your import table",currentproject.connection
recout.open "your output table
do until .eof
recout.addnew
Parse the fields and move to output column
recout.update
.movenext
loop
.close
end with
recout.close
set recin=nothing
set recout=nothing

 

[Mary10k]

While trying to use this code I got an error which highlights one line of code "recout.Open "Table3". Could you please help me with this?
Thanks.