|
|
楼主 |
发表于 2024-5-14 02:37
|
显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
以下为文心一言得到的一个大略，哪位可以帮忙修改一下，使可运行于附表，试试是否运行时间会快一些？===Sub ProcessDataWithDatabase()
Dim conn As Object
Dim rs As Object
Dim cmd As Object
Dim ws As Worksheet
Dim resultsRow As Long
Dim searchColumn As Long
Dim i As Long, j As Long, k As Long
Dim wsValue1 As Variant, wsValue2 As Variant, wsValue3 As Variant
Dim combination As Long
Dim sql As String
Dim count As Long
Dim lastRowDs As Long ' 假设这是你的数据源的最后一行
' 初始化
Set ws = ThisWorkbook.Sheets("Sheet1") ' 修改为你的工作表名称
lastRowDs = 1000 ' 假设这是你的数据源的实际最后一行，你需要根据实际情况设置
Set conn = CreateObject("ADODB.Connection")
Set cmd = CreateObject("ADODB.Command")
' 连接数据库（这里以SQL Server为例，你需要根据实际情况修改连接字符串）
conn.Open "Provider=SQLOLEDB;Data Source=YourServer;Initial Catalog=YourDatabase;User ID=YourUsername;Password=YourPassword;"
' 禁用屏幕更新和手动计算以提高性能
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
' 遍历每一行数据
For resultsRow = 1002 To 1200
' 假设这里不需要将整列数据导入到数组中，因为我们将直接在数据库中查询
' 执行内部循环以构建组合并查询数据库
For i = 1 To 80
For j = 1 To 18
wsValue1 = ws.Cells(resultsRow, 2 + j).Value
For k = j + 1 To 19
wsValue2 = ws.Cells(resultsRow, 2 + k).Value
For l = k + 1 To 20
wsValue3 = ws.Cells(resultsRow, 2 + l).Value
combination = wsValue1 * 1000000 + wsValue2 * 10000 + wsValue3 * 100 + i
' 构建SQL查询字符串
sql = "SELECT COUNT(*) FROM YourTable WHERE CombinationColumn = " & combination
' 执行查询并获取结果
Set rs = conn.Execute(sql)
If Not rs.EOF Then
count = rs.Fields("Expr1000").Value ' 假设Expr1000是SQL查询返回的别名，这取决于你的查询
' 将结果写回到Excel中
ws.Cells(22800 + i, resultsRow).Value = count
count = 0 ' 重置count，因为我们要为下一个组合计数
End If
rs.Close
Next l
Next k
Next j
Next i
Next resultsRow
' 清理并关闭连接
If Not rs Is Nothing Then rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
' 恢复屏幕更新和计算
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub |
|
楼主: 付伟林
沪公网安备 31011702000001号 沪ICP备11019229号-2
