adding a selected range into SELECT statement using VBA 
Author Message
 adding a selected range into SELECT statement using VBA

Is there a way that we can add a range of values that a user has selected in
Excel and add those values into a SELECT statement via VBA?
For example, in the SELECT statement below:
SELECT * FROM usertable WHERE firstname in
('alvin','bernard','chris','dennis')

The values alvin, bernard, chris, dennis is what the user has selected in
cell a1 to a4 and these values are automatically inserted into the SELECT
statement. On the other hand, if cell a1 to a3 is selected by the user, then
only the values alvin, bernard, chris is inserted into the SELECT statement
to become:
SELECT * FROM usertable WHERE firstname in ('alvin','bernard','chris')
Can this be done in Excel using VBA ?



Wed, 18 Jun 1902 08:00:00 GMT  
 adding a selected range into SELECT statement using VBA
yes, if I understand you, you're just manipulating strings here.

DIM sSQL as String
DIM Cell as Range

sSQL = "SELECT * FROM usertable WHERE firstname in ("
For each Cell in Selection
sSQL= sSQL & "'" & Cell & "',"   'adds single quotes & comma
Next
sSQL = left(sSQL,LEN(sSQL)-1) ' remove the last comma
sSQL=sSQL & ")" ' add the last closing bracket

HTH

--
Patrick
Microsoft Excel MVP
________________


Quote:
> Is there a way that we can add a range of values that a user has selected
in
> Excel and add those values into a SELECT statement via VBA?
> For example, in the SELECT statement below:
> SELECT * FROM usertable WHERE firstname in
> ('alvin','bernard','chris','dennis')

> The values alvin, bernard, chris, dennis is what the user has selected in
> cell a1 to a4 and these values are automatically inserted into the SELECT
> statement. On the other hand, if cell a1 to a3 is selected by the user,
then
> only the values alvin, bernard, chris is inserted into the SELECT
statement
> to become:
> SELECT * FROM usertable WHERE firstname in ('alvin','bernard','chris')
> Can this be done in Excel using VBA ?



Wed, 18 Jun 1902 08:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. How do I select a constant time range from a variable time range using VBA

2. Problem selecting a cell using Range.Select

3. Select statements in Excel VBA macro: Using Sum

4. using the results of a SQL SELECT(COUNT) statement in VBA

5. Using VBA to select a highlighted range and paste values to another workbook

6. help selecting a range of rows using VBA

7. Using VBA to select a relative range of unknown size

8. Using VBA to select a relative range

9. Selecting Ranges using VBA

10. VBA Range().Select expresssions using variables


 
Powered by phpBB® Forum Software © phpBB Group