Import into Access from Excel, passing a variable as the field...

[Hendy88]

Hello all,

I have an Excel spreadsheet with a header that contains the exact same
30 names as the corresponding 30 fields in an Access DB table, and Row
2 below the Header contains the data I want to import. Instead, of
physically typing all 30 fields in the code, I have a macro that loops
through each "field" name that it got from the spreadsheet header.
When I run this macro I get "run-time error '3265' Item not found in
this collection', however, when I specify the actual field Name
manually it works. This what I have:


Dim iColumn As Integer
Dim FieldName As String
Dim FieldData As String

Dim AccessDB As Database
Dim AccessRecordSet As Recordset

Set AccessDB = OpenDatabase("C:\MyDataBase.mdb")
Set AccessRecordSet = AccessDB.OpenRecordset("MyTable", dbOpenDynaset)

With AccessRecordSet
.AddNew
For iColumn = 1 To 30
FieldName = Cells(1, iColumn).Value
FieldData = Cells(2, iColumn).Value
![FieldName] = FieldData
Next iColumn
.Update
End With


Again, if change: ![FieldName] = FieldData to, let's say: !
[Customer] = FieldData , then it works fine (but then it would only
import into the database on that particular field 30 times).

Do I need to reset my FieldName variable to something other than
String? I've tried even rewriting the code to the following but that
didn't work either:

![ & "FieldName" & ] = FieldData

 

[strive4peace]

instead of
![FieldName] = FieldData

try this:
..Fields(FieldName) = FieldData

~~~

the reason it stops at 30 time is because of this line:
For iColumn = 1 To 30

~~~

it would be good to change:
Dim AccessRecordSet As Recordset

to:
Dim AccessRecordSet As dao.Recordset

~~~

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*