microsoft excelmicrosoft-excel-2007vba
I am trying show some data from my SQL Server in excel. Because I have a query that hits 3 tables and uses a parameter, I am going to have to use VBA.
I am not sure how to get the VBA to "auto run". Doing a normal query in Excel gives you options like running when the sheet is open or auto refreshing. Since I am adding a parameter, I would like the my Sub to run when I change the value of the cell the parameter is in.
Any ideas?
Here's the Dynamic SQL, ADODB way to do it (make sure and add the ADODB reference). This was built for string values in the cells. Also there's no error-catching code, please add that as needed:
Public Function Test(p1 As Range, p2 As Range, p3 As Range) As Integer
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strSQL As String
cnt.Open "Driver={SQL Server};Server=YY;Database=ZZ;Trusted_Connection=yes"
strSQL = "Select col4 from Table_1 where col1='" & p1.Value & _
"' and col2='" & p2.Value & _
"' and col3='" & p3.Value & "'"
rst.Open strSQL, cnt, adOpenStatic, adLockReadOnly, adCmdText
Test = rst("col4")
rst.Close
cnt.Close
Set rst = Nothing
Set cnt = Nothing
End Function
Here's the ADODB, Command-Parameter way, a little trickier. This one is just setup up for single chars:
Public Function Test(p1 As Range, p2 As Range, p3 As Range) As Integer
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim ccmd As New ADODB.Command
Dim PA1 As New ADODB.Parameter, PA2 As New ADODB.Parameter
Dim PA3 As New ADODB.Parameter
cnt.Open "Driver={SQL Server};Server=YY;Database=ZZ;Trusted_Connection=yes"
ccmd.ActiveConnection = cnt
ccmd.CommandText = _
"SELECT col4 FROM Table_1 WHERE col1=? AND col2=? AND col3=?"
ccmd.CommandType = adCmdText
Set PA1 = ccmd.CreateParameter("first", adChar, adParamInput, 1, p1.Value)
Set PA2 = ccmd.CreateParameter("second", adChar, adParamInput, 1, p2.Value)
Set PA3 = ccmd.CreateParameter("third", adChar, adParamInput, 1, p3.Value)
ccmd.Parameters.Append PA1
ccmd.Parameters.Append PA2
ccmd.Parameters.Append PA3
Set rst = ccmd.Execute
Test = rst("col4")
rst.Close
Set rst = Nothing
cnt.Close
Set cnt = Nothing
End Function
This VBA code will do it:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim wk As Workbook
Set wk = ThisWorkbook
Dim ws As Worksheet
Set ws = ActiveSheet
WatchedColumn = 2
BlockedRow = 1
TimestampColumn = 4
Crow = Target.Row
CColumn = Target.Column
If CColumn = WatchedColumn And Crow > BlockedRow Then
Cells(Crow, TimestampColumn) = Now()
End If
End Sub
You have to copy the code, go to View -> Macros in Excel, Create a new one (any name is valid) and on the left column double click the worksheet where you want to use it (red flag in the picture) and in the right side, paste the code.
This macro modifies the content of the cell on column D whenever theres a change on the same row on column B. The variable BlockedRow protects the first row because it usually has labels, if you have more than one row of labels changed the variable to 2 or more.
If you need to change the columns, make the change on the variables WatchedColumn and TimestampColumn. (A=1, B=2, C=3, D=4,... and so on).
Best Answer
Right click on the sheet tab and choose View Code. Put code like this in the sheet's module
This sub will run whenever a cell on that sheet is changed. If the cell that's changed is A1 (change to suit your data), then a procedure in a standard module named MySub (change to suit) will be run.
I'm not sure why you have to use VBA though. It seems like you could write SQL to pull from as many tables as you want and include a parameter.