I'm a network administrator for an insurance agency with little experience
using Excel to access external data sources (like Access databases, etc).
I'm trying to accomplish a simple task, but efforts to figure it out on my
own have so far been unfruitful. I'm hoping someone here can point me in
the right direction.

We have a fairly complex spreadsheet in Excel that calculates experience
ratings used in worker's compensation insurance. Currently, the agent has
to look up a particluar Class Code in a printed table containing about 600
Class Codes to find and enter two corresponding values for that Class
Code -- they're called the ELR and the D-RATIO -- to be used in the
spreadsheet calculations. I'd like to automate this process. In other
words, I want the agent to simply type the Class Code in the appropriate
field in Excel, and have the corresponding ELR and D-RATIOS auto-populate
from an electronic table into their respective fields in Excel.

It's fairly simple conceptually, but I haven't been able to determine the
best way to do this. I created an Access database with a single, simple
table into which I entered the values for the three fields: Class Code, ELR,
and D-RATIO, with Class Code set to be the primary key. However, I haven't
figured out how to get Excel to query the Class Code in this table and
return the values from the other two fields into the spreadsheet. I've
created a query in the Database that I think would accomplish this. In
Excel, I go to Data Get External Data New Database Query and try to
browse to the database or to the query, but this gets me nowhere.

Am I making this more complicated than it needs to be? Any help is
appreciated. I have a feeling I might be going about this the wrong way,
but haven't found much help from online searches or the built-in help.

Btw, I'm using Office 2000.

Thanks,

Bryan

http://support.microsoft.com/?id=295646
Q295646 HOWTO: Transfer Data from ADO Data Source to Excel with ADO


From Andy Wiggins:

This might be a help for getting data to and from Excel and Access: It
includes examples of using variables in SQL queries.
http://www.bygsoftware.com/examples/sql.html

Or you can get there from the "Excel with Access Databases" section on page:
http://www.bygsoftware.com/examples/examples.htm

It demonstrates how to use SQL in Excel's VBA to:

* create a database,
* create a table and add data to it,
* select data from a table,
* delete a table,
* delete a database.

You can also download the demonstration file called "excelsql.zip".

The code is open and commented.


--

Regards
Andy Wiggins
www.BygSoftware.com

==========================

Mr Erlandsen's site:

http://www.erlandsendata.no/english/...php?t=envbadac

-----------------------

http://support.microsoft.com/default...b;en-us;257819
How To Use ADO with Excel Data from Visual Basic or VBA

--
Regards,
Tom Ogilvy

"Bryan Linton" wrote in message
...
I'm a network administrator for an insurance agency with little experience
using Excel to access external data sources (like Access databases, etc).
I'm trying to accomplish a simple task, but efforts to figure it out on my
own have so far been unfruitful. I'm hoping someone here can point me in
the right direction.

We have a fairly complex spreadsheet in Excel that calculates experience
ratings used in worker's compensation insurance. Currently, the agent
has
to look up a particluar Class Code in a printed table containing about 600
Class Codes to find and enter two corresponding values for that Class
Code -- they're called the ELR and the D-RATIO -- to be used in the
spreadsheet calculations. I'd like to automate this process. In other
words, I want the agent to simply type the Class Code in the appropriate
field in Excel, and have the corresponding ELR and D-RATIOS auto-populate
from an electronic table into their respective fields in Excel.

It's fairly simple conceptually, but I haven't been able to determine the
best way to do this. I created an Access database with a single, simple
table into which I entered the values for the three fields: Class Code,
ELR,
and D-RATIO, with Class Code set to be the primary key. However, I
haven't
figured out how to get Excel to query the Class Code in this table and
return the values from the other two fields into the spreadsheet. I've
created a query in the Database that I think would accomplish this. In
Excel, I go to Data Get External Data New Database Query and try to
browse to the database or to the query, but this gets me nowhere.

Am I making this more complicated than it needs to be? Any help is
appreciated. I have a feeling I might be going about this the wrong way,
but haven't found much help from online searches or the built-in help.

Btw, I'm using Office 2000.

Thanks,

Bryan

The "excelsql.zip" demonstration file gave me exactly what I needed, and the
spreadsheet is purring right along now.

Thanks Tom!

B

"Tom Ogilvy" wrote in message
...

http://support.microsoft.com/?id=295646
Q295646 HOWTO: Transfer Data from ADO Data Source to Excel with ADO


From Andy Wiggins:

This might be a help for getting data to and from Excel and Access: It
includes examples of using variables in SQL queries.
http://www.bygsoftware.com/examples/sql.html

Or you can get there from the "Excel with Access Databases" section on
page:
http://www.bygsoftware.com/examples/examples.htm

It demonstrates how to use SQL in Excel's VBA to:

* create a database,
* create a table and add data to it,
* select data from a table,
* delete a table,
* delete a database.

You can also download the demonstration file called "excelsql.zip".

The code is open and commented.


--

Regards
Andy Wiggins
www.BygSoftware.com

==========================

Mr Erlandsen's site:

http://www.erlandsendata.no/english/...php?t=envbadac

-----------------------

http://support.microsoft.com/default...b;en-us;257819
How To Use ADO with Excel Data from Visual Basic or VBA

--
Regards,
Tom Ogilvy

"Bryan Linton" wrote in message
...
I'm a network administrator for an insurance agency with little
experience
using Excel to access external data sources (like Access databases,
etc).
I'm trying to accomplish a simple task, but efforts to figure it out on
my
own have so far been unfruitful. I'm hoping someone here can point me
in
the right direction.

We have a fairly complex spreadsheet in Excel that calculates experience
ratings used in worker's compensation insurance. Currently, the agent
has
to look up a particluar Class Code in a printed table containing about
600
Class Codes to find and enter two corresponding values for that Class
Code -- they're called the ELR and the D-RATIO -- to be used in the
spreadsheet calculations. I'd like to automate this process. In other
words, I want the agent to simply type the Class Code in the appropriate
field in Excel, and have the corresponding ELR and D-RATIOS
auto-populate
from an electronic table into their respective fields in Excel.

It's fairly simple conceptually, but I haven't been able to determine
the
best way to do this. I created an Access database with a single, simple
table into which I entered the values for the three fields: Class Code,
ELR,
and D-RATIO, with Class Code set to be the primary key. However, I
haven't
figured out how to get Excel to query the Class Code in this table and
return the values from the other two fields into the spreadsheet. I've
created a query in the Database that I think would accomplish this. In
Excel, I go to Data Get External Data New Database Query and try to
browse to the database or to the query, but this gets me nowhere.

Am I making this more complicated than it needs to be? Any help is
appreciated. I have a feeling I might be going about this the wrong
way,
but haven't found much help from online searches or the built-in help.

Btw, I'm using Office 2000.

Thanks,

Bryan