MS Access VBA脚本与Excel交互

问题描述：

我正在尝试在Microsoft Access中编写一个VBA脚本，该脚本将与Excel工作表连接，依次遍历行和行中的单元格，然后将信息拉回到Access表中.

I am trying to write a VBA scriptin Microsoft Access that will interface with an Excel sheet, loop through rows and then cells in the row, and then pull info back into the Access table.

这是一些sudo代码-

Here is some sudo code-

For Each Row
    For Each Cell in the Row
        Read the Cell
        Create a new Record in the Access table with the info from the cell
    End For Each
End For Each

您可以在下面的图片中看到最终结果的简化示例.

You can see a simplified example of the end result in the pictures below.

我们所拥有的-

需要什么-

我以前曾经编写过代码，但从未在VBA中编写过代码；因此，任何帮助将不胜感激！谢谢您的帮助！

I have coded before, but never in VBA; so any help would be appreciated! Thanks for your help!!!

答

首先按照@Remou的建议创建指向Excel工作表的链接.在下面的示例中，我将该链接命名为"tblExcelData".然后，"tblDestination"将根据您的请求为工作表行的每个单元格"存储一个单独的记录.在tblDestination中，Seq#是长整数，并且Field Name和Field Value都是文本.

First create a link to your Excel worksheet as @Remou suggested. In the following example, I named the link as "tblExcelData". Then "tblDestination" will store a separate record for each "cell" of a worksheet row as you requested. In tblDestination, Seq# is long integer, and Field Name and Field Value are both text.

Public Sub foo20120612a()
    Dim db As DAO.Database
    Dim rsSrc As DAO.Recordset
    Dim rsDest As DAO.Recordset
    Dim fld As DAO.Field

    Set db = CurrentDb
    Set rsSrc = db.OpenRecordset("tblExcelData", dbOpenSnapshot)
    Set rsDest = db.OpenRecordset("tblDestination", _
        dbOpenTable, dbAppendOnly)
    Do While Not rsSrc.EOF
        For Each fld In rsSrc.Fields
            If fld.Name <> "Seq#" Then
                With rsDest
                    .AddNew
                    ![Seq#] = CLng(rsSrc![Seq#])
                    ![Field Name] = fld.Name
                    ![Field Value] = fld.value
                    .Update
                End With
            End If
        Next fld
        rsSrc.MoveNext
    Loop
    rsDest.Close
    Set rsDest = Nothing
    rsSrc.Close
    Set rsSrc = Nothing
    Set db = Nothing
End Sub

MS Access VBA脚本与Excel交互

相关推荐