Query To Get Excel Data Using Vb Script Follow

Legacy Poster

Hi there. I'm facing problem using vb script with SQL query to get the info i want from Excel file.

My Excel file basically has 5 fields in Ship To Lists sheet,
Name Address 1 Address 2 Address 3 Address 4

I have did a dropdown list to get the Name field.That works ok. I also gave it a share name call Place.

What i want is a query that can return the address 1 field based on what the user has selected for the dropdown list.
This is what i did for the code but i kept getting expected end of statement error in the query.

Dim strQuery
strQuery = "SELECT [Address 1] FROM [Ship To Lists$] WHERE [Name] = '& Format.NamedSubstrings("Place").Value & '

I think i have mislook something. Please help me look on this.
I didn't post my above code (connection) and below code (loop to search) here. But that's all work fine.
Any help would be greatly appreciated.


Shotaro Ito
Comment actions Permalink

At least you need to quote string by "" (double quotations) - would it work?
"SELECT [Address 1] FROM [Ship To Lists$] WHERE [Name] = '" & Format.NamedSubstrings("Place").Value & "'"

Please sign in to leave a comment.