Use excel cell in SQL query string (function)

Question

i want to import data from a mssql database into my excel spreadsheet. Everything works fine with parameters. But i want to use a cell value in a function query:

Example:

select ROUND(dbo.fn_geteffort(3484, 'Project', 0, 1)/8,2) 

I want to use a cell value for 3484!

Any idea?

Answer 1

You mean the value '3484' is in a cell and you want to include that in your query string?

Then:

s = "select ROUND(dbo.fn_geteffort(" & Sheet.Cells(RowNumber, ColumnNumber) & ", 'Project', 0, 1)/8,2)"

Or:

s = "select ROUND(dbo.fn_geteffort(" & Sheet.Range("A1").Value & ", 'Project', 0, 1)/8,2)"

Answer 2

You will have to change your query with VBA scripting.

Worksheets("sheet_with_table").ListObjects(1).QueryTable.CommandText = "select ROUND(dbo.fn_geteffort(" & Worksheets("status").Range("D5").Value & ", 'Project', 0, 1)/8,2)"

If you want your query to update whenever you change status!D5, post following code in status worksheet event Change

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("D5")) Is Nothing Then
        Worksheets("sheet_with_table").ListObjects(1).QueryTable.CommandText = "select ROUND(dbo.fn_geteffort(" & Worksheets("status").Range("D5").Value & ", 'Project', 0, 1)/8,2)"
    End If
End Sub