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.

Please Login or Register  to view this content.

[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:

Please Login or Register  to view this content.

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.

[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:

Please Login or Register  to view this content.

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)

[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:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

[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:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

[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.

[DonkeyOte]

I will merge the two... what you're doing is not trivial per se so kudos for trying it, once you get the hang of the approach rest assured you'll be flying.

[Salty Sea Dog]

I will merge the two... what you're doing is not trivial per se so kudos for trying it, once you get the hang of the approach rest assured you'll be flying.

Thanks for the merge. And I appreciate the help

[romperstomper]

You've got two basic choices as I see it:
1. Return the querytable to one contiguous range, then use formulas in your cells to lookup the relevant item for each; or
2. Use VBA and ADO to return a recordset containing all your data, then loop through populating the cells from individual records.

Option 1 is easier, option 2 is neater. I'm on a phone right now, so don't have code to hand for 2, and I won't do until next week probably, but let us know which you prefer.

[Salty Sea Dog]

I prefer code whenever possible. Option 2 sounds like the way to go.

[RxMiller]

I would agree with everyone above, there are several ways to do this.
Now, in my case, I was just using an Access front-end linked to SQL Server tables. This is one lengthy method that I use for updating about 14 Query Defs with SQL code, then calling on a Excel Template with 14 named ranges to just transfer the data. When the customer wants the report reformatted, it is often just a matter of changing the Excel Template instead a lot of code.
Another way, I like to use SQL Pass-Through queries. All the processing is done by SQL Server - only the results come across the network.

If this is not useful and too large, please feel free to delet it:

Please Login or Register  to view this content.

[romperstomper]

Here's some sample code for retrieving a recordset from a SQL server db and dumping it into a worksheet. We would need a bit more detail about what goes where to customise it for your situation:

Please Login or Register  to view this content.

[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:

Please Login or Register  to view this content.

[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!!

[romperstomper]

You're welcome.

[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).