Using the values in range of cells in an SQL query

[steveh]

I would like to use the values of a range of cells in an SQL query, but
I'm not sure the best way to do it. Basically, the value of each cell
would be part of a string, each separated by a comma. I could build an
array to loop through each value in the range and append a comma, but is
there a simpler way?

Thank you for your time.

 

[Guest]

Demo'd from the immediate window:

v = Application.Transpose( _
Application.Transpose(range("A1").Resize(1,5)))
s = Join(v,",")
? s
AA,BB,CC,DD,EE


if the cells are in a column, you only need one Application.Transpose

 

[steveh]

Thanks, Tom. That worked great. I just had to use WorksheetFunction
instead of Application.

 

[Tom Ogilvy]

You shouldn't have to - afterall, "demo'd from the immediate window" means
the code was successfully executed. But if it works, I don't see a problem
with that.