VBA与数据库——排列组合（可重复）

在VBA实现排列组合（可重复）中使用普通的VBA编程方法，实现了排列组合（可重复），代码虽然不是很多，但作为初学者需要理解还是有一定难度的。

如果是使用ADO来实现的话，就比较的简单了：

代码语言：javascript复制

Function ADOGetPermutation(ArrKeysZeroBase() As String, m As Long) As Long
    Dim n As Long
    
    n = UBound(ArrKeysZeroBase) - LBound(ArrKeysZeroBase)   1
    If m = 0 Then
        ADOGetPermutation = -1
        Exit Function
    End If
    
    '数据放到excel中
    Range("A1").Value = "数据"
    With Range("A2").Resize(n, 1)
        .NumberFormat = "@"
        .Value = Application.WorksheetFunction.Transpose(ArrKeysZeroBase)
    End With
    
    Dim strTable As String
    strTable = "[" & ActiveSheet.name & "$" & Range("A1").Resize(n   1, 1).Address(False, False) & "]"
    
    '构建sql语句
    Dim sqlFields() As String, sqlTables() As String
    ReDim sqlFields(m - 1) As String
    ReDim sqlTables(m - 1) As String
    Dim i As Long
    For i = 0 To m - 1
        sqlFields(i) = "T" & VBA.CStr(i) & ".数据"
        sqlTables(i) = strTable & " as T" & VBA.CStr(i)
    Next
    
    Dim strsql As String
    strsql = "select " & VBA.Join(sqlFields, " ") & " from " & VBA.Join(sqlTables, ",")
    
    Dim AdoConn As Object
    Set AdoConn = VBA.CreateObject("ADODB.Connection")
    
    '打开数据库
    AdoConn.Open "Provider =Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.fullname & ";Extended Properties=""Excel 12.0;HDR=YES"";"
    Range("C2").CopyFromRecordset AdoConn.Execute(strsql, , 1)

    AdoConn.Close
    Set AdoConn = Nothing
End Function

程序的核心是构建sql语句，形式如：

代码语言：javascript复制

select T0.数据 T1.数据 T2.数据 from [Sheet1$A1:A5] as T0,[Sheet1$A1:A5] as T1,[Sheet1$A1:A5] as T2

构建的这个sql语句应该是比较好理解的，而这条语句就能够得到一个可以选重复数据的排列组合，程序逻辑相比用普通的VBA语句来说就好理解的多了。

vba sql

0 人点赞