Report from SQL to Excel using a ListBox

[Latoya]

Hi-

I am pulling a report from SQL in Excel. I got it all working, except I need multiple user prompts (one parameter with multiple values).

So I decided to use a listbox which contains all the possible selection that I want the user to choose from (These values are on another sheet in the same workbook) and then when a button is clicked, it displays the information in that same worksheet base on the selected choices the user choose.

This information that is being displayed is coming straight from SQL.

I tried using a userform in VBA in Excel, but I am still not quite sure how to have it pull from SQL base on the selections in the listbox.

I had it where the information was displayed, except I was only able to input one parameter at a time. I want the user to be able to do multiple selections and then the information is displayed.

In my query I had wmav_code = ? to prompt the user to input what code they wanted, bus as stated before, its just one. I tried wmav_code in (?,?,?,?), however, that's limiting the prompts to 4. I have 97 various codes to choose from (Hence the decision to use a listbox).

I tried (InStr(?, [wmav_code])>0), but Excel 2016 does not recognize that function (InStr()).

P.S. These are all within the 'WHERE' clause.

Any help is appreciated!!!

Thank you!

 

[kjv1611]

At least in my experience, I've found with that situation it is easiest to run the SQL totally from VBA rather than from a linked SQL parameter query. That way you have full control over it.

You can use an ADODB connection via vba where you build the SQL script in your VBA code. The way I like to do it is building the main portion of the SQL script as a variable called "strSQL1" then build the other piece(es) according to user input. So you might start out with say 3 or 4 different SQL string variables. Then after all the pieces are together, you build the ADODB connection and recordset concatenating the string variable pieces of the full SQL statement so SQL gets what it's expecting, but you stop pulling out your hair on your end.

The main headaches/hiccups if any on your end are:
[ol 1]
[li]Working with ADODB connections and recordsets when you're not used to it - it's not terribly difficult, but you need to find the correct syntax.[/li]
[li]You have to use the recordset and populate the fields for the rows using embedded loops - again not bad, but something else to do.[/li]
[li]All future edits will be in VBA - if you have to change the main SQL code, you probably will have to copy out the code to a text editor and remove all the VBA portions such as quotes around different lines.[/li]
[/ol]

I've built a few things that worked quite well with it, but it's worth keeping the caveats in mind.

And Another thing to consider: Is it possible to just feed the larger dataset to the user, and allow them to use Excel's table filters to filter what they need? Then you have less to maintain on your end. To me, it mainly depends upon how much source data we're talking about as well as whether there are any precautions needed for allowing the end user to only see so much.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[Latoya]

Hi kjv1611-

Thank you for your response.

It is just a simple report and I don't want to make it overly complicated per say.

The user just wants a weekly report base on the codes that they put in.

We use Crystal to generate the report, but we are converting it to a format that is more supported by our IT department, hence why I wanted to just generate the report in Excel. The problem is the prompts and the multiple codes that are to be prompted for.

I thought about filtering, but I want the user to just put the codes and the report generate base on those codes. That is how its done in the Crystal Reporting format that we have now.

I appreciate your suggestion, I will definitely use it on another report that I have to do.

For this one, I am leaning more towards somewhat of a simple process.

Thank you!

 

[SkipVought]

Hi,

There are a number of pieces to this puzzle. The first involves the method of obtaining the data. kjv611 likes ADODB. I've often gone this route, but in my opinion, it's more complex than necessary in this instance. I'd use MS Query.

In either case, you'll need to 1) find the correct connection to your database. With native MS Query, you'll need to locate the Microsoft ODBC Data Source Administrator (32-bit) on your PC and OPEN. 2) In the User DSN Tab, ADD the appropriate DRIVER for your DB (in your language) 3) Fill in the appropriate Data Source, Password etc, TEST the connection. And now you have a connection that you can choose from the MS Query dialog in Excel.

So Open MS Query in Excel, chose your new driver, construct your query WITHOUT the statement in the WHERE clause having your IN () statement. That can be added later. Return the recordset to Excel. This is a ONE TIME EVENT, adding a query object to your sheet. Edit your query and COPY the SQL from the SQL Box.

When you get that far, post back with your SQL code and we'll help you develop your VBA.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Please read my previous post.

This is another piece to the puzzle: How to get the selected values into the IN () statement. My approach is to 1) set up a table of values for the user to choose from. My Table is called ParmList. 2) set up a Structured Table (Insert > Tables > Table) names tList, with heading MyList, and a Data > Validation--LIST (using ParmList) as the means to select data values.

Then 3) paste this function into a module in your workbook...

Function MakeList(rng As Range, Optional sDEL As String = ",", Optional sQUO As String = "'") As String
'SkipVought 2017/05/24
'returns delimited list, default COMMA with SINGLE QUOTES
    Dim r As Range
    
    For Each r In rng
        MakeList = MakeList & sQUO & r.Value & sQUO & sDEL
    Next
    
    MakeList = Left(MakeList, Len(MakeList) - 1)
End Function

This is how you would use this function in your VBA code:

Sub ExecuteQuery()
    Dim sPath As String, sDB As String, sConn As String, sSQL As String
    
    sPath = ""      'path to your db
    
    sDB = ""        'name of your db here
    
    sConn = ""      'your connection string here
    
    sSQL = "SELECT *"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "FROM [YOUR_DB]"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "WHERE SOME_FLD IN (" & MakeList([b][tList[MyList]][/b]) & ")"
    
    Debug.Print sSQL
End Sub

And the result looks like this:
[pre]
SELECT *
FROM [YOUR_DB]
WHERE SOME_FLD IN ('s','d','f','g','k')
[/pre]
...because I made five selections in tList like this:
[pre]
MyList
s
d
f
g
k
[/pre]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Latoya]

Thank you Skip!

I am going to try this and let you know.

I had already done the connection, with Microsoft Query and I use my query to pull the records. and then edited the where clause in the table properties.

(InStr(?, [wmav_code)>0 seems to work for some trying to do what I am doing, however Excel 2016 does not recognize that function.

I don't necessarily have to use a list box, if there is a less complex way to achieve this.

I just want the user to be able to choose/input codes (from the 96) and base on those selections/input generate the report base on the query that is already in the property.

This is my code:
Select

Case
when wmhd_wo_status = '1' then 'Rejected'
when wmhd_wo_status = '2' then 'New'
when wmhd_wo_status = '3' then 'Submitted'
when wmhd_wo_status = '4' then 'Approved'
when wmhd_wo_status = '5' then 'In Progress'
when wmhd_wo_status = '6' then 'On Hold'
when wmhd_wo_status = '7' then 'Completed'
when wmhd_wo_status = '8' then 'Accounting Complete'
when wmhd_wo_status = '9' then 'Canceled'
else 'None'

end as [Work Order Status],

wmhd_wo_num [WO Number],
Convert (varchar (10), [wmhd_req_dt], 110) [Entry Date],
Convert (varchar (10), [wmhd_act_end_dt], 110) [Close Date],
wmha_loc_no [House Number],
wmha_street_name [Street Name],
wmav_code [Activity Code],
spav_desc [WO Details],
wmha_cross_st1 [Cross Street 1],
wmha_cross_st2 [Cross Street 2],

count (wmactvty.wmav_code) [Row Count]

From

wmheader
join wmhdradd on wmha_hdr_id = wmhd_wo_id
join wmactvty on wmav_act_id = wmhd_act_id
join spactvty on spav_code = wmav_code

Where

wmhd_svc_dept = '43100' and
wmha_street_name not like 'VARIOUS LOCATIONS' and
wmhd_wo_status in ('2', '5', '7') and
datepart (m, wmhd_req_dt) = datepart (m, dateadd (m, -1, getdate())) and
datepart (yyyy, wmhd_req_dt) = datepart (yyyy, dateadd (m, -1, getdate())) and
datepart (m, wmhd_act_end_dt) = datepart (m, dateadd (m, -1, getdate())) and
datepart (yyyy, wmhd_act_end_dt) = datepart (yyyy, dateadd (m, -1, getdate())) and

------addition after the data have been returned to excel from SQL.
wmav_code in (?,?,?,?)---- this allows the user to input up to four codes in a whatever cell i set to pass the parameter.

OR

wmav_code = ? ---- which passes only one parameter.

OR

(InStr(?,[wmav_code]>0) ---- which excel does not recognize.
----------------------------------------------------------------------------------------------------------

Group by

wmhd_wo_status, wmhd_wo_num, wmhd_req_dt, wmhd_act_end_dt, wmha_loc_no, wmha_street_name,
wmav_code, spav_desc, wmha_cross_st1, wmha_cross_st2

 

[SkipVought]

????

Did you read and understand my previous post that addressed entering a variable list?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Latoya]

Thanks for your help!

I will figure it out on my own.

Clearly you are not understanding what I am wanting without it being overly complicated.

I read your post and I comprehended what you said.

No need to be rude or mean.

Thanks for your help!

 

[SkipVought]

Great! You found a solution.

Please share your solution with our Tek-Tips members. This is how we can all benefit from your knowledge and determiation.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Latoya, sorry that we got off on the wrong foot.

I've done several variable IN() lists in queries. It seemed that your requirements were 1) choose from a list of 97 and 2) have more than 4 selections for the query.

You could use a multi-select list box, but that's an advanced approch that might not be as maintainable as a simpler approch of using a table/list with one column of Data > Validation > LIST, which is what and why I suggested what I did. I wanted a simple solution that incorporated both of your stated requirements.

As the thread progressed, other requirements popped up that ALL can be addressed. The key, however, is that rather than the query being strictly a parameter query, it is actually a variable substitution query, where ALL the variables for substitution, are derived from entries on a sheet in your workbook. But we never got that far.

I've used such an approch many times over the past 20 years.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Latoya]

Thank you Skip.

I was out for a bit, so I will work on this today and let you know how it goes.

Thanks
Latoya