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-

enter image description here

What is needed-

enter image description here

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

Please read what Remou wrote, there is already a built-in method for doing this. Even reading the range into an array rather than looping through each cell would be better. — JimmyPena
– JimmyPena, Commented Jun 12, 2012 at 15:41

HansUp · Accepted Answer · 2012-06-12 16:15:57Z

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

Fionnuala · Accepted Answer · 2012-06-12 16:22:47Z

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.

edited Jun 12, 2012 at 16:22

answered Jun 12, 2012 at 15:09

Fionnuala

91.4k7 gold badges121 silver badges155 bronze badges

1 Comment

ohGosh Over a year ago

I have used the wizard to pull in data from Excel and that worked great. However, I have never used action queries before to organize data. Can you elaborate a bit on how to use action queries to format the data the way it is shown the pictures?

Collectives™ on Stack Overflow

MS Access VBA script to interface with Excel

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related