troubleshooting Question

Problem with SELECT statement in VBSCRIPT

Avatar of allanbros
allanbrosFlag for United States of America asked on
Microsoft ExcelVB Script
3 Comments1 Solution844 ViewsLast Modified:
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 CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros