Computer arithmetic

[Walter D Vaughan Jr]

> -----Original Message-----
> From: filepro-list-bounces+wvaughan=steelerubber.com at lists.celestial.com
> [mailto:filepro-list-
> bounces+wvaughan=steelerubber.com at lists.celestial.com] On Behalf Of Bill
> Campbell
> Sent: Monday, June 17, 2013 1:05 PM
> To: filepro-list at lists.celestial.com
> Subject: Re: Computer arithmetic

[Walter D Vaughan Jr] 
> Unfortunately, the last time I looked, Excel didn't have the ability to
connect
> to real SQL databases.

[Walter D Vaughan Jr] 

Well, 

You can. Below is a pretty simple (stripped down and untested) mechanism
that I use a lot with excel these days.
Say I have a column of stock codes in a spread sheet in column A, and in
column B I have the following formulas:
=BOMCost(A1), =BOMCost(A2), etc...
Column B would then have totals looked up from a stored procedure that takes
in a stock code number and returns a bunch of rows of data with costs for
example in this case.

I make sure my Developer Tools are turned on in Excel, and hit the Visual
Basic Icon.

I then paste in a function like below
===========================================
Function BOMCost(StockCode As String)

        Dim Conn As ADODB.Connection
        Dim ADODBCmd As ADODB.Command
        Dim rs As ADODB.Recordset
        Dim sConnect As String
        Dim SubCost as Double
        
        'Stored procedure name
        spName = "BOMComponets"
        strParameter = StockCode

        '--DEFINE CONNECTION STRING
        sConnect = "driver={sql server}; server=SQLServer; Database=ERP;
UID=limited_user; PWD=limited_user;"

        'Establish connection
        Set Conn = New ADODB.Connection
        Conn.ConnectionString = sConnect
        Conn.Open

        'Open recordset to get BOM Cost
        Set ADODBCmd = New ADODB.Command
        ADODBCmd.ActiveConnection = Conn
        ADODBCmd.CommandText = spName
        ADODBCmd.CommandType = adCmdStoredProc
        ADODBCmd.Parameters.Refresh
        ADODBCmd.Parameters(1).Value = strParameter
        Set rs = ADODBCmd.Execute()

        'Loop through recordset and place values
        rs.MoveFirst

        Do While Not rs.EOF

            SubCost = SubCost + rs.Fields("Cost")          
            rs.MoveNext

        Loop

        'Clean up
        rs.Close
        Set rs = Nothing

        BOMCost = SubCost
    
End Function
===========================================

There are a million examples of this on the web and dozens of connection
strings to work with any standard SQL server (my, post, etc...)