This is all fairly new to me so please be gentle...... i would like to know
if there is a way to automate data entry between excel and access. Here is my
situation,
I have an Excel worksheet that figures money amounts over a selected period
of time, gives a projection, and will save the data/file/worksheet as the
client name. What i would like to do with this data is to have it copy/enter
into an access database automaticly say, during the save, so that it is
usable for future use. Can i use VB to accomplish this and if so, How? and
or, what would be the easiest way for this to be accomplished?

Any help would be greatly appreacated

Option Explicit
Private Sub saveDataToAccess()
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sSQL As String, strConn
Dim r As Integer
r = 6
'Use for Access (jet)
'Assumes that the access database is in the same folders as thisworkbook
strConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
ThisWorkbook.Path & "\NameOfYourmdb.mdb;Persist Security Info=False"

'Use for jet
'sSQL = Name Of Your Access table Change to your
'Table Name
sSQL = "TableName"

Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset
cnn.Open strConn

rs.Open sSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdTable
Do While Len(Range("A" & r).Formula) 0
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("Field1") = Range("A" & r).Value
.Fields("Field2") = Range("E" & r).Value
.Fields("Field3") = Range("F" & r).Value
.Fields("Field4") = Range("G" & r).Value

' add more fields if necessary...
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
cnn.Close

"Ted Helgeson" wrote:

This is all fairly new to me so please be gentle...... i would like to know
if there is a way to automate data entry between excel and access. Here is my
situation,
I have an Excel worksheet that figures money amounts over a selected period
of time, gives a projection, and will save the data/file/worksheet as the
client name. What i would like to do with this data is to have it copy/enter
into an access database automaticly say, during the save, so that it is
usable for future use. Can i use VB to accomplish this and if so, How? and
or, what would be the easiest way for this to be accomplished?

Any help would be greatly appreacated