excel to access in vb

[codeCruncher]

Hi! im a newbie here n im kinda stuck at dis function. This function is to import excel file(book1.xls) into a an access(97) table(name Service). I am using the code below but sumhow its not working. All i get is an error saying method 'range' of object '_global' failed. Can any1 help me detect the problem pleeeze...tq:)



Sub ConvertExcel()
Dim db As Database, rs As Recordset, r As Integer
Dim k

Set db = OpenDatabase("C:\RMIV\RMIV.mdb")
' open the database
Set rs = db.OpenRecordset("service", dbOpenTable)
' get all records in a table
r = 2 'skip 1; table header

k = ("A" & r)
Do While Len(Range(k).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("TRNNO") = Range("A" & r).Value
.Fields("TRNDATE") = Range("B" & r).Value
.Fields("VECNO") = Range("C" & r).Value
.Fields("CMR") = Range("D" & r).Value
.Fields("NMR") = Range("E" & r).Value
.Fields("LOCATION") = Range("F" & r).Value
.Fields("DRIVER") = Range("G" & r).Value
.Fields("SP_CODE") = Range("H" & r).Value
.Fields("INVNO") = Range("I" & r).Value
.Fields("INVDATE") = Range("J" & r).Value
.Fields("INVAMT") = Range("K" & r).Value
.Fields("NARRATION") = Range("L" & r).Value
.Fields("MARK") = Range("M" & r).Value
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
MsgBox "Finish!", vbInformation, "Import"

rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

End Sub

[r035198x]

Hi! im a newbie here n im kinda stuck at dis function. This function is to import excel file(book1.xls) into a an access(97) table(name Service). I am using the code below but sumhow its not working. All i get is an error saying method 'range' of object '_global' failed. Can any1 help me detect the problem pleeeze...tq:)



Sub ConvertExcel()
Dim db As Database, rs As Recordset, r As Integer
Dim k

Set db = OpenDatabase("C:\RMIV\RMIV.mdb")
' open the database
Set rs = db.OpenRecordset("service", dbOpenTable)
' get all records in a table
r = 2 'skip 1; table header

k = ("A" & r)
Do While Len(Range(k).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("TRNNO") = Range("A" & r).Value
.Fields("TRNDATE") = Range("B" & r).Value
.Fields("VECNO") = Range("C" & r).Value
.Fields("CMR") = Range("D" & r).Value
.Fields("NMR") = Range("E" & r).Value
.Fields("LOCATION") = Range("F" & r).Value
.Fields("DRIVER") = Range("G" & r).Value
.Fields("SP_CODE") = Range("H" & r).Value
.Fields("INVNO") = Range("I" & r).Value
.Fields("INVDATE") = Range("J" & r).Value
.Fields("INVAMT") = Range("K" & r).Value
.Fields("NARRATION") = Range("L" & r).Value
.Fields("MARK") = Range("M" & r).Value
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
MsgBox "Finish!", vbInformation, "Import"

rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

End Sub

Moved to VB forum