Copy data from Excel to Access

  • Thread starter Thread starter Finn Petersen
  • Start date Start date
F

Finn Petersen

Office 97:

I want to read data cell by cell from Excel and add the data to an Access
table with the command AddNew:

With rstTemp
.AddNew
!Time = strTime
!Name = strName
.Update
.Bookmark = .LastModified
End With

This works fine as long as the data shall go to the field "Name". But I want
to replace the !Name with a variable, so that I can copy data from various
columns in Excel and use the value of the top cell in the column to find the
right column in Access.

Do You have any solutions?

regards

Finn
 
Firstly Finn, change the Name of your field in Access to something else:
Name is a reserved word and will cause you problems further down the line.

P
 
Use the Fields object to define what field of the recordset you want to
populate.

--- For example: (where the Access database has fields named: [myTime] and
[myName])
rstTemp.Fields("myTime").Value = strTime
rstTemp.Fields("myName").Value = strName

--- To use a variable:
Dim sFld0 As String
Dim sFld1 As String

sFld0 = "myTime"
sFld1 = "myName"
rstTemp.Fields(sFld0).Value = strTime
rstTemp.Fields(sFld1).Value = strName

--- Also, you can reference by position (useful for arrays). The fields will
be arranged in order that they appear in the recordset.
rstTemp.Fields(0).Value = strTime
rstTemp.Fields(1).Value = strName

Hope that gets you started.

Troy
 
Back
Top