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

Question

Hi folks,

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

Any text editor with a macro/record feature will let you automate this -- VS.NET, TextPad, Notepad++. For, the last see: Notepad++ Macros.

Answer 2

You're probably going to have Excel installed if you're doing VBA.

You could write a spreadsheet where you paste the SQL in column A, and use the CONCATENATE() excel formula function to add the VBA code. Then you can just copy and paste it into your app.

Answer 3

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

Answer 4

A quick-and-dirty solution:

Copy the text into cell A1 of a clean spreadsheet. Each line will land in a cell going down from A1.

In B1 put ="sqlString ="""&A1&""""
In B2 put ="sqlString=sqlString&"""&A2&""""

Copy/drag B2 down to the end of the column of text.

Copy and paste the resulting column B into your code.

You could also edit your sql fragments straight into column A of a blank Excel sheet instead of notepad, and save a step.

If you'd rather do it with code, this VBA will make Column B from Column A:

Option Explicit

Public Sub makeSqlStmt()
    Dim r
    Dim n
    Dim i
    Const s = "sqlString = """
    Const t = "sqlString = sqlString & """
    Set r = Range("a1")
    Range("B1") = s & r & """"
    n = r.CurrentRegion.Rows.count
    For i = 1 To n - 1
        r.Offset(i, 1) = t & r.Offset(i, 0) & """"
    Next i
End Sub

If you wanted to take it straight from the notepad file, you could replace the For loop with code to read the file.

Answer 5

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.