Problem with SELECT statement in VBSCRIPT

allanbros
allanbros used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2012
Commented:
In a test example I processed your code, and it runs fine from my end.  I get the first 3 characters from GrowerName in the query, as you specified.

However, in your Do Loop, you're trying to specify the column name against the recordset and its looking for an ordinal (integer) value, not a text string.  As you're only pulling from one column, you can just use rs(0).

Try this modified code:
Sub testQuery()
    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=" & DataSource & ";" _
                              & "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(0).Value
        rs.MoveNext

    Loop

    'Close connection
    objCon.Close
    Set objCon = Nothing
    Value = Options
End Sub

Open in new window



Are you running this from Excel?  You call it a vb script, but where will the drop down's reside?  In Excel?  I don't see the code for that part of it (populating drop downs) at this point.

If its in Excel, and you have a combobox dropdown ActiveX control, you can load the combobox quickly with these two lines of code:

    'load dropdown on worksheet from the recordset
    vArray = Application.Transpose(Application.Transpose(rs.getrows)) 'transpose to single dimensioned variant array for loading to combobox
    Sheet1.ComboBox1.List = vArray

Open in new window


See attached, example .xls with code in a public module, and a simple dataset for testing in labeldata.xlsx

Dave
testQuery.xls
labeldata.xlsx

Author

Commented:
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?
Most Valuable Expert 2012
Top Expert 2012

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial