产品展示栏目在网站中的作用,新手建站教程视频,php网站开发入门到精通教程,网络营销产品概念最近搞个Excel的vba查询数据库#xff0c;发现vba有代码行长度限制需要转换下就弄了这个#xff0c;布局和功能暂且这样了#xff0c;哪位大佬如果有兴趣的可以再美化下#xff01; 这次更新了SQL命令互转VBA格式#xff0c; SQL原始格式要分行的不能一坨贴进去#xff0…最近搞个Excel的vba查询数据库发现vba有代码行长度限制需要转换下就弄了这个布局和功能暂且这样了哪位大佬如果有兴趣的可以再美化下 这次更新了SQL命令互转VBA格式 SQL原始格式要分行的不能一坨贴进去不然转换后会出现多个连接符能力有限不懂改了…… vba格式原始格式尽量从vba复制出来吧 我使用的2008 R2所以vba那个段仅当测试用的如果你用其他数据库自行修改吧
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 fSub RunSQLQuery()Dim conn As ObjectDim rs As ObjectDim strSQL As StringDim xlApp As ObjectDim xlWorkbook As ObjectDim 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 ProviderSQLOLEDB;Data Source serverName ;Initial Catalog databaseName ;User ID userName ;Password password ; SQL查询命令strSQL {sql_command} 执行查询rs.Open strSQL, conn 将查询结果输出到Excel工作表Dim i As IntegerFor i 1 To rs.Fields.CountActiveSheet.Cells(1, i).Value rs.Fields(i - 1).NameNext iIf Not rs.EOF ThenActiveSheet.Range(A2).CopyFromRecordset rsEnd If 清理资源rs.Closeconn.CloseSet rs NothingSet conn NothingEnd Subreturn 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(fillboth, expandTrue)input_frame tk.Frame(notebook)
input_frame.pack(fillboth, expandTrue)text_frame tk.Frame(input_frame)
text_frame.pack(sidetk.TOP, fillboth, expandTrue)text tk.Text(text_frame, height10, width100)
text.pack(sidetk.LEFT, fillboth, expandTrue)text_scrollbar ttk.Scrollbar(text_frame, orienttk.VERTICAL, commandtext.yview
)
text_scrollbar.pack(sidetk.RIGHT, filltk.Y)
text.configure(yscrollcommandtext_scrollbar.set)notebook.add(input_frame, text输入框)input_frame tk.Frame(root)
input_frame.pack(sidetk.TOP)server_label tk.Label(input_frame, text服务器)
server_label.grid(row0, column0, stickytk.E, padx5, pady5)
server_entry tk.Entry(input_frame)
server_entry.grid(row0, column1, padx5, pady5)database_label tk.Label(input_frame, text数据库)
database_label.grid(row1, column0, stickytk.E, padx5, pady5)
database_entry tk.Entry(input_frame)
database_entry.grid(row1, column1, padx5, pady5)username_label tk.Label(input_frame, text用户名)
username_label.grid(row2, column0, stickytk.E, padx5, pady5)
username_entry tk.Entry(input_frame)
username_entry.grid(row2, column1, padx5, pady5)password_label tk.Label(input_frame, text密码)
password_label.grid(row3, column0, stickytk.E, padx5, pady5)
password_entry tk.Entry(input_frame)
password_entry.grid(row3, column1, padx5, pady5)output_frame tk.Frame(notebook)
output_frame.pack(fillboth, expandTrue)sql_command_text scrolledtext.ScrolledText(output_frame, height10, width100)
sql_command_text.pack(fillboth, expandTrue)vba_code_text scrolledtext.ScrolledText(output_frame, height10, width100)
vba_code_text.pack(fillboth, expandTrue)notebook.add(output_frame, text输出框)button_frame tk.Frame(root)
button_frame.pack(sidetk.TOP)paste_button tk.Button(button_frame, text清空粘贴, commandpaste_text)
paste_button.pack(sidetk.LEFT, padx10)button tk.Button(button_frame, text转换VBA格式, commandremove_newlines)
button.pack(sidetk.LEFT, padx10)button tk.Button(button_frame, text转换SQL格式, commandconvert_to_sql)
button.pack(sidetk.LEFT, padx10)copy_button tk.Button(button_frame, text复制结果, commandcopy_text)
copy_button.pack(sidetk.LEFT, padx10)root.mainloop()
