Populating a LISTBOX based on sheet name

[anusha]

Hello,

I am new to this forum and a novice at VBA programming.

I created my first app this week and have a few questions.

1. I created a list box which needs to be populated based on another listbox selection value.

For eg.

If the fist list box selection is "SUBS" from the listbox, then listbox2 should be populated with data from column A in a sheet named "SUBS"

If the fist list box selection is "BHCK" from the listbox, then listbox2 should be populated with data from column A in a sheet named "BHCK"

How do I do this?

Thanks in advance for any help.

~Anusha

[Killian]

Hi Anusha and welcome to VBAX :hi:

in each case, first clear the listbox, then loop though the values you need and add them with the AddItem method
For the first list box, you loop through each worksheet name in the worksheets collection
For the second, you use the selected item in listbox 1 for the worksheet name and loop through each cell in the range on that sheetPrivate Sub UserForm_Initialize()
'Load the first listbox with worksheet names
Dim ws As Worksheet

ListBox1.Clear
For Each ws In ActiveWorkbook.Sheets
ListBox1.AddItem ws.Name
Next

End Sub

Private Sub ListBox1_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, _
ByVal X As Single, ByVal Y As Single)
'load the second listbox with value in defined range
'using Range("A1:A10") for this example
Dim c As Range

ListBox2.Clear
'refer to the sheet name by using the selected value in listbox1
For Each c In ActiveWorkbook.Sheets(ListBox1.Value).Range("A1:A10")
ListBox2.AddItem c.Value
Next

End SubI've assumed these are on a user form. If they're worksheet objects, you'll need to use a different event to load the first listbox (like worksheet_acitvate)

Hope that helps

[anusha]

Where do I put this code? Can I send you the file? How do I do I send it?

Thanks in advance for your help.

[Killian]

I've attached an example.
If you need to post back an attachment, click the "Go Advanced" button below the Quick Reply text window, scroll down to the Additional Options section and select Manage Attachments. (If you're sending a workbook, you'll need to compress it to a ZIP file first)

[anusha]

Wow ... Thanks. Now I have more questions..

1. I included my workbook.

2. In the Analysis workbook: There is a combobox in D1 and a listbox in E1

when I make a selction from D1 it should loop through the sheets t the bottom and find the name just like you did. So, I won't have a load data button. It should do the clear and load the data to E1 when I make a combobox selction in D1. So do I put your code in in the D1 VBA code?

3. Also, how can I get I4 to reflect the selection in the E1 selection box?

Thanks for all your help.

~Anusha

[Killian]

OK, have a look at the code behind teh ANALYSIS sheet... I've made the changes I think you need and added some comments. Hopefully it's clear what's going on.
I use the sheet's activate event to populate the combo box
The combo box's change event to populate the list box
The list box's click event to update cell I4

If you want to keep the code this simple (not a bad idea) you need to make sure that each sheet you add to the combobox has a range of the same name you want to go into the listbox

[anusha]

Awesome !!! Thank you so much ofr helpng me through this project.

~Anusha

[MOS MASTER]

Hi and Welcome to VBAX Anusha! :hi:

Don't forget to mark your thread solved if it has been. :*)

[anusha]

K,

The one problem I see is the get data button now does not run macro2 and Maco two should heve the information from the listbox and combbox in order to run...

Sorry I didn't mention this earlier...

What do you recommend?

[anusha]

K, I have attached the file adding macro2 to the get data botton VBA code. But I guess the macro does not know where to get the information for the E1 selection VarItem. I assume it know VarSeries. Are these definitions in the macro code still good?

Thanks for your continued help.

~Anusha

[Killian]

Ahh... I think I see what you mean.

Well the problem is that we need to make sure that listbox1 and combobox1 are always initialized.
To do this, I've addedListbox1.ListIndex = 0to the combobox change event code to make sure each time it's re-populated, the first item is selected.
Also, when the work book os opened, we need to make sure the ANALYSIS sheet's activate code is fired to populate the combo box, so I've added some code in the workbook_open event to activate sheet 1, then sheet "ANALYSIS", which should do the trick.
I didn't look too closely at what marco2 does... Does that fix it?

[anusha]

K,

The get button now works. But the macro behind it has a SQL error. I assume it the variables need to be adjusted to pull the information correctly.. Here's the macvro code behind the get data button.Sub Macro2()

Dim varDate As String
varDate1 = Range("g2").Value

Dim varDate2 As String
varDate2 = Range("h2").Value

Dim varDate3 As String
varDate3 = Range("i2").Value

Dim varItem As String
varItem = Worksheets("ANALYSIS").ListBox1.Text

Dim varSeries As String
varSeries = Worksheets("ANALYSIS").ComboBox1.Text
If dtl = True Then
Worksheets("ANALYSIS").Range("I4") = Worksheets("ANALYSIS").ComboBox1.Value
End If

Dim varGroup As String
varGroup = Range("f2").Value
'
' Macro2 Macro
' Macro recorded 9/9/2005 by M1AFD00
'
'
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=M1DB2P;UID=m1afd00;;MODE=SHARE;DBALIAS=M1DB2P;", Destination:=Range _
("A10"))
.CommandText = Array( _
"select " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "c.nm_lgl," & Chr(13) & "" & Chr(10) & "c.auth_frd_Updt ," & Chr(13) & "" & Chr(10) & "a.id_rssd, " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "E." & varItem & "," & Chr(13) & "" & Chr(10) & "B." & varItem & ", " & Chr(13) & "" & Chr(10) & "A." & varItem & "" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "from " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "fdrP.cuv_" & varSeries & "01 a, " & Chr(13) & "" & Chr(10) & "fdrP.cuv_" & varSeries & "01 b, " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "fdrP.cuv_" & varSeries & "01 E," & Chr(13) & "" & Chr(10) & "nicua.cuv_attr_mr c" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "where" _
, _
"" & Chr(13) & "" & Chr(10) & " A.dt = " & varDate1 & " " & Chr(13) & "" & Chr(10) & "AND A." & varGroup & " " & Chr(13) & "" & Chr(10) & "AND B.DT=" & varDate2 & " " & Chr(13) & "" & Chr(10) & "AND A.ID_RSSD = B.ID_RSSD " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "AND E.DT=" & varDate3 & " " & Chr(13) & "" & Chr(10) & "AND B.ID_RSSD = E.ID_RSSD " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "AND A.id_rssd = c.id_rssd and c.dt_end = 99991231 " _
)
.Name = "Query from M1DB2P"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End SubThanks again ...

[Killian]

OK, well I'm not going to be able to debug your SQL query since I don't have access to your data, but whats happening here is that the .commandtext property of your query is being built up to include the variables - as long something is selected in the combo and list box, you should have values there. I just tested it and it seems fine.

I'm, a bit confused as to why the command text is an array - I would expect it to be a simple string. I've tidied it up a little and added it in as a string variable so you can see a bit more clearly how it's working. Try this...Sub Macro2()

Dim varDate As String
varDate1 = Range("g2").Value
Dim varDate2 As String
varDate2 = Range("h2").Value
Dim varDate3 As String
varDate3 = Range("i2").Value
Dim varItem As String
varItem = Worksheets("ANALYSIS").ListBox1.Text
Dim varSeries As String
varSeries = Worksheets("ANALYSIS").ComboBox1.Text
Dim varGroup As String
varGroup = Range("f2").Value

'initialize the SQL string to pass to the query
Dim strSQL As String
strSQL = "SELECT " & "c.nm_lgl," & Chr(13) & _
"c.auth_frd_Updt," & Chr(13) & _
"a.id_rssd," & Chr(13) & _
"E." & varItem & "," & Chr(13) & _
"B." & varItem & "," & Chr(13) & _
"A." & varItem & Chr(13) & _
"FROM " & Chr(13) & _
"fdrP.cuv_" & varSeries & "01 a, " & Chr(13) & _
"fdrP.cuv_" & varSeries & "01 b, " & Chr(13) & _
"fdrP.cuv_" & varSeries & "01 E," & Chr(13) & _
"nicua.cuv_attr_mr c" & Chr(13) & _
"WHERE" & Chr(13) & _
"A.dt = " & varDate1 & Chr(13) & _
"AND A." & varGroup & Chr(13) & _
"AND B.DT=" & varDate2 & Chr(13) & _
"AND A.ID_RSSD = B.ID_RSSD" & Chr(13) & _
"AND E.DT=" & varDate3 & " " & Chr(13) & _
"AND B.ID_RSSD = E.ID_RSSD " & Chr(13) & _
"AND A.id_rssd = c.id_rssd and c.dt_end = 99991231"

With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=M1DB2P;UID=m1afd00;;MODE=SHARE;DBALIAS=M1DB2P;", _
Destination:=Range("A10"))
.CommandType = xlCmdSql
.CommandText = strSQL
.Name = "Query from M1DB2P"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

[anusha]

WooHoooooooo !!!!

Thank you soooooo much. It is working. But give me a day or so to test it in case I have more questions.

Thanks again...

I am absolutely delighted.

~Anusha

[anusha]

One very last ... Why does the combo box keep shrinking whenever I open it? I have to readjust the size every time?

It currently looks the way it should in the jpg.

[Killian]

Cool, glad it's working out for you :yes

I noticed that listbox resizing thing - very strange... I don't do too much stuff with worksheet controls, so I may be missing something obvious but you can fix it with a quick addition to the codePrivate Sub ComboBox1_Change()
Dim c As Range

With ListBox1
If ComboBox1.Value <> "" Then
.Clear
For Each c In ActiveWorkbook.Sheets(ComboBox1.Value).Range(ComboBox1.Value)
.AddItem c.Value
Next c
.ListIndex = 0
.Width = 80 '<--that should do it
End If
End With
End Sub

[anusha]

K,

Sorry to ask for help again ... I am battling with how to send the selection from the listbox to the query. It was easy when all the items were from the same table. Now, the table name could be anything.

This means I will have to read off clumn B in the sheets such as "BHCF" and when this sheeet is slected and an item from it, I need to also now figure out how to read the next coulmn B to pull the table name, and send it to Macro2.

SELECT
c.nm_lgl,
c.auth_frd_Updt,
a.id_rssd,
a."&VarItem&" ---> to get this BHCK2170

FROM
fdrP.cuv_"&series&""&tablenum&" a, ---> to get this BHCF01 a,
nicua.cuv_attr_mr c

WHERE
A.dt in ("&Vardate1&", "&Vardate2&", "&Vardate3&") --> to get this(20040930, 20040630, 20040331)

AND A.ID_RSSD = C.ID_RSSD
AND A.id_rssd = c.id_rssd
and c.dt_end = 99991231

How do I do this?

Thanks in advance.

~Anusha

[Killian]

Hi again...
No need to apologise for asking for help - it's pretty much what we do here :yes

So I think I understand what you wanted...
I've added another column to the list box (you'll see you can scroll it right now) and the combo box change code adds the table value one to the right of the original value) so we can get to it in Macro2.
In Macro2 I've added a another variable that initializes from that column in the listbox and I've had a play around with the SQL string building code to reflect your changes (I think). :think:

If you need to fine-tune it, I've put just the SQL string building code in Module1. This uses Debug.Print to output the string to the Immediate window (ctrl G in the VBE) so you can just run that to check what it's doing.

[anusha]

Thanks for your time. But the macro does not run. I am getting a runtime error. I also modified the query and took off a.id_rssd = a.id_rssd

and the Icing on the cake would be to pull the info in column c of the selected sheet for series and display the item descrition in I5.

So what am mI doing wrong with the macro?

Sub Macro2()
Dim varDate As String
varDate1 = Range("g2").Value
Dim varDate2 As String
varDate2 = Range("h2").Value
Dim varDate3 As String
varDate3 = Range("i2").Value
Dim varItem As String
varItem = Worksheets("ANALYSIS").ListBox1.Text
Dim varTable As String
varTable = Worksheets("ANALYSIS").ListBox1.List(Worksheets("ANALYSIS").ListBox1.ListIndex, 1)
Dim varSeries As String
varSeries = Worksheets("ANALYSIS").ComboBox1.Text
Dim varGroup As String
varGroup = Range("f2").Value

'initialize the SQL string to pass to the query
Dim strSQL As String
strSQL = "SELECT " & vbLf & _
"c.nm_lgl," & vbLf & _
"c.auth_frd_Updt," & vbLf & _
"a.id_rssd," & vbLf & _
varItem & vbLf & _
"FROM " & vbLf & _
"fdrP.cuv_" & varTable & " a" & vbLf & _
"nicua.cuv_attr_mr c" & vbLf & _
"WHERE" & vbLf & _
"A.DT IN (" & varDate1 & ", " & varDate2 & ", " & varDate3 & ")" & vbLf & _
"AND A.id_rssd = c.id_rssd" & vbLf & _
"AND C.dt_end = 99991231"

Debug.Print strSQL

With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=M1DB2P;UID=m1afd00;;MODE=SHARE;DBALIAS=M1DB2P;", _
Destination:=Range("A10"))
.CommandType = xlCmdSql
.CommandText = strSQL
.Name = "Query from M1DB2P"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub


+++++++++++++++++++ I MESSED UP THE QUERY +++++++++++

This is the query that is correct. And I have to incorporate the VarTable...
I'll give it a shot with you.

With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=M1DB2P;UID=m1afd00;;MODE=SHARE;DBALIAS=M1DB2P;", Destination:=Range _
("A10"))
.CommandText = Array( _
"select " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "c.nm_lgl," & Chr(13) & "" & Chr(10) & "c.auth_frd_Updt ," & Chr(13) & "" & Chr(10) & "a.id_rssd, " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "E." & varItem & "," & Chr(13) & "" & Chr(10) & "B." & varItem & ", " & Chr(13) & "" & Chr(10) & "A." & varItem & "" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "from " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "fdrP.cuv_" & varSeries & "01 a, " & Chr(13) & "" & Chr(10) & "fdrP.cuv_" & varSeries & "01 b, " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "fdrP.cuv_" & varSeries & "01 E," & Chr(13) & "" & Chr(10) & "nicua.cuv_attr_mr c" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "where" _
, _
"" & Chr(13) & "" & Chr(10) & " A.dt = " & varDate1 & " " & Chr(13) & "" & Chr(10) & "AND A." & varGroup & " " & Chr(13) & "" & Chr(10) & "AND B.DT=" & varDate2 & " " & Chr(13) & "" & Chr(10) & "AND A.ID_RSSD = B.ID_RSSD " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "AND E.DT=" & varDate3 & " " & Chr(13) & "" & Chr(10) & "AND B.ID_RSSD = E.ID_RSSD " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "AND A.id_rssd = c.id_rssd and c.dt_end = 99991231 " _
)

[anusha]

K,

Here's how I hard coded contents of I5 prior to going VBA.

=LOOKUP(ANALYSIS!I4,BHCK!A:A,BHCK!C:C)

How do I incorporate this into the VBA macro?

~Anusha :cloud9:

[Killian]

OK, I've made some changes...
The second column in the listbox now contains the row number for that item and I use that to set the table name variable and the description for cell I5

I re-hashed the SQL string - it's seems OK to me but you'll have to try it with the database and see how it goes

[anusha]

VarTable --> Was working before

Dim varTable As String
varTable = Worksheets("ANALYSIS").ListBox1.List(Worksheets("ANALYSIS").ListBox1.ListIndex, 1)



Dim varTable As String ---> The new one does not

varTable = Worksheets(varSeries).Cells(Worksheets("ANALYSIS").ListBox1.List(Worksheets("ANALYSIS").ListBox1.ListIndex, 1), 2)

Maybe we should leave that alone and create a new VarDesc???

What do you think?

[Killian]

I think maybe you're not using the workbook I sent back and that the one you've put the code in doesn't have a two column list box..?

If that's the case, Change the ColumnCount property of the list box to 2 and change the ComboBox change event code to thisPrivate Sub ComboBox1_Change()
Dim c As Range

With ListBox1
If ComboBox1.Value <> "" Then
.Clear

For Each c In ActiveWorkbook.Sheets(ComboBox1.Value).Range(ComboBox1.Value)
.AddItem c.Value
'add the items row number to coloumn 2 of the listbox
.List(.ListCount - 1, 1) = c.Offset(0, 1).Row
Next c
.ListIndex = 0
.Width = 80
End If
End With

[anusha]

We are very close ... Here's the file...

[Killian]

Close? you mean we're not there? After all we've been through!!! :rofl:

Well I put the code in the workbook open event to kick everything off and stripped all the extra spaces out of the descriptions so the align right properly.
I suppose I can't test the data, but everything else seems ok :whistle:

[anusha]

K, I owe you big time... I didn't see the attachment?

[anusha]

What I have so far, is working. Only the question that remains is indicated below. Attatched is the most recent. Thanks sooooo much.:cloud9:

+++++++++++++++++

And if your ready for the final question ...

How do I make VarGroup an option?

where
x = y
and c > 20000 <----- if this is VarGroup and I did not want to run this part?

How would I do it?

The way it is coded now, if I don't set VarGroup the query won't run.

[Killian]

oops, yes and I desrcibed the changes I made but didn't post it back... here 'tis

Now, with this VarGroup situation... I don't really understand what you're asking
I see it in the SQL string you're using, and in the code at the moment, it's set to cell F2, which is bhck2170 > 1000000
It looks like you want to take the listbox value and test it against something (???) but x, y and c are all a mystery to me.
If you describe the behaviour you want and what the parameters are, we can produce the code to make it happen :yes

[anusha]

K,

Currently the query is setup to force a that part of the where statement. But there will be situations where that part of the where statement will not be needed. In such a case how do we make &varGroup an optional element of the VBA program?

For e.g where a.id = b.id and bhck2170 > 100000. This part after the and needs to be flexible and it ill be typed in the cell F2. Currently F2 cannot be left blank.

How do we proceed?

[Killian]

OK , I think I get it... :think:
In bhck2170 > 100000, I'm assuming the bhck2170 part is what's selected in the list box
And 100000 is the value typed into F2
So:'replace the current assingment of varGroup
Dim varGroup As String
varGroup = Range("f2").Value

'with this
Dim varGroup As String
varGroup = varItem & " > " & Range("f2").ValueBut questions remain:
what are the conditions that determine if it is included?
Which part of the where statment is left out if the conditions are not met?

For example, If you want to always have "a.id = b.id", but only want to include " AND listitem > F2 value" if the F2 value is greater than 50000, you would use this to assign varGroup'a.id = b.id would always in the SQL string
'you just want to determine whether to add the AND ....
Dim varGroup As String
If Range("F2").Value > 50000 Then
varGroup = "AND " & varItem & " > " & Range("F2").Value
End If

[anusha]

K,

Yes we always want a.id_rssd = b.id_rssd.

And sometimes we may want to also say and bhck2170 between 1000 and 5000...

And so... I think this code below sounds right...

For e.g. Here's the whole string they would type in F2.

bhck2170 between 1000000 and 5000000

So what I need is a Vargroup variable, that will be in the SQL part,
but will only resolve sometimes, if something is in F2.

Is that clearer?

And then the really big question... I will state it for time difference purposes...

If I have a another workbook call "top50" and in it a list of Id's in column A, how can I link to that file, read the column, and bring back a comma delimited list to populate the the SQL part in Macro 2 to say...

and id_rssd not in (1234, 5678, 91011)

Is this possible? How hard is it to do?

Thanks.

[Killian]

Dim varGroup As String
If Range("F2").Value <> "" Then
varGroup = "AND " & Range("F2").Value
End If So this will be fine... if the F2 is empty then the varGroup variable will be empty, other wise it will add the "AND " plus what ever is in F2

The problem you have is you are then relying on the user to enter the correct SQL syntax (unlikely!) or you need to find a way to either control the input (the best option) or parse it into SQL (difficult if the user enters something unexpected (likely!)

If there are only certain specific options, then perhaps you could use validation to pick from a list you store somewhere in the workbook.

Regarding the second point... anything's possible!
One option would be, at the start of your macro2 code where you initialize all the other variables for building the SQL string, open the other workbook, read the range into a string and initialize another variable form there.
Or maybe if this Top50 workbook is something thats quite large, it might be better to add some code to that saves the delimited list out as a text file when it's updated. Then you can read you variable value in from the text file.

[anusha]

K,

For now I have 50 id's in a sheet named "top50" in the same workbook.

So how would I read column A into a variable and use it sometimes as in the previous case. These are all parts of the where which we will use sometimes.

What does the code for that look like and where do I put it?

I sooooo in experienced. And the larger task will be to document what I have learned from you.

Thanks in advance.

[anusha]

I added this code but now there is a ODBC error...

Dim varGroup As String

If Range("F2").Value <> "" Then

varGroup = "AND " & Range("F2").Value

End If

[Killian]

Your SQL string code currently produces:
AND A.AND bhck2170 between 1000000 and 5000000
for the varGroup part - the whole AND statement needs to be optional so I think you need to make the following changes'in declaring the variable
If Range("F2").Value <> "" Then
varGroup = "AND A." & Range("F2").Value
End If

'in the SQL string code, instead of
"AND A." & varGroup & " " & Chr(13) & _
'you now need
varGroup & " " & Chr(13) & _
To get your top50, you pretty much had the code (commented out) already, I've just declared the variable, c, as a range and put the target range in directly. The final line strips the last comma off the end by taking the Left characters of the string not including the last one, Len(VarTop50)-1)Dim c As Range
Dim varTop50 As String
varTop50 = ""
'rng = Range("Top50!A1:A50")
For Each c In Range("Top50!A1:A50")
varTop50 = varTop50 + c.Value
Next c
varTop50 = Left(varTop50, Len(varTop50) - 1)Now, I guess you'll need to wrap that in an If... Then clause, in exactly the same way you did for varGroup and then add the additional part to the SQL string code, again, just as for varGroup.

I noticed you had all the commas in the worksheet for top50. If you're going to take theses from actual values in the future, you'll need to build those commas with the code loopFor Each c In Range("Top50!A1:A50")
varTop50 = varTop50 & c.Value & ","
Next c

[anusha]

K,

This works. Now how can I make it optional? Do I need a check box that says with top50? And where can I see the actual SQL code tht os being run in the background?

Very soon we will have to get this party started....:yes

~Anusha

[anusha]

K,

Why is &varTop50 not resolving? I am getting a ODBC error. I hard coded an id and it works but not with the variable.

"AND A.ID_RSSD NOT IN(&varTop50) " & Chr(13) & _

=================================

Dim varDate As String
varDate1 = Range("g2").Value

Dim varDate2 As String
varDate2 = Range("h2").Value

Dim varDate3 As String
varDate3 = Range("i2").Value

Dim varSeries As String
varSeries = Worksheets("ANALYSIS").ComboBox1.Text

Dim varItem As String
varItem = Worksheets("ANALYSIS").ListBox1.Text

Dim varTable As String
varTable = Worksheets(varSeries).Cells(Worksheets("ANALYSIS").ListBox1.List(Worksheets("ANALYSIS").ListBox1.ListIndex, 1), 2)

If Range("F2").Value <> "" Then
varGroup = "AND A." & Range("F2").Value
End If

Dim c As Range
Dim varTop50 As String
varTop50 = ""
'rng = Range("Top50!A1:A50")
For Each c In Range("Top50!A1:A50")
varTop50 = varTop50 & c.Value & ","
Next c

'initialize the SQL string to pass to the query
Dim strSQL As String
strSQL = "select " & Chr(13) & _
"c.nm_lgl," & Chr(13) & _
"c.auth_frd_Updt," & Chr(13) & _
"a.id_rssd, " & Chr(13) & _
"E." & varItem & "," & Chr(13) & _
"B." & varItem & ", " & Chr(13) & _
"A." & varItem & "" & Chr(13) & _
"from " & Chr(13) & _
"fdrP.cuv_" & varTable & " a," & Chr(13) & _
"fdrP.cuv_" & varTable & " b," & Chr(13) & _
"fdrP.cuv_" & varTable & " E," & Chr(13) & _
"nicua.cuv_attr_mr c" & Chr(13) & _
"where" & Chr(13) & _
"A.dt = " & varDate1 & " " & Chr(13) & _
varGroup & " " & Chr(13) & _
"AND A.ID_RSSD NOT IN(1126868) " & Chr(13) & _ <----------------
"AND B.DT = " & varDate2 & " " & Chr(13) & _
"AND A.ID_RSSD = B.ID_RSSD " & Chr(13) & _
"AND E.DT = " & varDate3 & " " & Chr(13) & _
"AND B.ID_RSSD = E.ID_RSSD " & Chr(13) & _
"AND A.id_rssd = c.id_rssd " & Chr(13) & _
"AND C.dt_end =(SELECT MAX(BB.DT_END) FROM NICUA.CUV_ATTR_MR as BB WHERE BB.ID_RSSD = A.ID_RSSD)"

[anusha]

New changes today..

Use a new Excel workbook
Store up to six periods of Top-50 institutions
Separate Top-50 data by reporting periods (i.e. six worksheets)
Name each worksheets based on reporting date (i.e. 20050630, 20050331, 20041231, etc.)
Each worksheet will have the following fields: Districts, ID_RSSDs, and Institution namesPlease name this workbook "ADA Top-50."


I have to figure out how to read an external workbook, and for every date in G2, H2 and I2, I have to find the matching sheet in ADA Top-50, read down the columns and create three new comma delimited variable lists...

Gosh.... how do I do this? HELP !!!!!!! :banghead:

[Killian]

Hi Anusha,
I was away for the weekend... so time to catch up
Post #36: Yes , a check box would be a good wat, I guess. Then wrap the varTop50 code in an If... then statementDim c As Range
Dim varTop50 As String
varTop50 = ""
If Sheets("ANALYSIS").CheckBox1 Then
For Each c In Range("Top50!A1:A50")
varTop50 = varTop50 & c.Value & ","
Next c
varTop50 = Left(varTop50, Len(varTop50) - 1)
End IfTo see the SQL srting that you're getting, add the line Debug.Print strSQL after you've initialized it and open the immediate window (Ctrl G)
Post #37: I think your error is because you haven't added the line of code that strips off the last comma when you initialize varTop50For Each c In Range("Top50!A1:A50")
varTop50 = varTop50 & c.Value & ","
Next c
varTop50 = Left(varTop50, Len(varTop50) - 1)
Post 38: Have another look at the last paragraph of post 32. If you're going to compile the top50 workbook using some code (it looks like you might) and it is quite large, then I think it would be worth outputting the comma delimited data as text files at that point, then they can be quickly read into this workbook. Alternatively, you can just open it an read it direct.
Let me know what you decide and post an example of the top50 so I can see how it fits together
Don't worry, it should be pretty straight-forward :whistle:

[anusha]

Hello K, I was getting stressed over this new requirement... so here's what we want..

1. First book - Final2.xls - sheet -> Analysis G1, H1, I1 have dates like
20041231 20040930 20040630

2. Second book - ada top-50 - Sheet --> same as G1, H1, and I1 and more

3. Idea. Read the first book G1 h1 and I1 , find the corresponding sheets in ada top-50, read column B from each of them, create three comma delimited list variables for use in Final2 in a macro that runs a SQL query ... Select * from where a.id not in(list1) and b.id not in (list2) and c.id not in (list3)

Does this help?

I will look through your comments gain and see what I am doing wrong. But for now here are the files...

~Anusha

[mvidas]

Per anusha, I have removed all attachments except Killian's original example. I have also removed the jpg per anusha as well.

If you need anything else changed to the thread, just reply here and either Killian or myself can take care of it!

Matt

[lcpx]

Hi, Can I have a look at the attachments please, I am doing a similar project now. Many thanks!

[Killian]

Hi lcpx

I believe the attachments were removed because they held some company data - in such cases its better to be safe and not have them out in public.
The basics of the code for populating a listbox based on sheet name are in the earlier posts - feel free to start a new thread if you need any clarification

[anusha]

Hello, Happy to help anyway I can.... Tell me in detrail waht your doing and I can help.

~Anusha