Excel Query into Validation List 1

[tchor7]

Multiple Validation Lists in worksheet (wk1). Validation list needs to pull data from another worksheet (wk2) - which houses 5+ columns/variables. Access db, etc not an option. Need to query wk2 with 1+ parameters/variables. Found articles on MS Query and on Validation lists, but none linking the two together.

MS Query:

http://tek-tips.com/faqs.cfm?fid=5829

Code for Validation list (Market needs to be an array variable - maybe passed though another function?):
Much Thanks, -Thoeum

Sub MarketValidate()

Dim xl As Object
Dim wkb As Workbook
Dim CUR As Worksheet
Dim DVR As Worksheet
Dim Market As String
Dim Region As String

Set xl = CreateObject("excel.application")
Set wkb = ThisWorkbook
Set CUR = wkb.Worksheets("MISTER Upload Request")
Set DVR = wkb.Worksheets("DataValuesR")

Region = "B9"
Market = "1. Choice1, 2. Choice2, 3. Choice3"

CUR.Range("B10").Select

With Selection.Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=Market
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub

 

[Gruuuu]

tchor, does the list need to update automatically when the file is open, or should it be manually refreshed?

In order to generate a list based on a parameter or variable, you can either use a parameter query in MS Query, or use VBA to alter the query string. Using VBA it's possible to have the query change dynamically or automatically. I'm pretty sure the Parameter Query would be a manual thing, but I don't use it that often, so I may be wrong.

 

[SkipVought]

This is super simple.

Option 1: Parameter query - a NON VBA solution

EDIT your current query and in the QBE Grid, enter criteria VALUES in each or Region & Market respecitvely...
[tt]
[What Region?]
[What Market?]
[/tt]
What goes inside the BRACKETS is up to you.

Return data to Excel and answer the parameter requests.

On the sheet, in the QueryTable, open the Query Parameters window.

Select the LAST option. Note that you can run the query when one or either of these cell locations change.

Option2: VBA

In the VB Editor...

sub RunQuery()
   dim sPath as string, sDB as string, sConn as string, sSQL as string

   spath = thisworkbook.path

   sdb = thisworkbook.name

   sconn = ""

   ssql = ""

   with Sheets("YourSheetName").listobjects(1).querytable
 debug.print .connection
 debug.print .commandstring
   end with
end sub

COPY the results in the Immediate Window, post back and PASTE the strings for help with the final step.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[tchor7]

Skip - much thanks. Got the query to run, but now need to pass the results to a validation list. The validation list will be populated in a column (with 500+ rows), so the procedure will repeat. I will repeat this entire process about 10 times for other criterias. I'm testing if performance will be an issue. We have about 200+ users.

query code:

Sub RunQuery()
Dim sPath As String, sDB As String, sConn As String, sSql As String
Dim sRegion As String, sMarket As String

sPath = ThisWorkbook.Path
sDB = ThisWorkbook.Name

sRegion = ThisWorkbook.Worksheets("Form").Range("A2")
sMarket = ThisWorkbook.Worksheets("Form").Range("A4")

sConn = "DSN=Excel Files;DBQ=H:\QueryTest.xlsm;"
sConn = sConn & "DefaultDir=H:\;DriverId=1046;"
sConn = sConn & "MaxBufferSize=2048;PageTimeout=5;"

sSql = "SELECT DISTINCT tbl_Market.STATE"
sSql = sSql & " FROM tbl_Market tbl_Market"
sSql = sSql & " WHERE (tbl_Market.Region=" & "'" & sRegion & "')"
sSql = sSql & " AND (tbl_Market.Market=" & "'" & sMarket & "')"
sSql = sSql & " ORDER BY tbl_Market.STATE"

With Sheets("Sheet4").ListObjects.Add(SourceType:=0, Source:= _
"ODBC;" & sConn, Destination:=Range("$a$3")).QueryTable
.CommandText = Array(sSql)
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Table_Query_from_Excel_Files_2"
.Refresh BackgroundQuery:=False
End With

End Sub

my validation list code:

Sub MarketValidate()

Dim xl As Object
Dim wkb As Workbook
Dim CUR As Worksheet
Dim DVR As Worksheet
Dim Market As String
Dim Region As String

Set xl = CreateObject("excel.application")
Set wkb = ThisWorkbook
Set CUR = wkb.Worksheets("Form")
Set DVR = wkb.Worksheets("Data")

Region = "A2"
Market = "1. Choice1, 2. Choice2, 3. Choice3"

CUR.Range("A4").Select

With Selection.Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=Market
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub

 

[SkipVought]

Hi,

you really do not want to ADD a querytable each time your run this, UNLESS you first DELETE the existing QueryTable first. BTW, I NEVER delete and add querytables. I MODIFY the existing QT CommandString and/or COnnection paraneters.

I Likewise, the validation list reference does NOT need to be deleted and re-added, IF you use a Named Range for the List reference. I regularly use dynamic QueryTable results as a reference for Data Validation lists, with VBA code that simply redefines the Named Range.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[tchor7]

Hi Skip, thanks for the response. I like the idea of using a dynamic QueryTable. I was also considering storing the data in a recordset.

http://www.ozgrid.com/forum/showthread.php?t=18289&page=1

Ultimately, I will be querying from two worksheets. If I use the Named Range method, I will have potentially 13 QueryTable's for the 13 columns of Validation Lists. Tried adding the Named Range for:

=Table_Query_from_Excel_Files_2[[#Headers],[STATE]]

My query result is:
STATE
CA
ID
AZ

But it only shows "STATE" as the value. Please help. Thanks.

 

[SkipVought]

    With Sheets("YourSheetName").ListObjects(1).QueryTable
        .Connection = sConn
        .commandstring = sSQL
        Application.DisplayAlerts = False
'this names your range as STATE, the heading name
        .ResultRange.CreateNames True, False, False, False
        Application.DisplayAlerts = True
    End With

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[tchor7]

Hi Skip - ended up using a parameter query, which worked great.
Question: Is it possible to use a parameter query for multiple cell references. I found your answer in the thread below, but that solution will not work for me.

http://www.tek-tips.com/viewthread.cfm?qid=1318984&page=424

e.g. User input column STATE: cell A1 = WA, A2 = CA
What I want is a validation list in cell B1 and B2 that will show the Counties, from the queryTable. My table is much more complicated than this and we will have up to 500+ rows.
I'm thinking I may have to use VBA. After the user selects the STATE, I could call a procedure to run the query and output it to.. say Worksheet2!A2. And I would have a validation list for the County in Worksheet1!B1.

Let me know what you think. Thanks again.
-Thoeum

 

[SkipVought]

Question: Is it possible to use a parameter query for multiple cell references

YES.

Seems as if you need a lookup table that has State & County.

From there you can structure a Data >Vookup --LIST using the OFFSET Spreadsheet function, that employs the Match() & COUNTIF() funstions.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]