vba sql查询

发布网友发布时间：2022-04-10 01:50

共2个回答

懂视网时间：2022-04-10 06:11

Sub NextSeven_CodeFrame()
‘应用程序设置
 Application.ScreenUpdating = False
 Application.DisplayAlerts = False
 Application.Calculation = xlCalculationManual

 ‘错误处理
 On Error GoTo ErrHandler

 ‘计时器
 Dim StartTime, UsedTime As Variant
 StartTime = VBA.Timer

 ‘变量声明
 Dim wb As Workbook
 Dim sht As Worksheet
 Dim Rng As Range
 Dim Arr As Variant
 Dim EndRow As Long

 Dim oSht As Worksheet
 Dim DataPath As String
 Dim SQL As String
 Dim EndDate As Date
 Dim StartDate As Date
 Dim Client As String



 ‘实例化对象
 Set wb = Application.ThisWorkbook
 Set sht = wb.Worksheets("凭证录入")
 Set oSht = wb.Worksheets("客户明细")
 DataPath = wb.FullName



 usertxt = Application.InputBox("请输入开始日期", "开始日期", , , , , , 2)
 If usertxt = False Then Exit Sub
 StartDate = Format(CDate(usertxt), "yyyy-mm-dd")


 usertxt = Application.InputBox("请输入结束日期", "结束日期", , , , , , 2)
 If usertxt = False Then Exit Sub
 EndDate = Format(CDate(usertxt), "yyyy-mm-dd")

 usertxt = Application.InputBox("请输入客户姓名", "客户姓名", , , , , , 2)
 If usertxt = False Then Exit Sub
 Client = CStr(usertxt)


 oSht.UsedRange.Offset(1).Clear
 Set Rng = oSht.Range("A2")

 SQL = "SELECT * FROM [" & sht.Name & "$A3:V] WHERE 出货客户=‘" & Client & "‘ AND ( 出货日期 Between #" & StartDate & "# AND #" & EndDate & "# )"
 SQL = SQL & " ORDER BY 型号 ASC"
 If RecordExistsRunSQL(DataPath, SQL) = True Then
 GetRecordSetIntoRange DataPath, SQL, Rng
 End If



 ‘运行耗时
 UsedTime = VBA.Timer - StartTime
 MsgBox "本次运行耗时：" & Format(UsedTime, "0.0000000秒")
ErrorExit: ‘错误处理结束，开始环境清理
 Set wb = Nothing
 Set sht = Nothing
 Set Rng = Nothing

 Application.ScreenUpdating = True
 Application.DisplayAlerts = True
 Application.Calculation = xlCalculationAutomatic
 Exit Sub
ErrHandler:
 If Err.Number <> 0 Then
 MsgBox Err.Description & "！", vbCritical, "错误提示！"
 ‘Debug.Print Err.Description
 Err.Clear
 Resume ErrorExit
 End If
End Sub

Public Sub GetRecordSetIntoRange(ByVal DataPath As String, ByVal SQL As String, ByVal Rng As Range)
‘对传入数据源地址进行判断
 If Len(DataPath) = 0 Or Len(Dir(DataPath)) = 0 Then _
 MsgBox "数据源地址为空或者数据源文件不存在！", vbInformation, "NS Excel Studio": Exit Sub
 ‘对传入SQL语句进行判断
 If Len(SQL) = 0 Then _
 MsgBox "SQL语句不能为空！", vbInformation, "NS Excel Studio": Exit Sub
 ‘对象变量声明
 Dim cnn As Object
 Dim rs As Object
 ‘数据库引擎——Excel作为数据源
 Const DATA_ENGINE As String = "Provider=Microsoft.jet.OLEDB.4.0;" & _
  "Extended Properties=‘Excel 8.0;HDR=YES;IMEX=2‘; Data Source= "
 ‘创建ADO Connection 连接器 实例
 Set cnn = CreateObject("ADODB.Connection")
 ‘On Error Resume Next
 ‘创建 ADO RecordSet 记录集 实例
 Set rs = CreateObject("ADODB.RecordSet")
 ‘连接数据源
 cnn.Open DATA_ENGINE & DataPath
 ‘执行查询 返回记录集
 rs.Open SQL, cnn, 1, 1
 ‘Set RS = CNN.Execute(SQL)
 ‘复制记录集到指定Range
 Rng.CopyFromRecordset rs
 ‘关闭记录集
 rs.Close
 ‘关闭连接器
 cnn.Close
 ‘释放对象
 Set rs = Nothing
 Set cnn = Nothing
End Sub
Public Function RecordExistsRunSQL(ByVal DataPath As String, ByVal SQL As String) As Boolean
‘对传入数据源地址进行判断
 If Len(DataPath) = 0 Or Len(Dir(DataPath)) = 0 Then
 RecordExistsRunSQL = False
 MsgBox "数据源地址为空或者数据源文件不存在！", vbInformation, "NS Excel Studio"
 Exit Function
 End If
 ‘对传入SQL语句进行判断
 If Len(SQL) = 0 Then
 RecordExistsRunSQL = False
 MsgBox "SQL语句不能为空！", vbInformation, "NS Excel Studio"
 Exit Function
 End If
 ‘对象变量声明
 Dim cnn As Object
 Dim rs As Object
 ‘数据库引擎——Excel作为数据源
 ‘Const DATA_ENGINE As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
  ‘ "Extended Properties=‘Excel 12.0;HDR=YES;IMEX=2‘; Data Source= "
  
  ‘数据库引擎——Excel作为数据源
 Const DATA_ENGINE As String = "Provider=Microsoft.jet.OLEDB.4.0;" & _
  "Extended Properties=‘Excel 8.0;HDR=YES;IMEX=2‘; Data Source= "
  
  
 ‘创建ADO Connection 连接器 实例
 Set cnn = CreateObject("ADODB.Connection")
 On Error Resume Next
 ‘创建 ADO RecordSet 记录集 实例
 Set rs = CreateObject("ADODB.RecordSet")
 ‘连接数据源
 cnn.Open DATA_ENGINE & DataPath
 ‘执行查询 返回记录集
 rs.Open SQL, cnn, 1, 1
 ‘返回函数结果
 If rs.RecordCount > 0 Then
 RecordExistsRunSQL = True
 Else
 RecordExistsRunSQL = False
 End If
 ‘关闭记录集
 rs.Close
 ‘关闭连接器
 cnn.Close
 ‘释放对象
 Set rs = Nothing
 Set cnn = Nothing
End Function

20170112xlVBA查询SQL

标签：orm form close bool val bug sql语句 div his

热心网友时间：2022-04-10 03:19

sql一般用在多条件查询，对数据库读写等。
1. VBA如何建立sql连接
A．在VBA Project 窗口中，“工具—引用—Microsoft ActiveX Data Objects 2.0 Library”，如下图所示

B． VBA语句
Private Sub CommandButton1_Click()
Dim cnn As ADODB.Connection’定义变量
Dim sql$, Stext$, i As Integer
Worksheets(3).Cells.Clear
Stext = IIf(TextBox1.Value = "", "*", TextBox1.Value)
Set cnn = CreateObject("adodb.connection")’生成对象
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & ThisWorkbook.FullName’打开数据库
If TextBox1 = "" Then
sql = "select * from [sheet2$]"’sql语句
Else
sql = "select * from [sheet2$] where 姓名 like '" & TextBox1.Value & "'"
End If
Worksheets(3).[a1].CopyFromRecordset cnn.Execute(sql)’执行sql语句
cnn.Close
Set cnn = Nothing
End Sub
2. sub text()
Dim cnn As ADODB.Connection’定义变量
Dim rs as adodb.recordset
Set cnn = CreateObject("adodb.connection")’生成对象,若定义变量时为Dim cnn As New ADODB.Connection，此句可省略
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & ThisWorkbook.FullName’打开数据库
sql = "select * from [sheet2$] where 姓名 like '" & TextBox1.Value & "'"
rs.open sql,cnn,adOpenKeyset.adLockOptimistic’生成查询记录，为后面出书数据准备
Worksheets(3).[a1].CopyFromRecordset rs
end sub
________________________________________________________________________
以下根据网络整理
2 Select SQL语句
SELECT * FROM table_name WHERE column1 = xxx [AND column2 > yyy] [OR column3 <> zzz]
'*'表示全部的栏位都列出来，如果不全部列出，则可以写字段名称如学号，姓名……；WHERE 之后是接条件式，把符合条件的资料列出来，table_name如果为工作表名称，书写格式[工作表名$]，下同。
SELECT column1,AVG(column2) FROM table_name GROUP BY column1 HAVING AVG(column2) > xxx
说明： 1.GROUP BY: 以column1 为一组计算 column2 的平均值必须和 AVG、SUM等整合性查询的关键字一起使用。2.HAVING : 必须和 GROUP BY 一起使用作为整合性的*。
复合性的查询：SELECT * FROM table_name1 WHERE EXISTS (SELECT * FROM table_name2 WHERE conditions )
说明：1.WHERE 的 conditions 可以是另外一个的 query。2. 在此是指存在与否。
SELECT column1,columns2,...FROM table_name
说明：把table_name 的特定栏位资料全部列出来
SELECT COUNT (*) FROM table_name WHERE column_name = xxx
说明：查询符合条件的资料共有几笔。
1.计算出总和，所选的栏位必须是可数的数字形态。2.除此以外还有 AVG() 是计算
SELECT SUM(column1) FROM table_name
说明：平均、MAX()、MIN()计算最大最小值的整合
SELECT * FROM table_name1 WHERE column1 BETWEEN xx AND yy
说明：BETWEEN 表示 column1 的值介於 xx 和 yy 之间。
3. update SQL语句
UPDATE table_name SET column1='xxx' WHERE conditoins
说明：1.更改某个栏位设定其值为'xxx'。
2.conditions 是所要符合的条件、若没有 WHERE 则整个 table 的那个栏位都会全部被更改。
4. deletesql语句
删除资料： DELETE FROM table_name WHERE conditions
说明：删除符合条件的资料。
说明：关于WHERE条件后面如果包含有日期的比较，不同数据库有不同的表达式
5．SQL中通配符用
• "%" 表示零个或多个，
• "_"表示任何单一字符
• [charlist] 字符列中的任何单一字符
• [^charlist] [!charlist] 不在字符列中的任何单一字符
• select * from databasename where fieldname like '%XX%'
• SELECT * FROM Persons WHERE City LIKE '[AOF]%'
• 从上面的 "Persons" 表中选取居住的城市以 "A" 或 "O" 或 "F" 开头的人
• SELECT * FROM Persons WHERE City LIKE '[!AOF]%'
• 从上面的 "Persons" 表中选取居住的城市不以 "A" 或 "O" 或 "F" 开头的人