use a variable SQL column to recieve data to excel

[robert_645]

Hello,

I hope to get some help with the following issue.
I am recieving data from multiple columns from SQL.
The names of the columns are for instance "KWR1 and KWR2
So I would like to use a variable to choose the column name.
I can recieve the data, but i get an error by the underlined command. (see a part of the programm)

Can anybody help me to solve this issue?
Thanks in advance.


Private Sub OptionButton1_Click()' While enable this optionbutton, a listbox should be filled with information from SQL.

ListBox1.Clear
ListBox2.Clear
OptionButton8.Value = False

If OptionButton1.Value = True Then

Sheets("Temp").Visible = True
Sheets("Temp").Select
Range("A1:Z5000").ClearContents
Range("A1").Select

Dim jaar As String
Dim kolom As String
Dim mytel As Integer
Dim mytel2 As Integer

jaar = "dbo.QHSE_Planning_" & Sheets("Start").Range("C8") + 2012
mytel = 1
mytel2 = 1

SQL_Login.Login_SQL 'function to make a SQL connection

Do While mytel < 2 'I have 2 columns, named KWR1 and KWR2
kolom = "KWR" & mytel

rc.Open "SELECT [" & kolom & "] FROM " & jaar & " WHERE [" & kolom & "] != ''", con
rc.MoveFirst

Do
Range("A" & mytel2) = rc![" & kolom & "]
mytel2 = mytel2 + 1
rc.MoveNext
Loop Until rc.EOF
mytel = mytel + 1
rc.Close

Loop

con.Close
Set rc = Nothing
Set con = Nothing

[Bob Phillips]

How about something like



rc.Open "SELECT [" & kolom & "] FROM " & jaar & " WHERE [" & kolom & "] != ''", con
MyData = rc.GetRows
Range("A1").Resize(rc.RecordCount) = Application.Transpose(MyData)
con.Close