scalestwo
04-07-2007, 06:36 PM
hi all .. first time here for me..
I've done a lot of research on this site and i either cant find my issue or i cant interpret correctly.
1. i have an Excel user form, the excel userform has 3 boxes that can allow for multiple selections in each box.
2. the values are returned to Excel in columns A, B, C, D. Each column could have 1 or many returned values.
3. I'm trying to pass those values in a SQL query
I tried this code below but i dont know how to be able to read all the values in a column and pass to the query..
Basically i dont want all these 'And' statements and 'OR' statements in my 'where' clause.. how can shrink this code up?
DataWrkSht = Sheets("SelectionCriteriaResult")
Div1 = DataWrkSht.Cells(5, 1).Value
Div2 = DataWrkSht.Cells(6, 1).Value
Div3 = DataWrkSht.Cells(7, 1).Value
Div4 = DataWrkSht.Cells(8, 1).Value
Div5 = DataWrkSht.Cells(9, 1).Value
Div6 = DataWrkSht.Cells(10, 1).Value
Div7 = DataWrkSht.Cells(11, 1).Value
Div8 = DataWrkSht.Cells(12, 1).Value
Div9 = DataWrkSht.Cells(13, 1).Value
Div10 = DataWrkSht.Cells(14, 1).Value
ExpGrp1 = DataWrkSht.Cells(5, 9).Value
ExpGrp2 = DataWrkSht.Cells(6, 9).Value
ExpGrp3 = DataWrkSht.Cells(7, 9).Value
Year1 = DataWrkSht.Cells(5, 17).Value
Year2 = DataWrkSht.Cells(6, 17).Value
Year3 = DataWrkSht.Cells(7, 17).Value
Table = "EXPENSE_X_CBACK"
Field1 = "DIV"
Field2 = "EXPENSE_GROUP"
Field3 = "YEAR"
TableField1 = Table & "." & Field1
TableField2 = Table & "." & Field2
TableField3 = Table & "." & Field3
With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
.Connection = _
"ODBC;DSN=xxxxx;Description=xxxxx;APP=Microsoft Office 2003;WSID=xxxxxxx;DATABASE=xx_xxxxx;Trusted_Connection=Yes"
.CommandType = xlCmdSql
.CommandText = "Select * " _
& "FROM pt_aawokmgr.dbo." _
& Table _
& " WHERE ((" & TableField & "='" & Div1 & "') " _
& "Or (" & TableField1 & "='" & Div2 & "')" _
& "Or (" & TableField1 & "='" & Div3 & "')" _
& "Or (" & TableField1 & "='" & Div4 & "')" _
& "Or (" & TableField1 & "='" & Div5 & "')" _
& "Or (" & TableField1 & "='" & Div6 & "')" _
& "Or (" & TableField1 & "='" & Div7 & "')" _
& "Or (" & TableField1 & "='" & Div8 & "')" _
& "Or (" & TableField1 & "='" & Div9 & "')" _
& "Or (" & TableField1 & "='" & Div10 & "'))" _
& "And ((" & TableField2 & "='" & ExpGrp1 & "')" _
& "Or (" & TableField2 & "='" & ExpGrp2 & "')" _
& "Or (" & TableField2 & "='" & ExpGrp3 & "'))" _
& "And ((" & TableField3 & "='" & Year1 & "')" _
& "Or (" & TableField3 & "='" & Year2 & "')" _
& "Or (" & TableField3 & "='" & Year3 & "'))" _
.CreatePivotTable TableDestination:="'[MI Tool.xls]ListBox List'!R30C1", _
TableName:="PivotTale2", DefaultVersion:=xlPivotTableVersion10
End With
Edited 10-Apr-07 by geekgirlau. Reason: insert vba tags
I've done a lot of research on this site and i either cant find my issue or i cant interpret correctly.
1. i have an Excel user form, the excel userform has 3 boxes that can allow for multiple selections in each box.
2. the values are returned to Excel in columns A, B, C, D. Each column could have 1 or many returned values.
3. I'm trying to pass those values in a SQL query
I tried this code below but i dont know how to be able to read all the values in a column and pass to the query..
Basically i dont want all these 'And' statements and 'OR' statements in my 'where' clause.. how can shrink this code up?
DataWrkSht = Sheets("SelectionCriteriaResult")
Div1 = DataWrkSht.Cells(5, 1).Value
Div2 = DataWrkSht.Cells(6, 1).Value
Div3 = DataWrkSht.Cells(7, 1).Value
Div4 = DataWrkSht.Cells(8, 1).Value
Div5 = DataWrkSht.Cells(9, 1).Value
Div6 = DataWrkSht.Cells(10, 1).Value
Div7 = DataWrkSht.Cells(11, 1).Value
Div8 = DataWrkSht.Cells(12, 1).Value
Div9 = DataWrkSht.Cells(13, 1).Value
Div10 = DataWrkSht.Cells(14, 1).Value
ExpGrp1 = DataWrkSht.Cells(5, 9).Value
ExpGrp2 = DataWrkSht.Cells(6, 9).Value
ExpGrp3 = DataWrkSht.Cells(7, 9).Value
Year1 = DataWrkSht.Cells(5, 17).Value
Year2 = DataWrkSht.Cells(6, 17).Value
Year3 = DataWrkSht.Cells(7, 17).Value
Table = "EXPENSE_X_CBACK"
Field1 = "DIV"
Field2 = "EXPENSE_GROUP"
Field3 = "YEAR"
TableField1 = Table & "." & Field1
TableField2 = Table & "." & Field2
TableField3 = Table & "." & Field3
With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
.Connection = _
"ODBC;DSN=xxxxx;Description=xxxxx;APP=Microsoft Office 2003;WSID=xxxxxxx;DATABASE=xx_xxxxx;Trusted_Connection=Yes"
.CommandType = xlCmdSql
.CommandText = "Select * " _
& "FROM pt_aawokmgr.dbo." _
& Table _
& " WHERE ((" & TableField & "='" & Div1 & "') " _
& "Or (" & TableField1 & "='" & Div2 & "')" _
& "Or (" & TableField1 & "='" & Div3 & "')" _
& "Or (" & TableField1 & "='" & Div4 & "')" _
& "Or (" & TableField1 & "='" & Div5 & "')" _
& "Or (" & TableField1 & "='" & Div6 & "')" _
& "Or (" & TableField1 & "='" & Div7 & "')" _
& "Or (" & TableField1 & "='" & Div8 & "')" _
& "Or (" & TableField1 & "='" & Div9 & "')" _
& "Or (" & TableField1 & "='" & Div10 & "'))" _
& "And ((" & TableField2 & "='" & ExpGrp1 & "')" _
& "Or (" & TableField2 & "='" & ExpGrp2 & "')" _
& "Or (" & TableField2 & "='" & ExpGrp3 & "'))" _
& "And ((" & TableField3 & "='" & Year1 & "')" _
& "Or (" & TableField3 & "='" & Year2 & "')" _
& "Or (" & TableField3 & "='" & Year3 & "'))" _
.CreatePivotTable TableDestination:="'[MI Tool.xls]ListBox List'!R30C1", _
TableName:="PivotTale2", DefaultVersion:=xlPivotTableVersion10
End With
Edited 10-Apr-07 by geekgirlau. Reason: insert vba tags