Geek Answers Handbook

Excel / VBA: how to insert SQL query with proper row formatting

I wrote some fairly long SQL queries in notepad, and then inserted them into my VBA code as is, and then formatted the multi-line line correctly each line at a time. For instance...

In a text editor, the query is as follows.

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

Then paste this into the VBA editor and manually add sqlStr = sqlStr and "...." to each 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 a tool that will allow me to automatically wrap VBA string stuff around my request (instead of adding it manually)? I assume that there is a site there, but I can not find it.

I could fine-tune something in Vi, but I cannot guarantee that I will do it on a computer on which I will have permission to install Vi.

! .

+5
6

SQLinForm. SQL VB/VBA

+6

sqlStr = sqlStr & . :

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

, 25 .

, , , . , , . . ( RowSource )

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"

, SQL . "& _" VBA, , Ctrl-V, , Ctrl-V, .

+4

:

A1 . , A1.

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

/ B2 .

B .

sql A Excel .

, VBA B 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

, For .

+1

/ - VS.NET, TextPad, Notepad ++. : Notepad ++ Macros.

0

, Excel, VBA.

, SQL A, CONCATENATE() excel VBA. .

0

, SQL Formatter ( - sql)

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 "
0

More articles:


All Articles