Excel/VBA: How to paste SQL query with proper string formatting

Question

I've been writing some pretty long SQL queries in notepad and then pasting them into my VBA code as-is and then formatting the multi-line string correctly each line at a time. For example...

In my text editor, the query looks like this.

SELECT 
      a,
      b,
      c,
      ...,
      n
FROM
      table1,
      table2,
      ...,
      tableN
WHERE
      etc

Then pasting this into the VBA editor and manually adding sqlStr = sqlStr & " .... " to every line.

sqlStr = "               SELECT "
sqlStr = sqlStr & "          a,"
sqlStr = sqlStr & "          b,"
sqlStr = sqlStr & "          c,"
sqlStr = sqlStr & "          ...,"
sqlStr = sqlStr & "          n"
sqlStr = sqlStr & "      FROM"
sqlStr = sqlStr & "          table1,"
sqlStr = sqlStr & "          table2,"
sqlStr = sqlStr & "          ...,"
sqlStr = sqlStr & "          tableN"
sqlStr = sqlStr & "      WHERE"
sqlStr = sqlStr & "          etc"

Does anyone know of a tool that will let me automatically wrap the VBA string stuff around my query (instead of adding it manually)? I imagine there's a web site somewhere for that, but I can't find it.

I could rig up something in Vi, but I can't guarantee that I'll be doing this on a computer that I'll have rights to install Vi on.

Any help appreciated! Thanks.

Answer 1

You might want to look at SQLinForm. Among other formats, it allows you to format SQL for use in VB/VBA

Answer 2

You don't need to do sqlStr = sqlStr & on every line. Just continue the one statement like this:

sqlStr = "SELECT a, b, c,..., n " & _
         "  FROM table1, table2,..., tableN " & _
         " WHERE etc"

You can have up to 25 lines on a single statement this way.

Also, I don't think you are doing yourself any favours by formatting long queries with every item on a separate line. I take a more moderate approach, trying to show a bit more structure without using too many lines. Here is a bit of code from a recent project. (It is used to set the RowSource for a combo box)

q = "SELECT NurseID, NurseName FROM " & _
    " (SELECT DISTINCT 0 as NurseID,  '-- choose nurse --' as NurseName FROM tblNurse) " & _
    "UNION " & _
    " (SELECT DISTINCT N.NurseID AS NurseID, FirstName & ' ' & LastName AS NurseName " & _
    "  FROM tblNurse AS N INNER JOIN tblBookings AS B" & _
    "  ON N.NurseID=B.NurseID " & _
    "  WHERE B.BDate >= " & Date_To_SQL(txtStartDate) & _
    "    AND B.BDate <= " & Date_To_SQL(txtEndDate) & ") " & _
    "ORDER BY NurseName"

This also demonstrates the use of aliases to make the SQL shorter and more readable. It is pretty quick to insert all the quotes and "& _" in the VBA editor, if you put them in the clipboard and use the mouse and keyboard to Click, Ctrl-V, Click, Ctrl-V buzzing down through the rows.