Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite

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.

share|improve this question

6 Answers

up vote 3 down vote accepted

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

share|improve this answer
Thanks Russ, exactly what I was looking for! – Tommy O'Dell Oct 3 '10 at 13:17
No problem, happy to help :) the pay for version is worth it if you use it a lot – Russ Cam Oct 3 '10 at 15:03
1  
Almost two years later... I've purchased a license to SQLinform. Well worth the price! Not only for outputting SQL in VBA string format, but also for auto-formatting badly formatted SQL statements. – Tommy O'Dell Jun 11 '12 at 23:52
up vote 0 down vote

here is the result generated automatically by Instant SQL Formatter(free online sql formatter)

var1 = ""
var1 = var1 & "SELECT a, " & vbCrLf
var1 = var1 & "       b, " & vbCrLf
var1 = var1 & "       c " & vbCrLf
var1 = var1 & "FROM   table1, " & vbCrLf
var1 = var1 & "       table2, " & vbCrLf
var1 = var1 & "       tablen " & vbCrLf
var1 = var1 & "WHERE  a > 1 "
share|improve this answer
up vote 2 down vote

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.

share|improve this answer
up vote 1 down vote

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.

share|improve this answer
Thanks Marc! This looks good. I'm going to use this if I'm dealing with any sensitive information that shouldn't get pasted to sqlinform mentioned below. – Tommy O'Dell Oct 3 '10 at 13:20
up vote 0 down vote

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.

share|improve this answer
up vote 0 down vote

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.