VBA (Excel) String difficulties RRS feed

  • Question

  • Hello,

    I am trying to write a macro which will dynamically create an SQL statement such as

    SELECT COL1 FROM TABLE1 WHERE COL2 IN ('val1','val2','valn');

    I have got the data pulled out of the spreadsheet and stored in a collection but the only way this will work for me is to turn the query into many lines of SELECT COL1 FROM TABLE1 WHERE COL2 ='valn' which doesn't work in SQLTOOLS as each line replaces the result of the last executed SELECT statement; hence why I am trying to dynamically create a query using the IN operator.

    My question is how to iterate through a for loop and append each item onto the end of a string such that I can wrap beginning and end parts of the query such that it takes the form of the code block above.

    I know in other programming languages you have the += which allows you to do this.

    Can anyone suggest what I need to achieve this?  --I will post my code so far with substituted column names if it is needed.

    Sunday, October 5, 2014 10:36 PM

All replies

  • I can not entirely follow what you are trying to accomplish.

    You want to query collection items and assign them somewhere else?

    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Please vote an answer helpful if they helped. Please mark an answer(s) as an answer when your question is being answered.

    Sunday, October 5, 2014 11:51 PM
  • Sub generate_sql() Dim sqlQueryOpen As String Dim sqlQueryClose As String Dim devices As Range Dim deviceCollection As New Collection Dim SQLText As String Set devices = Range("a1:a100") 'For checking contents of devices range 'For Each strval In devices 'Debug.Print strval 'Next 'Decompose the overall SQL Query into 'open' and 'close' strings and then dynamically add the devices inbetween sqlQueryOpen = "SELECT GRAPHICKEY FROM table1 WHERE column1 IN (" sqlQueryClose = ");" For i = 1 To WorksheetFunction.CountA(Columns(1)) deviceCollection.Add (devices(i))

    Next End Sub

    I want to include each deviceCollection element into the IN ('element1','element2','element3'......)

    such that they have apostrophes and comma delimitation. 

    Monday, October 6, 2014 9:53 AM