corporateaccount
Board Regular
- Joined
- Aug 11, 2004
- Messages
- 64
Hello all, I've done a search on this and I believe I am on the right track.
Basically, all I need to do is take selected information from an Excel workbook and copy it to a closed Access file.
I've found this code:
Sub DAOFromExcelToAccess()
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim db As Database, rs As Recordset, r As Long
Set db = OpenDatabase("C:\FolderName\DataBaseName.mdb")
' open the database
Set rs = db.OpenRecordset("TableName", dbOpenTable)
' get all records in a table
r = 3 ' the start row in the worksheet
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("FieldName1") = Range("A" & r).Value
.Fields("FieldName2") = Range("B" & r).Value
.Fields("FieldNameN") = Range("C" & r).Value
' add more fields if necessary...
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End Sub
Which takes a stream of information until it ends and places it on a mdb file. What I would need is just to take five or six selected pieces of information and add them in. I've tried to simplify the code to something like this, but it does not work.
Sub DAOFromExcelToAccess()
Dim db As Database, rs As Recordset
Set db = OpenDatabase("C:\FolderName\DataBaseName\db1.mdb")
Set rs = db.OpenRecordset("TableName", dbOpenTable)
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("Name") = Sheet2.Cells(25, 1).Value
.Fields("Address") = Range(26, 1).Value
.Fields("Phone") = Range(27, 1).Value
.Update ' stores the new record
End With
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End Sub
Can anyone please help me out here?
Basically, all I need to do is take selected information from an Excel workbook and copy it to a closed Access file.
I've found this code:
Sub DAOFromExcelToAccess()
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim db As Database, rs As Recordset, r As Long
Set db = OpenDatabase("C:\FolderName\DataBaseName.mdb")
' open the database
Set rs = db.OpenRecordset("TableName", dbOpenTable)
' get all records in a table
r = 3 ' the start row in the worksheet
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("FieldName1") = Range("A" & r).Value
.Fields("FieldName2") = Range("B" & r).Value
.Fields("FieldNameN") = Range("C" & r).Value
' add more fields if necessary...
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End Sub
Which takes a stream of information until it ends and places it on a mdb file. What I would need is just to take five or six selected pieces of information and add them in. I've tried to simplify the code to something like this, but it does not work.
Sub DAOFromExcelToAccess()
Dim db As Database, rs As Recordset
Set db = OpenDatabase("C:\FolderName\DataBaseName\db1.mdb")
Set rs = db.OpenRecordset("TableName", dbOpenTable)
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("Name") = Sheet2.Cells(25, 1).Value
.Fields("Address") = Range(26, 1).Value
.Fields("Phone") = Range(27, 1).Value
.Update ' stores the new record
End With
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End Sub
Can anyone please help me out here?