Maybe OT

[Rick Dilley]

This is my first post to this NG and I may be in the wrong place so please
be patient.

Problem:

I periodically receive an email with an attached Excel worksheet. .XLS
this is received at the desktop of a domain user in our domain.
We have an AS400 on the network as well.
We would like to take certain information from 2 different columns and
access a database on the AS400 to retrieve 3 data fields and place them into
3 new columns, when a "hit" exists in the AS400 database.

ODBC link to the AS400 data within Windows 2000 Professional is possible.

I am not adverse to having a batch extract from the XLS to a file that could
be uploaded to the AS400 and matched/updated on the AS400 and then
downloaded back into a new XLS.

The process should be fairly simple as the users is non-technical.
I would like to reduce the likelihood of errors by having as little user
intervention as possible.

OK you whiz kids... here is your chance to really demonstrate your
"interoperability" design skills.

Thank you in advance.



Rick Dilley

(e-mail address removed)
(908) 686-0513 # 263
(908) 696-9165(fax)

 

[SkipVought]

Rick,

You DID post in the PROGRAMMING Group.

I have done something like this before using DB2 ODBC
Driver. Here's the basic approch using DAO or ADO

Open Database

For Each Item in YourExcelTable
Open a Recordset with an SQL with parameters from the
Item Row
Write the Results from the Returned Recordset to the
Target Sheet
Next

Close Recordset
Close Database

Alternatively, you can use Data/Get External Data to set
up an initial query to your AS400 Database, get out, turn
on the Macro Recorder and record Data/Edit Query and
finish, turn off the recorder and observe, clean up &
modify your code. You can return data from this
QueryTable in a loop, stuffing the Where parameters in
Named Range Cells like this...

sQuery = "Select * From AS400Table Where Field1='" &
[Field1Value] & "' "

and Field1Value is a named range containing a parameter
value.

Does any of this sound viable?

SkipVought

 

[Stephen Bye]

You can use the SQL.REQUEST function with a SELECT statement to fetch the
field values from the corresponding records in the AS400 file via ODBC.