Excel Vba - Sql Query And Field Mappings

I have created an excel spreadsheet used for calculating shipping rates based on miles using an add-in for =GetGoogleDistance to do the calculations. Since you are limited to just

Solution 1:

I think all you need to do is modify your getGoogleDistance method to:

1. Check SQL Server to see if the value already exists
2. If it does, return that value -- no call to Google
3. If it doesn't, run your normal function and then insert that value into SQL Server

As far as how to do steps #1 and #3, there is no shortage of examples if you know where to look. If you search ADO, SQL Server, VBA I think will see more examples than you can shake a stick at. As an alternative to Step 1, you can also use MS Query, which is built into MS Excel, and that would eliminate some VBA, but it doesn't preclude you from having to use ADO since there is nothing built-in (that I know of) to manage the inserts.

This is a really bare-bones (and untested) example of how you could do the insert:

Dim conn As ADODB.Connection
Dim cmd AsNew ADODB.Command
Dim cs AsStringSet conn = New ADODB.Connection

cs = "Provider=SQLOLEDB;Data Source=<whatever>\<whatever>;" & _
     "Initial Catalog=<whatever>;" & _
     "Integrated Security=SSPI;"

conn.ConnectionString = cs

cmd = New ADODB.Command
cmd.ActiveConnection = conn
cmd.CommandText = "insert into [Distances] values (@FROM, @TO, @DIST)"
cmd.NamedParameters = True

cmd.Parameters.Append cmd.CreateParameter("@FROM", adVarChar, adParamInput, 256, fromValue)
cmd.Parameters.Append cmd.CreateParameter("@TO", adVarChar, adParamInput, 256, toValue)
cmd.Parameters.Append cmd.CreateParameter("@DIST", adNumeric, adParamInput, 256, distance)

cmd.Execute