Excel Populate Combo Box with Dynamic SQL Values

[sardine53]

Hello,

Excel 2013, SQL 2008R2

I am hoping this is the right forum and someone could point me in the right direction:

I have a Stored Procedure with a "Customer" parameter linking to an excel spreadsheet.

So far, I have an input box in Excel where the user can type the value and the command button will pull up the proper results:

Private Sub CommandButton1_Click()

Dim Customer As String

Member = Sheets("Sheet1").Range("K8").Value

With ActiveWorkbook.Connections("ABCWEB2 SQLReports sysdiagrams").OLEDBConnection
.CommandText = "EXEC dbo.usrsp_S_Customer_Census '" & Customer & "'"
ActiveWorkbook.Connections("ABCWEB2 SQLReports sysdiagrams").Refresh
End With
End Sub

My goal is to use a Combo Box for the user to select a value. I would like to use the stored procedure or SQL table to bring in the distinct values. As there will be new "Customer"s added frequently it needs to be a dynamic list.

How would I best accomplish this?

Thanks in advance for any assistance!

 

[SkipVought]

Hi,

In the sheet containing the query, the resultset range is a defined Named Range that adjusts each time you execute that query.

Simply reference that Range Name in the row source of your control object.

BTW, you can change the name to something simpler and more descriptive.

PS: I often use an in-cell Data > Validation > List which is much simpler and very conducive to on-sheet manipulation.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[sardine53]

Skip,

Thanks for your quick response.

I feel this is obvious for others but I think I need a bit more assistance as I am new to this in Excel.

After I add a Combo Box control, how do I get to the row source of the control object to reference the Range Name? Is it possible for you to use my code so I can figure this out.

(I do know I can change the name but don't want to complicate this for myself before I gain full understanding of the process.)

Thanks!

 

[SkipVought]

Are you building the workbook with code from scratch repeatedly?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[sardine53]

If I understand the question, the Excel workbook with associated tables will not be rebuilt repeatedly. I want to query SQL to pull in the selected "Customer" records. After the records are displayed, the user will remove the data connection and save the workbook with the records.

 

[SkipVought]

So this is a one time throw away workbook designed to capture a subset of data for a limited use over a limited period?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[sardine53]

Yes. Good way of putting it.

 

[SkipVought]

Did you ever think of making the WB reusable by allowing for variable criteria at future times the the user could select?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[sardine53]

I don't know if it would work for this purpose. The whole process can't change drastically or the users won't use this. They need to just fetch the data, save as, and go from there.

 

[SkipVought]

If your range is named MyList then

‘
   With ActiveWorkbook.Connections("ABCWEB2 SQLReports sysdiagrams").OLEDBConnection
      .CommandText = "EXEC dbo.usrsp_S_Customer_Census '" & Customer & "'"
      ActiveWorkbook.Connections("ABCWEB2 SQLReports sysdiagrams").Refresh
      YourControl.RowSource = [MyList]
   End With

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[sardine53]

Hate to keep bothering you but is this on the right track? (Though the data is not populated in the combo box yet.)

Private Sub ComboBox1_Change()

Dim Member As String

Member = Sheets("Sheet1").Range("K8").Value

With ActiveWorkbook.Connections("ABCWEB2 SQLReports sysdiagrams").OLEDBConnection
.CommandText = "EXEC dbo.usrsp_S_Customer_Census '" &Customer & "'"
ActiveWorkbook.Connections("ABCWEB2 SQLReports sysdiagrams").Refresh
ComboBox1.RowSource = ["Table_ExternalData_1"]
End With
End Sub

 

[SkipVought]

Though the data is not populated in the combo box yet

1) the sheet gets populated from you db on .Refresh

2) the control get populated from the sheet via the Named Range defining the resultset list

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[sardine53]

I did try to refresh. I'm just afraid I have something wrong with my syntax.

 

[SkipVought]

Sorry, was just on my iPad.

ComboBox1.ListFillRange = Activesheet.[Table_ExternalData_1].Address

NO QUOTES!

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[sardine53]

Tried with all suggestions and no luck. Any other ideas?

 

[SkipVought]

Is the active sheet, the sheet with the query when this code executes?

Where is your Combobox1?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[sardine53]

Active sheet (tab) is "Sheet1"
Combobox1 is on the "Sheet1" in cell K8
The SQL field I what is "Member"
The Parameter is "Customer"

 

[SkipVought]

Is the active sheet, the sheet with the query when this code executes?

If not, what is the sheet name that contains your QueryTable?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[sardine53]

Everything is on the same page "Sheet1

 

[SkipVought]

Please post your entire VBA procedure.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}