Populating Cells With SQL Server Data

[Salty Sea Dog]

I need to run multiple queries and insert their results into Excel cells. Each query will return only one result (a number, or a null). What is the best way to accomplish this?

Below is an example of the code I'm currently using. It does return data to the cell specified, but I'm guessing there's an easier way to populate multiple cells with the results of multiple queries.

Sub GetData()
Dim qt As QueryTable

sqlstring = "SELECT DISTINCT(r.result_text)" & vbCrLf & _
"FROM dt_sample s" & vbCrLf & _
"LEFT OUTER JOIN dt_field_sample fs ON (s.facility_id = fs.facility_id and s.sample_id = fs.sample_id)" & vbCrLf & _
"INNER JOIN dt_test t ON (s.sample_id = t.sample_id and s.facility_id = t.facility_id)" & vbCrLf & _
"INNER JOIN dt_result r ON (t.test_id = r.test_id and t.facility_id = r.facility_id)" & vbCrLf & _
"INNER JOIN rt_analyte a ON (r.cas_rn = a.cas_rn)" & vbCrLf & _
"INNER JOIN dt_well d ON (s.sys_loc_code = d.sys_loc_code)" & vbCrLf & _
"LEFT OUTER JOIN vw_locatiON l ON s.facility_id = l.facility_id and s.sys_loc_code = l.sys_loc_code" & vbCrLf & _
"LEFT OUTER JOIN dt_task ts ON (s.task_code = ts.task_code and s.facility_id = ts.facility_id)" & vbCrLf & _
"WHERE ((s.sample_date between '09/01/2008' and '12/31/2008') or s.sample_date is null)" & vbCrLf & _
"AND r.result_text is not null" & vbCrLf & _
"AND r.cas_rn = '79-01-6'" & vbCrLf & _
"AND s.sys_loc_code = 'K2'"

connstring = "ODBC;DSN=Fsn-db1\FSN_DB1;UID=;PWD=;Database=4040"

Set qt = ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("H12"), Sql:=sqlstring)
With qt
    .FieldNames = False
    .AdjustColumnWidth = False
    .PreserveFormatting = True
End With
 
qt.Refresh BackgroundQuery:=True

End Sub

[Salty Sea Dog]

Wow... this must be a lot more difficult than I thought...

[romperstomper]

Without knowing in what way the queries are different, or how (or if) they relate to any Excel data, it's impossible to answer really. It may be as easy as returning one recordset from your SQL server and then dropping the relevant records into particular cells, or you may be better off sticking with what you have.

[Salty Sea Dog]

The query will be exactly the same with one exception: The criteria in the last line will change.

For example, the first query looks for "AND s.sys_loc_code = 'K2'"
The second will be "AND s.sys_loc_code = 'MW25'"
The third "AND s.sys_loc_code = 'MW6'"

Etc.

When I try to just copy & paste the query and put the results in different cells, I get no data back at all. No errors or anything, just no data.

Does that make a little more sense?

[Salty Sea Dog]

How do I assign the result of a query (SQL Server) to a variable? Pesudo:

Dim varThis as string
Set varThis = Query result

Once the variable value is set, I would like to assign that value to a cell in Excel, in this case let's just say A1.

[DonkeyOte]

Although only hypothetical code please add CODE tags around your pseudo-code in your first post.

Set is only used with Objects (Workbook, Worksheet, Range etc...), Let for non-objects (strings, numeric types etc) which is implicit in VBA and thus does not need to be specified:

Dim varThis As String
varThis = Query Result
Range("A1").Value = varThis

Of course you could just assign the result to the cell directly

Range("A1").Value = Query Result

[Salty Sea Dog]

Although only hypothetical code please add CODE tags around your pseudo-code in your first post.

Set is only used with Objects (Workbook, Worksheet, Range etc...), Let for non-objects (strings, numeric types etc) which is implicit in VBA and thus does not need to be specified:

Dim varThis As String
varThis = Query Result
Range("A1").Value = varThis

Of course you could just assign the result to the cell directly

Range("A1").Value = Query Result

Ok, added CODE tags.

What is the actual code to assign the query result? I have posted my entire code as it currently stands below.

Sub GetData()
Dim qt As QueryTable

sqlstring = "SELECT DISTINCT(r.result_text)" & vbCrLf & _
"FROM dt_sample s" & vbCrLf & _
"LEFT OUTER JOIN dt_field_sample fs ON (s.facility_id = fs.facility_id and s.sample_id = fs.sample_id)" & vbCrLf & _
"INNER JOIN dt_test t ON (s.sample_id = t.sample_id and s.facility_id = t.facility_id)" & vbCrLf & _
"INNER JOIN dt_result r ON (t.test_id = r.test_id and t.facility_id = r.facility_id)" & vbCrLf & _
"INNER JOIN rt_analyte a ON (r.cas_rn = a.cas_rn)" & vbCrLf & _
"INNER JOIN dt_well d ON (s.sys_loc_code = d.sys_loc_code)" & vbCrLf & _
"LEFT OUTER JOIN vw_locatiON l ON s.facility_id = l.facility_id and s.sys_loc_code = l.sys_loc_code" & vbCrLf & _
"LEFT OUTER JOIN dt_task ts ON (s.task_code = ts.task_code and s.facility_id = ts.facility_id)" & vbCrLf & _
"WHERE ((s.sample_date between '09/01/2008' and '12/31/2008') or s.sample_date is null)" & vbCrLf & _
"AND r.result_text is not null" & vbCrLf & _
"AND r.cas_rn = '79-01-6'" & vbCrLf & _
"AND s.sys_loc_code = 'K2'"

connstring = "ODBC;DSN=Fsn-db1\FSN_DB1;UID=;PWD=;Database=4040"

Set qt = ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("H12"), Sql:=sqlstring)
With qt
    .FieldNames = False
    .AdjustColumnWidth = False
    .PreserveFormatting = True
End With
 
qt.Refresh BackgroundQuery:=True

End Sub

[DonkeyOte]

I believe you've asked that question (albeit in a slightly different flavour) elsewhere...

http://www.excelforum.com/excel-prog...rver-data.html

FYI: the member assisting you on your other thread is one of the "real" gurus so you will be hard pushed to find superior advice / assistance from anyone else... I would suggest you persevere on the above thread.

[Salty Sea Dog]

Ok, however I'm trying to learn different ways of accomplishing the same task(s) as one may be more efficient than the other.

I've stated that I'm not all that familiar with VBA. I'm just trying to explore my options. I also figured that asking in a different manner may bring more opinions and advice to the table. After all, if I can simply figure out how to assign the query result to a variable, I may very well be able to figure the rest out on my own.

If having two posts that are simliar is a problem, feel free to delete this one.

[romperstomper]

Yep. I'd probably use one query with an IN clause containing all the codes rather than lots of separate ones.

[Salty Sea Dog]

Yep. I'd probably use one query with an IN clause containing all the codes rather than lots of separate ones.

That's what I was doing initially. But how can I use the IN clause and populate multiple cells? I'm guessing I can assign a range:

Set qt = ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("H12:H20"), Sql:=sqlstring)

But then the issue of spaces comes up. The cells I'm going to populate have various blank rows inserted for formatting purposes. How can I get around that?

I was thinking something along the lines of assigning the query results to an array, and then populating individual cells with the values from that array. Would that work? If so, how would I do it? (I don't do a whole lot of VBA)

[Salty Sea Dog]

romperstomper,

That may be the way to go. I haven't tried getting the data back using an ADODB recordset.

I will give this a shot.

Thanks for your help!

[Salty Sea Dog]

I'm getting an error saying "Login failed for user". Is there a way I can set up the connection to use the current Windows user credendials?

[Salty Sea Dog]

Nevermind, got it.

[romperstomper]

Yep - replace the User ID bit with:

Integrated Security=SSPI

[Salty Sea Dog]

Ok, so this works. But I'm running into the same problem I ran into initially. I have roughly 40 or so queries I need to run. If I do the above 40 times and run each sub one-at-a-time I get my data back fine. However, if I attempt to run all of them, I only get data back for the last query (the last Sub in the editor). Is there a way around this?

[Salty Sea Dog]

Ah, if I don't use a new Sub each time it looks like the results may be different. Trying now.

[romperstomper]

Yes, that's the plan - run it once to get back ALL of the data, then loop through the recordset putting each bit in the right cell.

[Salty Sea Dog]

This looks like it's going to do the trick. Thank you! I appreciate the help.

[romperstomper]

Glad to help!

[gwunta]

I know this is an old thread, but I just wanted to say thanks to Romper Stomper for a simple and elegant solution to the problem. I've been working on the same problem for about 10 hours and reading the code was like turning on a light - kudos!!

[JapanDave]

I know this is an old thread, but I just wanted to say thanks to Romper Stomper for a simple and elegant solution to the problem. I've been working on the same problem for about 10 hours and reading the code was like turning on a light - kudos!!

Gwunta, I have to agree. I don't know how many times Romperstomper's code has helped me out, and helped me learn, which is why I always make a habit to subscribe to his threads ( Did not mean to talk about you Romper as if you were not here).

[romperstomper]

You're welcome.