excel DNA

just a heads up .... getting really good results using something called
Excel DNA to code excel add-in functions using C# and Visual Studio. The
point of the C# code being I can then use ODBC to connect to the as400.

http://excel-dna.net/

Was able to write an Excel function which accepts a product code argument
and return the product description of that product code.

=GetProdDesc(A1,B1)

where cell A1 and B1 contain the two keys used to retrieve the product
description.

Excel-DNA produces an .xll file, an excel add in. Once the add-in is added
to the spreadsheet the functions are available.

To get the C# code to run you need to have client access installed on the
PC. And the ODBC data source has to be configed in ODBC administration. And
in the visual studio project the System.Data assembly has to be referenced.

Here is some sample code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using ExcelDna.Integration;
using System.Data.Odbc;
using System.Windows;

namespace Excel1
{
public static class ExcelFunctions
{
[ExcelFunction(Description = "Get product description")]
public static string GetProdDesc(string CustCode, string ProdCode)
{
var prodDesc = GetProductDescription(CustCode, ProdCode);
return prodDesc;
}

static OdbcConnection OpenConnection(string InDsn, string InUser,
string InPwd)
{
string connString = "DSN=" + InDsn + "; UID=" + InUser +
"; PWD=" + InPwd + ";";
var conn = new OdbcConnection(connString);
conn.Open();
return conn;
}

static string GetProductDescription(string Cucode, string ProdCode)
{
string vPrdes1 = null;
using (var conn = OpenConnection("vsas400", "BF1", "BF1"))
{
if (conn == null)
MessageBox.Show("Connection to as400 failed. Is ODBC setup
correctly?");
else
{

var cmd = conn.CreateCommand();
cmd.CommandText =
"select a.prdes1 from dtalib.prmast a " +
"where a.prcucd = ? and a.prprcd = ?";

{
var parm = new OdbcParameter("@Prcucd", OdbcType.Char, 6);
parm.Value = Cucode;
cmd.Parameters.Add(parm);
}
{
var parm = new OdbcParameter("@PrPrcd", OdbcType.Char, 25);
parm.Value = ProdCode;
cmd.Parameters.Add(parm);
}

using (OdbcDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
vPrdes1 = reader.GetString(0);
}
}

if (vPrdes1 == null)
vPrdes1 = "Prodcode " + ProdCode + " is not found";
}
}

return vPrdes1;
}

}
}