MS Access VBA script to interface with Excel

Question

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.

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.

What we have-

What is needed-

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

Answer 1

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

Answer 2

I suggest you link the Excel sheet using the various wizards or the TransferSpreadsheet method of DoCmd and simply run action queries using the linked Excel table.

A Union Query is what is required. Let us call your linked spreadsheet t.

SELECT * INTO Table1
FROM (
    SELECT [Seq#], "Name" As [Field Name], [Name] As [Field Value]
    FROM t
    UNION ALL
    SELECT [Seq#], "Location" As [Field Name], [Location] As [Field Value]
    FROM t
    UNION ALL
    SELECT [Seq#], "Car" As [Field Name], [Car] As [Field Value]
    FROM t ) imp

INSERT INTO Table1
SELECT * FROM (
    SELECT [Seq#], "Name" As [Field Name], [Name] As [Field Value]
    FROM t
    UNION ALL
    SELECT [Seq#], "Location" As [Field Name], [Location] As [Field Value]
    FROM t
    UNION ALL
    SELECT [Seq#], "Car" As [Field Name], [Car] As [Field Value]
    FROM t ) imp

You can make life easier by getting rid of the spaces and reserved words in your field and column names.

It is generally better to list fields that to use an asterisk (*) as shown above.