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.

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

--
Regards,
Tom Ogilvy


"steveh" wrote:

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.

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


Tom Ogilvy wrote:
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

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.

--
Regards,
Tom Ogilvy


"steveh" wrote in message
...
Thanks, Tom. That worked great. I just had to use WorksheetFunction
instead of Application.


Tom Ogilvy wrote:
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