Excel/VBA:如何使用正确的字符串格式粘贴SQL查询

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...

我在记事本中编写了一些相当长的SQL查询，然后将它们粘贴到我的VBA代码中，然后一次正确地格式化多行字符串。例如……

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.

然后粘贴到VBA编辑和手动添加sqlStr = sqlStr & " ....每一行。

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.

有谁知道有什么工具可以让我自动将VBA字符串填充到我的查询中(而不是手工添加)?我想应该有个网站，但是我找不到。

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.

我可以在Vi中设置一些东西，但是我不能保证我将在我有权利安装Vi的电脑上做这个。

Any help appreciated! Thanks.

任何帮助表示赞赏!谢谢。

6 个解决方案

#1

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

你可以看看SQLinForm。在其他格式中，它允许您格式化SQL以便在VB/VBA中使用

#2

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

您不需要对每一行执行sqlStr = sqlStr &。继续下面这句话:

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.

这样，一条语句最多可以有25行。

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.

这还演示了使用别名使SQL更短、更可读。在VBA编辑器中插入所有的引号和“& _”是非常快速的，如果您将它们放在剪贴板中，并使用鼠标和键盘单击、Ctrl-V、单击、Ctrl-V嗡嗡地通过行。

#3

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.

将文本复制到干净电子表格的单元格A1中。每条线都会落在一个从A1向下的单元格中。

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

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

复制/拖动B2到文本列的末尾。

Copy and paste the resulting column B into your code.

复制并粘贴生成的B列到代码中。

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

您还可以直接将sql片段编辑为空白Excel表的A列，而不是记事本，并保存一个步骤。

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

如果你更愿意用代码来做，这个VBA将从A列做B列:

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.

如果您想直接从记事本文件中获取它，可以用代码替换For循环来读取文件。

#4

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

任何带有宏/记录功能的文本编辑器都可以让您自动操作——VS.NET、TextPad、Notepad++。For，最后看到:Notepad++宏。

#5

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

如果你在做VBA，你可能会安装Excel。

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.

您可以编写一个电子表格，在其中将SQL粘贴到a列，并使用CONCATENATE() excel公式函数添加VBA代码。然后你可以复制粘贴到你的应用中。

#6

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

这是即时SQL格式化程序自动生成的结果(免费在线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 "

#1