CODEWITHSUNDEEP
sqlexcel
Andreas
Andreas

Reputation: 9

Use excel cell in SQL query string (function)

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?

Upvotes: -1

Views: 4288

Answers (2)

LS_ᴅᴇᴠ
LS_ᴅᴇᴠ

Reputation: 11181

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

Upvotes: 0

Raybarg
Raybarg

Reputation: 720

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)"

Upvotes: 1

Related Questions