SQL查询命令互转vba格式
本帖最后由 Kls673M 于 2023-10-19 17:37 编辑最近搞个Excel的vba查询数据库，发现vba有代码行长度限制需要转换下就弄了这个，布局和功能暂且这样了，哪位大佬如果有兴趣的可以再美化下！
这次更新了SQL命令互转VBA格式，
SQL原始格式要分行的不能一坨贴进去，不然转换后会出现多个连接符，能力有限不懂改了……
vba格式原始格式尽量从vba复制出来吧
我使用的2008 R2所以vba那个段仅当测试用的，如果你用其他数据库自行修改把
成品：https://wwvz.lanzoue.com/b04koop8b密码:e5d5
import tkinter as tk
from tkinter import ttk
import re
import tkinter.messagebox as messagebox
import tkinter.scrolledtext as scrolledtext
from tkinter import Tk
# 定义一个函数，用于移除文本中的换行符和回车符
def remove_newlines():
# 数据库连接信息
server_name = server_entry.get()
database_name = database_entry.get()
username = username_entry.get()
password = password_entry.get()
sql_command = text.get("1.0", "end-1c")
sql_command = re.sub(r"(.{130}(?:\s|$))", r'\1 "& _\n" ', sql_command)
sql_command = re.sub(r"\s+", " ", sql_command)
sql_command = re.sub(r"(.{130}(?:\s|$))", r'\1 "& _\n" ', sql_command)
vba_code = generate_vba_code(
server_name, database_name, username, password, sql_command
)
sql_command_text.delete(1.0, tk.END)
sql_command_text.insert(tk.END, sql_command)
vba_code_text.delete(1.0, tk.END)
vba_code_text.insert(tk.END, vba_code)
def convert_to_sql():
vba_command = text.get(1.0, tk.END)
vba_command = re.sub(r'"\s*&\s*_\s*"', '', vba_command)
sql_command_text.delete(1.0, tk.END)
sql_command_text.insert(tk.END, vba_command)
# 定义一个函数，用于生成VBA代码
def generate_vba_code(server_name, database_name, username, password, sql_command):
vba_code = f'''
Sub RunSQLQuery()
Dim conn As Object
Dim rs As Object
Dim strSQL As String
Dim xlApp As Object
Dim xlWorkbook As Object
Dim xlWorksheet As Object
' 数据库连接信息
serverName = "{server_name}"
databaseName = "{database_name}"
userName = "{username}"
password = "{password}"
' 获取输入框变量的值，这个可以删除
bFullName = ""
pFullName = ""
pUserCode = ""
pStandard = ""
Number = ""
CFDA = ""
pArea = ""
Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
' 建立数据库连接
conn.Open "Provider=SQLOLEDB;Data Source=" & serverName & ";Initial Catalog=" & databaseName & ";User ID=" & userName & ";Password=" & password & ";"
' SQL查询命令
strSQL = "{sql_command}"
' 执行查询
rs.Open strSQL, conn
' 将查询结果输出到Excel工作表
Dim i As Integer
For i = 1 To rs.Fields.Count
ActiveSheet.Cells(1, i).Value = rs.Fields(i - 1).Name
Next i
If Not rs.EOF Then
ActiveSheet.Range("A2").CopyFromRecordset rs
End If
' 清理资源
rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing
End Sub'''
return vba_code
# 定义一个函数，用于粘贴文本
def paste_text():
try:
text.delete('1.0', tk.END)
text.insert(tk.END, root.clipboard_get())
except tk.TclError:
pass
# 定义一个函数，用于复制文本到剪贴板
def copy_text():
try:
root.clipboard_clear()
root.clipboard_append(sql_command_text.get("1.0", tk.END))
messagebox.showinfo('复制成功', '已将转换后的内容复制到剪贴板')
except tk.TclError:
pass
# 主程序入口
root = tk.Tk()
notebook = ttk.Notebook(root)
notebook.pack(fill="both", expand=True)
input_frame = tk.Frame(notebook)
input_frame.pack(fill="both", expand=True)
text_frame = tk.Frame(input_frame)
text_frame.pack(side=tk.TOP, fill="both", expand=True)
text = tk.Text(text_frame, height=10, width=100)
text.pack(side=tk.LEFT, fill="both", expand=True)
text_scrollbar = ttk.Scrollbar(
text_frame, orient=tk.VERTICAL, command=text.yview
)
text_scrollbar.pack(side=tk.RIGHT, fill=tk.Y)
text.configure(yscrollcommand=text_scrollbar.set)
notebook.add(input_frame, text="输入框")
input_frame = tk.Frame(root)
input_frame.pack(side=tk.TOP)
server_label = tk.Label(input_frame, text="服务器：")
server_label.grid(row=0, column=0, sticky=tk.E, padx=5, pady=5)
server_entry = tk.Entry(input_frame)
server_entry.grid(row=0, column=1, padx=5, pady=5)
database_label = tk.Label(input_frame, text="数据库：")
database_label.grid(row=1, column=0, sticky=tk.E, padx=5, pady=5)
database_entry = tk.Entry(input_frame)
database_entry.grid(row=1, column=1, padx=5, pady=5)
username_label = tk.Label(input_frame, text="用户名：")
username_label.grid(row=2, column=0, sticky=tk.E, padx=5, pady=5)
username_entry = tk.Entry(input_frame)
username_entry.grid(row=2, column=1, padx=5, pady=5)
password_label = tk.Label(input_frame, text="密码：")
password_label.grid(row=3, column=0, sticky=tk.E, padx=5, pady=5)
password_entry = tk.Entry(input_frame)
password_entry.grid(row=3, column=1, padx=5, pady=5)
output_frame = tk.Frame(notebook)
output_frame.pack(fill="both", expand=True)
sql_command_text = scrolledtext.ScrolledText(output_frame, height=10, width=100)
sql_command_text.pack(fill="both", expand=True)
vba_code_text = scrolledtext.ScrolledText(output_frame, height=10, width=100)
vba_code_text.pack(fill="both", expand=True)
notebook.add(output_frame, text="输出框")
button_frame = tk.Frame(root)
button_frame.pack(side=tk.TOP)
paste_button = tk.Button(button_frame, text="清空粘贴", command=paste_text)
paste_button.pack(side=tk.LEFT, padx=10)
button = tk.Button(button_frame, text="转换VBA格式", command=remove_newlines)
button.pack(side=tk.LEFT, padx=10)
button = tk.Button(button_frame, text="转换SQL格式", command=convert_to_sql)
button.pack(side=tk.LEFT, padx=10)
copy_button = tk.Button(button_frame, text="复制结果", command=copy_text)
copy_button.pack(side=tk.LEFT, padx=10)
root.mainloop()
感谢分享。下载备用一下。 有没有成品？ 感谢分享{:1_893:} 可以反过来VBA格式转SQL吗? liunx0001 发表于 2023-10-6 13:18
有没有成品？
暂时没有，因为不是很完美，而且内置有数据库的连接信息就不方便放了 Kristine_He 发表于 2023-10-6 14:03
可以反过来VBA格式转SQL吗?
{:1_904:}
这是个好功能，下次可以搞下这个！ 谢谢分享，收藏备用。 还不错，多谢分享！ Kls673M 发表于 2023-10-6 14:08
这是个好功能，下次可以搞下这个！
你要是搞了请踢我一脚，马上来围观