I am trying to populate a dropdown list in a label making program. The problem is I just need the first 3 characters of the column. I am pulling from a spreadsheet. Here is the code.
I think the problem is the format of my query but in any case, I can't figure this out.
************************************************************
datasource = "C:\Users\receiving\Desktop\tag_data\labeldata.xlsx"
intitalCatalog = "labeldata.xlsx"
tableName = "Grower$"
columnName = "GrowerName"
'userId = "Admin"
'password = ""
'Create connection
Set objCon = CreateObject("ADODB.Connection")
objCon.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" _
& "Data Source=C:\Users\receiving\Desktop\labeldata.xlsx;" _
&"Extended Properties=""Excel 12.0;HDR=Yes;IMEX=2"""
objCon.Open
strCon = "SELECT LEFT(([GrowerName]),3) FROM [Grower$]"
'Run query
Set rs = CreateObject("ADODB.Recordset")
Set rs = objCon.Execute(strCon)
'Fill options
options=""
Do Until rs.EOF
If LEN(options)>0 Then options = options + vbCR
options = options + rs(columnName).value
rs.MoveNext
Loop
'Close connection
objCon.Close
Set objCon = Nothing
Value = options
*********************************************************
Please help. Thank you in advance
ASKER
I am using this label making software called BarTender. I am trying to populate a drop down for the user to print labels. The software is tough to work with.
Thank you that worked really well.
Btw- If I had more than one column would I use rs=(1,2 or 3) and so on?