Option Explicit
Dim MyDataObject As DataObject
Private Sub CommandButton1_Click()
Dim sqlOrg, sqlWithParams, PARAMSYMPOL, QUOTER As String
Dim params() As String
Dim i As Integer
PARAMSYMPOL = "?"
QUOTER = "'"
sqlOrg = TextBox1.Text
params = Split(TextBox2.Text, ",")
For i = 0 To UBound(params)
Dim tempParam As String
tempParam = QUOTER + LTrim(params(i)) + QUOTER
sqlOrg = Replace(sqlOrg, PARAMSYMPOL, tempParam, 1, 1)
Next i
TextBox3.Text = SQLConvert(sqlOrg) 'sqlOrg
CommandButton2_Click
End Sub
Private Sub CommandButton2_Click()
If TextBox3.Text <> "" Then
'クリップボードにコピーする
Set MyDataObject = New DataObject
MyDataObject.SetText TextBox3.Text
MyDataObject.PutInClipboard
End If
End Sub
Private Sub CommandButton3_Click()
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
End Sub
Private Sub CommandButton4_Click()
End Sub
Function SQLConvert(StrOrg)
Dim tempStrSQL As String
Dim index As Integer
Dim vbSpace As String
vbSpace = Chr(32)
' index = InStr(1, StrOrg, Chr(10), 0)
' index = InStr(1, StrOrg, Chr(13), 0)
' index = InStr(1, StrOrg, "SELECT" + vbSpace, 0)
While InStr(1, StrOrg, vbSpace + vbSpace, 0) > 0
StrOrg = Replace(StrOrg, vbSpace + vbSpace, vbSpace)
Wend
StrOrg = Replace(StrOrg, vbNewLine, "")
StrOrg = Replace(StrOrg, vbCr, "")
StrOrg = Replace(StrOrg, "SELECT" + vbSpace, vbLf + "SELECT" + vbLf + vbTab)
StrOrg = Replace(StrOrg, vbSpace + "FROM" + vbSpace, vbLf + "FROM" + vbLf + vbTab)
StrOrg = Replace(StrOrg, vbSpace + "WHERE" + vbSpace, vbLf + "WHERE" + vbLf + vbTab)
StrOrg = Replace(StrOrg, vbSpace + "ORDER" + vbSpace + "BY" + vbSpace, vbLf + "ORDER" + vbSpace + "BY" + vbLf + vbTab)
StrOrg = Replace(StrOrg, vbSpace + "UNION" + vbSpace + "ALL" + vbSpace, vbLf + "UNION" + vbSpace + "ALL")
StrOrg = Replace(StrOrg, vbSpace + "UNION" + vbSpace, vbLf + "UNION")
StrOrg = Replace(StrOrg, vbSpace + "LEFT" + vbSpace + "JOIN" + vbSpace, vbLf + "LEFT JOIN" + vbLf + vbTab)
StrOrg = Replace(StrOrg, vbSpace + "LEFT" + vbSpace + "OUTER" + vbSpace + "JOIN" + vbSpace, vbLf + "LEFT OUTER JOIN" + vbLf + vbTab)
StrOrg = Replace(StrOrg, vbSpace + "LEFT" + vbSpace + "JOIN" + vbSpace, vbLf + "LEFT JOIN" + vbLf + vbTab)
StrOrg = Replace(StrOrg, vbSpace + "RIGHT" + vbSpace + "OUTER" + vbSpace + "JOIN" + vbSpace, vbLf + "RIGHT OUTER JOIN" + vbLf + vbTab)
StrOrg = Replace(StrOrg, vbSpace + "RIGHT" + vbSpace + "JOIN" + vbSpace, vbLf + "RIGHT JOIN" + vbLf + vbTab)
StrOrg = Replace(StrOrg, vbSpace + "INNER" + vbSpace + "JOIN" + vbSpace, vbLf + "INNER JOIN" + vbLf + vbTab)
StrOrg = Replace(StrOrg, vbSpace + "ON" + vbSpace, vbLf + "ON" + vbTab)
StrOrg = Replace(StrOrg, vbSpace + "AND" + vbSpace, vbLf + "AND" + vbTab)
StrOrg = Replace(StrOrg, vbSpace + "OR" + vbSpace, vbLf + "OR" + vbTab)
StrOrg = Replace(StrOrg, vbSpace + "AS" + vbSpace, vbTab + vbTab + "AS" + vbTab)
'StrOrg = Replace(StrOrg, vbSpace + "WHERE" + vbSpace, vbLf + "WHERE" + vbLf + vbTab)
'StrOrg = Replace(StrOrg, "\\s*RIGHT\\s*OUTER\\s*JOIN\\s*", "\\n\\tRIGHT OUTER JOIN\\t\\t")
'StrOrg = Replace(StrOrg, "\\s*RIGHT\\s*JOIN\\s*", "\\n\\tRIGHT JOIN\\t\\t")
'StrOrg = Replace(StrOrg, "\\sAND\\s", "\\nAND\\t")
'StrOrg = Replace(StrOrg, "\\sOR\\s", "\\nOR\\t")
'StrOrg = Replace(StrOrg, "\\sAS\\s", "\\tAS\\t\\t")
StrOrg = Replace(StrOrg, vbSpace + ",", vbLf + vbTab + ",")
SQLConvert = StrOrg
End Function
分享到:
相关推荐
VBA+SQL数据处理VBA+SQL数据处理VBA+SQL数据处理VBA+SQL数据处理
VBA连接SQLSERVER数据库实例
首先,必须严肃脸说明的是,对于普通 Excel 使用者而言,VBA、SQL 以及以 后提及的 ADO 并不是非学不可的,非学不可的是基础操作、函数、透视表、图表…… 然而大数据时代,对于另外相当一部分表族而言,Excel 用...
利用EXCEL VBA+SQL数据库开发常用的办公模板,代码未加密,可以直接复制和使用!欢迎下载!
excel vba 连接sql server程序
VBA+SQLServer跨电脑读写数据传输
Excel_VBA+ADO+SQL入门教程看见星光.docx
Excel VBA函数生成SQL
自定义修改:数据库地址、sa账户密码、数据库表名,表字段可自定义添加(必须和数据库表字段相同)
自已寫的,很好的Excel VBA 學習資料。
VBA连接SQL数据库的代码。代码很简单,但是包含了不少初学者可能遇到的三个问题,一是长查询语句的换行问题,二是查询语句执行时间过长导致vba提示查询超时的问题,三是标题行和内容行怎么显示的问题。都很简单,...
Excel+vba通用SQL查询输出器源码 用于对Excel表格的SQL查询,被SQL操作的Excel表格必须有表头,作为类似数据库的字段,并位于表格第一行,SHEET1名称被指定时请注意替换默认的from后的表名称。
包含:Excel2010_常用SQL语句解释 Excel VBA 完全手册
是vba写自动创建sql的小工具,针对Oracle数据库,方便的生成sql语句
一个用VBA将数据写入SQLServer的实例,希望对大家有帮助
vba自动生成sql
在企业信息化初期,大部分数据都会通过excel进行管理; 作为IT人员,其实对excel又爱又恨,爱其灵活...现在就分享一个资源,Excel VBA 上传当前sheet中的所有内容到数据库, 然后通过excle调取数据库的数据进行呈现;
经典Excel_VBA_SQL语句经典Excel_VBA_SQL语句
基于VBA的EXCEL数据簿和SQL数据库转换处理方法.pdf
上面是一个vba连接数据库的列子,很简单的一个,上面有教你如何连接数据库的。