Excel/VBA：如何使用正确的字符串格式粘贴 SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3843586/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel/VBA: How to paste SQL query with proper string formatting
提问by Tommy O'Dell
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.
任何帮助表示赞赏！谢谢。
回答by Russ Cam
回答by Tom Robinson
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)
此外，我不认为您通过在单独的行上对每个项目格式化长查询对您有任何好处。我采取了一种更温和的方法，试图在不使用太多线条的情况下展示更多的结构。这是最近项目中的一些代码。（用于设置组合框的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"
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 在行中嗡嗡作响。
回答by James Wang
here is the result generated automatically by Instant SQL Formatter(free online sql formatter)
这是Instant 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 "
回答by Marc Thibault
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 循环来读取文件。
回答by ars
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++。对于，最后看到：Notepad++ Macros。
回答by Gus
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 代码。然后您可以将其复制并粘贴到您的应用程序中。
