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?

Raybarg · Accepted Answer · 2013-07-11 07:25:53Z

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

answered Jul 11, 2013 at 7:25

Raybarg

7206 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Andreas Over a year ago

My table name is "status" and the value is in cell D5 - can you please make an example on this infos... Thanks

LS_ᴅᴇᴠ · Accepted Answer · 2013-07-11 08:40:36Z

0

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

answered Jul 11, 2013 at 8:40

LS_ᴅᴇᴠ

11.2k1 gold badge27 silver badges46 bronze badges

6 Comments

Andreas Over a year ago

Thanks for your post! I use thq Excel Microsoft Query Tool - no VBA at the moment. it unclear for me, how can i use your informations...

Andreas Over a year ago

you mean, i must use vba? Do you have time to give me a simple example to query a database from VBA-code... ;))))

LS_ᴅᴇᴠ Over a year ago

Short introduction to VBA: On Excel: Alt+F11 >> Double click the status worksheet on left tree, something like "Sheet4 (Status)">> paste code from answer, replacing sheet_with_table with the name of data worksheet. And voila, it should be working!

LS_ᴅᴇᴠ Over a year ago

You don't have to do all work in VBA. You can use your current worksheet, with your data and query. With VBA you will just change SQL command.

Andreas Over a year ago

it doesn´t work. i tried the following: 1. data - another data sources - ms query 2. insert the sql query "select ROUND(dbo.fn_geteffort(" & Sheet.Range("A1").Value & ", 'Project', 0, 1)/8,2)" 3. Insert your VB code. any idea?

|

Collectives™ on Stack Overflow

Use excel cell in SQL query string (function)

2 Answers 2

1 Comment

6 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

6 Comments

Related