将聚合数据从Excel加载到ADODB.RecordSet中 [英] Load aggregate data from Excel into ADODB.RecordSet

问题描述

我正在尝试使用SQL SELECT命令通过VBA宏将特定工作表中的Excel文件中的数据加载到ADODB.RecordSet中.Excel工作表上有几列，而我并不需要全部.

例如:col.A =姓，col.B =名称，col.C = IDPerson，[....不需要的列]，N.Col =老板

目的是获取以下各项的汇总数据记录集:col.C = IDPerson，col.N = Boss.下图中突出显示的字段.

我想创建一个RecordSet，并用黄色突出显示列的聚合(非重复)数据.

显然，该问题也可以通过加载矩阵来解决，但是在这种情况下，我将必须构建一种加载算法来清理"矩阵.数据中的任何重复，然后以后我将不得不提供具有某些循环的搜索功能.因此，我认为如果可以通过读取WorkSheet来加载所需的所有数据，就好像它是数据表一样，然后对其进行查询以提取所需的数据并将所有内容加载到ADODB中.RecordSet会更多搜索数据(例如过滤器数据)也很有效.

下面，我报告我的代码，该代码将加载工作表中的所有数据:

 公共子LoadRecordSet(ByVal LastRow为long，ByVal LastCol为Integer)昏暗作为ADODB.ConnectionDim rsData作为ADODB.Recordset昏暗的strSQL作为字符串Dim strTMP作为字符串strTMP =单元格(LastRow，LastCol).地址strTMP =替换(strTMP，"$"，")设置cnt = New ADODB.Connectioncnt.Mode = adModeReadcnt.ConnectionString ="Provider = Microsoft.ACE.OLEDB.12.0;"&_数据源="&ActiveWorkbook.FullName&" ;;"&_扩展属性=" Excel 12.0宏； HDR =是；"；打开strSQL ="SELECT * FROM [Sheet1 $ C2:''&strTMP&"]"设置rsData = New ADODB.Recordset使用rsData设置.ActiveConnection = cnt源= strSQL.CursorLocation = adUseClient.CursorType = adOpenKeyset.LockType = adLockOptimistic.打开结束于'TODO-使用rsData进行过滤或研究的内容'GC如果不是rsData什么都没有，那么如果rsData.State<>adStateClosed然后rsData.Close设置rsData = Nothing万一如果没有cnt什么都没有如果cnt.State<>adStateClosed然后cnt.Close设置cnt = Nothing万一结束子 

我的问题是:如果我只想如上所述加载一些列并进行汇总以使它们在数据中没有重复呢?"

例如，如果我想加载类似的内容 SELECT [cod.fiscale]，负责人来自[MySheet $ A3:N480] GROUP BY [cod.fiscale]，负责人

有可能吗?非常感谢.

解决方案

我改进了现在可以正常工作的代码:

  Public Sub CaricaDati()昏暗作为ADODB.ConnectionDim rsDati作为ADODB.Recordset昏暗的strSQL作为字符串Dim strTMP作为字符串昏暗的整数关于错误转到To Error_Handler范围("A3").选择g_BOLTS_UltimaRiga = LasRow致电LastColstrTMP =单元格(g_LastRow，g_LastCol).地址strTMP =替换(strTMP，"$"，")设置cnt = New ADODB.Connectioncnt.Mode = adModeReadcnt.ConnectionString ="Provider = Microsoft.ACE.OLEDB.12.0;"&_数据源="&ActiveWorkbook.FullName&" ;;"&_扩展属性=" Excel 12.0宏； HDR =是；"；打开'strSQL ="SELECT * FROM [2 $ C2:"&strTMP&"]"strSQL ="SELECT cf，对[2 $ C2:负责]"&strTMP&] GROUP BY cf，负责任"；设置rsDati = New ADODB.Recordset与rsDati设置.ActiveConnection = cnt源= strSQL.CursorLocation = adUseClient.CursorType = adOpenKeyset.LockType = adLockOptimistic.打开结束于如果不是(rsDati.BOF和rsDati.EOF)，则strTMP ="对于i = 0到rsDati.Fields.Count-1strTMP = strTMP&rsDati.Fields(i).名称&" ;;"接下来我调试打印strTMPstrTMP ="rsDati.MoveFirst不做rsDati.EOFstrTMP ="对于i = 0到rsDati.Fields.Count-1strTMP = strTMP&rsDati.Fields(i).Value&" ;;"接下来我调试打印strTMPrsDati.MoveNext环形万一乌斯奇塔:关于错误继续'GC如果不是rsDati则一无所有如果rsDati.State<>adStateClosed然后rsDati.Close设置rsDati = Nothing万一如果没有cnt什么都没有如果cnt.State<>adStateClosed然后cnt.Close设置cnt = Nothing万一退出子Error_Handler:出错时转到0MsgBox错误号&"-"&错误说明，vbOKOnly + vbCritical，错误不正确".转到乌斯奇塔结束子 

I am trying to load data from an Excel file in a specific sheet into an ADODB.RecordSet via a VBA macro by using SQL SELECT command. There are several columns on the Excel sheet, and I don't need all of them.

For example: col.A = Surname, col.B = Name, col.C = IDPerson, [....columns that are not needed], Col.N = Boss

The purpose would be to get a recordset of aggregated data for: col.C = IDPerson, col.N = Boss. The fields highlighted in the image below.

I would like to have a RecordSet with the aggregated (non-repeating) data of the columns highlighted in yellow.

Obviously, this problem could also be solved by loading a matrix, but, in this case I would have to build a loading algorithm to "clean" any repetitions in the data and then later I would have to provide a search function with some loops. So I thought that if I could load all the data I need by reading the WorkSheet as if it were a data table and then make a query on it to extract the data that I need and load everything in an ADODB.RecordSet would be much more efficient also for searching for data (filter data for example).

Below I report my code that loads all the data of my sheet:

Public Sub LoadRecordSet(ByVal LastRow As Long, ByVal LastCol As Integer)
    Dim cnt As ADODB.Connection
    Dim rsData As ADODB.Recordset
    Dim strSQL As String
    Dim strTMP As String

    strTMP = Cells(LastRow, LastCol).Address
    strTMP = Replace(strTMP, "$", "")

    Set cnt = New ADODB.Connection
    cnt.Mode = adModeRead
    cnt.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                            "Data Source=" & ActiveWorkbook.FullName & ";" & _
                            "Extended Properties=""Excel 12.0 Macro;HDR=Yes;"";"
    cnt.Open
    
    strSQL = "SELECT * FROM [Sheet1$C2:" & strTMP & "]"
    Set rsData = New ADODB.Recordset
    With rsData
        Set .ActiveConnection = cnt
        .Source = strSQL
        .CursorLocation = adUseClient
        .CursorType = adOpenKeyset
        .LockType = adLockOptimistic
        .Open
    End With
    
    'TODO - Something with rsData for filtering or to research
    
    'GC
    If Not rsData Is Nothing Then
        If rsData.State <> adStateClosed Then rsData.Close
        Set rsData = Nothing
    End If
    If Not cnt Is Nothing Then
        If cnt.State <> adStateClosed Then cnt.Close
        Set cnt = Nothing
    End If
End Sub

My question is: "What if I just want to load some columns as described above and aggregate them so they don't have repetitions in the data?"

For example if I want to load similar SELECT [cod.fiscale], responsabile FROM [MySheet$A3:N480] GROUP BY [cod.fiscale], responsabile

It's possible? Thank you so much.

解决方案

I improved my code which is now working:

Public Sub CaricaDati()
    Dim cnt As ADODB.Connection
    Dim rsDati As ADODB.Recordset
    Dim strSQL As String
    Dim strTMP As String
    Dim i As Integer

on Error GoTo Error_Handler
    Range("A3").Select
    g_BOLTS_UltimaRiga = LasRow
    Call LastCol
    strTMP = Cells(g_LastRow, g_LastCol).Address
    strTMP = Replace(strTMP, "$", "")

    Set cnt = New ADODB.Connection
    cnt.Mode = adModeRead
    cnt.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                            "Data Source=" & ActiveWorkbook.FullName & ";" & _
                            "Extended Properties=""Excel 12.0 Macro;HDR=Yes;"";"
    cnt.Open
    
    'strSQL = "SELECT * FROM [2$C2:" & strTMP & "]"
    strSQL = "SELECT cf, responsabile FROM [2$C2:" & strTMP & "] GROUP BY cf, responsabile"
    Set rsDati = New ADODB.Recordset
    With rsDati
        Set .ActiveConnection = cnt
        .Source = strSQL
        .CursorLocation = adUseClient
        .CursorType = adOpenKeyset
        .LockType = adLockOptimistic
        .Open
    End With
    
    If Not (rsDati.BOF And rsDati.EOF) Then
        strTMP = ""
        For i = 0 To rsDati.Fields.Count - 1
            strTMP = strTMP & rsDati.Fields(i).Name & ";"
        Next i
        Debug.Print strTMP
        
        strTMP = ""
        rsDati.MoveFirst
        Do While Not rsDati.EOF
            strTMP = ""
            For i = 0 To rsDati.Fields.Count - 1
                strTMP = strTMP & rsDati.Fields(i).Value & ";"
            Next i
            Debug.Print strTMP
            rsDati.MoveNext
        Loop
    End If
    
Uscita:
On Error Resume Next
    'GC
    If Not rsDati Is Nothing Then
        If rsDati.State <> adStateClosed Then rsDati.Close
        Set rsDati = Nothing
    End If
    If Not cnt Is Nothing Then
        If cnt.State <> adStateClosed Then cnt.Close
        Set cnt = Nothing
    End If
Exit Sub
Error_Handler:
        On Error GoTo 0
        MsgBox Err.Number & " - " & Err.Description, vbOKOnly + vbCritical, "ERRORE IMPREVISTO"
        GoTo Uscita
    End Sub

这篇关于将聚合数据从Excel加载到ADODB.RecordSet中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！