By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,962 Members | 1,998 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,962 IT Pros & Developers. It's quick & easy.

excel to access in vb

codeCruncher
P: 8
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
Sep 11 '07 #1
Share this Question
Share on Google+
1 Reply


10K+
P: 13,264
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
Sep 11 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.