Copy data from Excel to Access
 

Thank you, Troy
But what if the column name in Excel is "AnotherName". Then I want the macro
to find the field "AnotherName" in the database, and then write data to that
field.
The field name is unknown when I write the VBA code.

regards

Finn


'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

"Finn Petersen" wrote in message
...
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

Copy data from Excel to Access
 

Finn,

Below are few more examples. strFld is the variable that holds the name of
the field.

Troy


Dim strFld As String

'This will save the value held in the variable strTime into the Field named
"AnotherName".
strFld = "AnotherName"
rstTemp.Fields(strFld).Value = strTime

'This will save the current date into the Field named "YetAnotherName".
strFld = "YetAnotherName"
rstTemp.Fields(strFld).Value = Format(Now(), "mm/dd/yyyy")


rstTemp.Fields("AnotherName").Value = strTemp
' - Is equivalent to writing -
strFld = "AnotherName"
rstTemp.Fields(strFld).Value = strTemp


--- Another example:
Dim iNum As Integer
Dim strFld As String

For iNum = 1 to 3
strFld = "myFld" & iNum
rstTemp.Fields(strFld).Value = iNum
Next iNum

'Save the value 1 into the field named: myFld1
'Save the value 2 into the field named: myFld2
'Save the value 3 into the field named: myFld3


"Finn Petersen" wrote in message
...
Thank you, Troy
But what if the column name in Excel is "AnotherName". Then I want the
macro
to find the field "AnotherName" in the database, and then write data to
that
field.
The field name is unknown when I write the VBA code.

regards

Finn


'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

"Finn Petersen" wrote in message
...
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

Copy data from Excel to Access
 

Thanks Troy

It works perfect!

Finn



"TroyW" skrev i en meddelelse
...
Finn,

Below are few more examples. strFld is the variable that holds the name
of
the field.

Troy


Dim strFld As String

'This will save the value held in the variable strTime into the Field
named
"AnotherName".
strFld = "AnotherName"
rstTemp.Fields(strFld).Value = strTime

'This will save the current date into the Field named "YetAnotherName".
strFld = "YetAnotherName"
rstTemp.Fields(strFld).Value = Format(Now(), "mm/dd/yyyy")


rstTemp.Fields("AnotherName").Value = strTemp
' - Is equivalent to writing -
strFld = "AnotherName"
rstTemp.Fields(strFld).Value = strTemp


--- Another example:
Dim iNum As Integer
Dim strFld As String

For iNum = 1 to 3
strFld = "myFld" & iNum
rstTemp.Fields(strFld).Value = iNum
Next iNum

'Save the value 1 into the field named: myFld1
'Save the value 2 into the field named: myFld2
'Save the value 3 into the field named: myFld3


"Finn Petersen" wrote in message
...
Thank you, Troy
But what if the column name in Excel is "AnotherName". Then I want the
macro
to find the field "AnotherName" in the database, and then write data to
that
field.
The field name is unknown when I write the VBA code.

regards

Finn


'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

"Finn Petersen" wrote in message
...
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