Avatar of allanbros
allanbrosFlag for United States of America

asked on 

Problem with SELECT statement in VBSCRIPT

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
VB ScriptMicrosoft Excel

Avatar of undefined
Last Comment
dlmille
ASKER CERTIFIED SOLUTION
Avatar of dlmille
dlmille
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of allanbros
allanbros
Flag of United States of America image

ASKER

Thank you for the help!! This has been driving me nuts.

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?
Avatar of dlmille
dlmille
Flag of United States of America image

Yes, that is correct - in the same order as how the query is written.

Otherwise, you'd have to tap the rs.Fields(index).Name to determine which name is associated with each index.

Dave
Microsoft Excel
Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo