excel - 是否可以从此示例数据创建嵌套字典？

Question

我熟悉集合和数组，但现在我正在尝试扩展到字典。我有一个工作 Excel VBA 应用程序，用于创建和存储建筑付款申请表。由于您的原因，它很笨重且编码很差。自从编写这个程序以来，我学到了很多东西，是时候进行必要的性能和结构改进了。

处理原始数据的一些挑战是......

创建新的付款应用程序新的付款申请需要以前申请的信息编辑现有的付款申请归档旧的付款申请数据位于SharePoint上的Excel文件中，前端Excel文件在不同用户之间共享。

我觉得字典可以极大地简化我的代码，但不确定我想要做的事情是否可行。如果可能的话，我真的只需要在正确的方向上轻轻插入即可。如果没有，您对解决这个问题的最佳方法有什么建议吗？我控制数据，因此如果需要，我可以将其分成单独的表。我宁愿远离后端的 Access 或 SQL Server。

我的示例数据如下所示:

<表类=“s-表”><标题>项目#应用程序#商品编号描述值 <正文>23-00111文本1货币123-00112文本2货币223-00121文本1货币123-00122文本2货币223-00211文本1货币123-00212文本2货币223-00221文本1货币123-00222文本2货币2

希望这个示例数据足以传达该模式。我的实际数据是项目编号后的几千行和 11 列。

当字典放入 watch 并展开时，我希望看到字典看起来像这样:

23-001
    1
        1
            Description
            Value
        2
            Description
            Value
23-002
    1
        1
            Description
            Value
        2
            Description
            Value

看起来就像数据透视表的输出......

Answer 1

您无需拥有 Access 或 SQL 服务器即可对 Excel 数据使用 SQL。以下代码将使用 ADO，连接到工作簿并从 Sheet1 获取数据作为输入。之后您可以使用 SQL 来处理数据，甚至可以使用 SQL 语句更新数据

Option Explicit

Sub AdoTest_UpdateExcelFile()
    
    Dim xlFile As String
    xlFile = ThisWorkbook.FullName

    ' You have to add Active Data Objects to the referencss
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim fld As Field
    Dim sSQL As String

    With cn
        .Provider = "Microsoft.ACE.OLEDB.16.0"
        .ConnectionString = "Data Source=" & xlFile & "; Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
        .Open
    End With

    sSQL = "SELECT * FROM [Sheet1$]"
    rs.Open sSQL, cn

    rs.MoveFirst
    Worksheets.Add
    
    Dim i As Long
    For i = 0 To rs.Fields.Count - 1
        Cells(1, i + 1).value = rs.Fields(i).Name
    Next i
        
    Range("A2").CopyFromRecordset rs
    rs.Close
    
    
    Sheet1.Activate
    ' One can also update data
    cn.Execute "Update [Sheet1$] SET Project='23-100' WHERE Project ='23-001' ;"
    
    ' In the following statement I left out the column 'value' because that is in conflict woth the SQL Key word VALUE
    ' You can circumvent that though
    cn.Execute "Insert Into [Sheet1$] (Project,Application ,Item,Description) Values('23-003',3,1,'TEXT4')"

End Sub

进一步阅读

Using ADO with VBA