Selective import?

[Jaazaniah]

I am working on a project where input files in .xls format will be
coming down, and after some simple stripping (either manual or by way
of a routine) will be ready to import 4 text fields and a currency
field into an existing table. The project is being built for people
who don't know Access, and is designed around forms to make their
tracking of info easier. I have a routine already that will allow them
to browse to and select the file to import, but I've never had to code
something like the import feature before. Here's the structure:

[LIKEY]: Autonumber, [Case]: Integer (link to more detailed table of
case info), [ExtID1]: Text, [ExtID2]: Text, [Desc]: Text, [Type]:
Text, [Cost]: Currency

There are more fields in the table, but my question is this:

Is there a way I can build a recordset, or otherwise import data from
an Excel sheet containing EtxID1, ExtID2, Desc, Type, and Cost into
the underlying table in such a way that would allow my script to set
Case to the currently referenced Case in the form, without disturbing/
causing issues with the Autonumber? I could write a few loops to
gather the info manually, but I know there has to be a better way.

Thanks.

 

[Klatuu]

I would suggest you link to the exel sheet as a table. Then use an append
query that will copy the fields you want into your existing table.

 

[Jaazaniah]

The sheets are all coming from external sources, not part of the
system. The primary person using the database will be able to
manipulate them into a set form. I could show her how to link it and
activate a query, but am unsure how it works in the first place. Never
linked to an excel sheet before.

I would suggest you link to the exel sheet as a table. Then use an append
query that will copy the fields you want into your existing table.
--
Dave Hargis, Microsoft Access MVP

I am working on a project where input files in .xls format will be
coming down, and after some simple stripping (either manual or by way
of a routine) will be ready to import 4 text fields and a currency
field into an existing table. The project is being built for people
who don't know Access, and is designed around forms to make their
tracking of info easier. I have a routine already that will allow them
to browse to and select the file to import, but I've never had to code
something like the import feature before. Here's the structure:

[LIKEY]: Autonumber, [Case]: Integer (link to more detailed table of
case info), [ExtID1]: Text, [ExtID2]: Text, [Desc]: Text, [Type]:
Text, [Cost]: Currency

There are more fields in the table, but my question is this:

Is there a way I can build a recordset, or otherwise import data from
an Excel sheet containing EtxID1, ExtID2, Desc, Type, and Cost into
the underlying table in such a way that would allow my script to set
Case to the currently referenced Case in the form, without disturbing/
causing issues with the Autonumber? I could write a few loops to
gather the info manually, but I know there has to be a better way.

Thanks.

 

[Klatuu]

Look in VBA Help for the TransferSpreadsheet method. That is what is used to
link, import, or export data from Excel.
--
Dave Hargis, Microsoft Access MVP

The sheets are all coming from external sources, not part of the
system. The primary person using the database will be able to
manipulate them into a set form. I could show her how to link it and
activate a query, but am unsure how it works in the first place. Never
linked to an excel sheet before.

I would suggest you link to the exel sheet as a table. Then use an append
query that will copy the fields you want into your existing table.
--
Dave Hargis, Microsoft Access MVP

I am working on a project where input files in .xls format will be
coming down, and after some simple stripping (either manual or by way
of a routine) will be ready to import 4 text fields and a currency
field into an existing table. The project is being built for people
who don't know Access, and is designed around forms to make their
tracking of info easier. I have a routine already that will allow them
to browse to and select the file to import, but I've never had to code
something like the import feature before. Here's the structure:

[LIKEY]: Autonumber, [Case]: Integer (link to more detailed table of
case info), [ExtID1]: Text, [ExtID2]: Text, [Desc]: Text, [Type]:
Text, [Cost]: Currency

There are more fields in the table, but my question is this:

Is there a way I can build a recordset, or otherwise import data from
an Excel sheet containing EtxID1, ExtID2, Desc, Type, and Cost into
the underlying table in such a way that would allow my script to set
Case to the currently referenced Case in the form, without disturbing/
causing issues with the Autonumber? I could write a few loops to
gather the info manually, but I know there has to be a better way.

Thanks.