Import from XLS in tbl

[Guest]

I would like to import data from an xls file into an Access tbl and mark the
xls record as imported.

I have tblCandidates. I would like to:

1) open the XLS file for read/write
2) find the next record eligible to be read signified by an "ImportDate" col
with the date it was imported in it. If the record read is not blank then go
to the next record; that record has already been read into the tbl.
3) The imported data should apend to tblCandidates.Number the imported
PhoneNumber from the xls and tblCandidates.Candidate should receive LastName
from the xls:

Source data file RRD Export.XLS
Last Name PhoneNumber
Doe John 5556667777

tblCandidates should receive:
tblCandidates.Number:5556667777
tblCandidates.Candidatesoe John

When done, RRD Export.xls should look like:
Doe John 5556667777 08/06/2006

4) Once the first elligible record to be inported has been found, it should
read "X" amount of records from that point. "X" records should be determined
by an input, "Requested Record", from the user.
5) Each record imported should not aleady exist in tblCandidates.Number. If
it does exist, skip the import of that record and mark it as imported by
putting the current date in the "ImportDate" field.
6) Once the "X" number of records has been read, it should end.

I this is not a tough thing to do, but I have some holes in my knowlege and
could use the hand holding.

Thanks

 

[John Vinson]

1) open the XLS file for read/write

Unfortunately, due to a lawsuit last year, Microsoft was compelled to
remove that feature. You cannot open Excel for read/write by linking
it to Access.

You'll need to write VBA code to manipulate the data in Excel.

John W. Vinson[MVP]

 

[Guest]

Hi John,

The original data comes in a comma separated file. Could you help me if I
left it as a TXT file?

 

[John Vinson]

Hi John,

The original data comes in a comma separated file. Could you help me if I
left it as a TXT file?

A linked text file isn't updateble either. You can (again) write VBA
code; the simplest approach may be to import it into a (temporary)
table, make any edits or updates you need, rename the old file to
..bak, and write the modified file back out.

John W. Vinson[MVP]