Here is sample ADO code to connect and pass a date to an MS SQL stored procedure from Excel and MS Dynamics Integration Manager.

Excel Macro code example:

Sub PerDiem()

‘ Create a connection object.
Set cn = CreateObject(“ADODB.Connection”)
cn.Open “Provider=’SQLOLEDB’;Data Source=’YourSQLServerName’;Initial Catalog=’YourSqlDBName’;” & _ “Integrated Security=’SSPI’”
‘ declare other variables
Dim strDate As String
Dim advarChar As Integer
Dim adParamInput As Integer
Dim i As Integer

‘use this method to read cells from the active excel sheet
‘this reads cell in row 1 column 1 to get the date, example 5/07/2011
‘in this example using character data for the date key in the slashes
i = 1
strDate = ActiveWorkbook.Sheets(“YourExcelSheetName”).Cells(i, 1).Value

‘build the ADO command object, set the parameters, execute the command object
‘in this case the date is passed to the stored procedure
‘make sure to include the @ in the stored procedure parameter name
Set Cmd_obj = CreateObject(“ADODB.Command”)
Cmd_obj.ActiveConnection = cn
Cmd_obj.CommandText = “YourSqlStoredProcName”
Cmd_obj.CommandType = 4
advarChar = 129
adParamInput = 1
Cmd_obj.Parameters.Append Cmd_obj.CreateParameter(“@YourSqlStoredProcExpectedParameterName”, advarChar, adParamInput, 10, strDate)
Cmd_obj.Execute

MsgBox “Stored Proc Call Completed”
‘close the open record set
cn.Close: Set cn = Nothing
End Sub

VBA script attached to MS Dynamcis Integration Manager example:

This example integration uses the econnect adpater to connect to project acccounting.
The script is attached to the Before Integration script button.
The code prompts for the date in character format, example 5/07/2011.
As you can see the ADO command object syntax is almost identical.

Dim strDate, text
Dim advarChar
Dim adParamInput
Dim objConnection, Cmd_obj
strDate = InputBox (“Enter Week Ending Date “)
Set objConnection = Nothing
Set objConnection = CreateObject(“ADODB.Connection”)
objConnection.ConnectionString = “Provider=MSDASQL;DSN=YourODBCDSNnameforSQLDB;UID=sa;PWD=Yoursapassword;”
objConnection.Open
Set Cmd_obj = CreateObject(“ADODB.Command”)
Cmd_obj.ActiveConnection = objConnection
Cmd_obj.CommandText = “YourSQLStoredProcName”
Cmd_obj.CommandType = 4
advarChar = 129
adParamInput = 1
Cmd_obj.Parameters.Append Cmd_obj.CreateParameter(“@YourSQLStorProcExpectedParameter”, advarChar, adParamInput, 10, strDate)
Cmd_obj.Execute

Set Cmd_obj = Nothing
Set objConnection = Nothing