Import Excel spreadsheet to Access

[Nicky]

Hi,

I was trying to import a Excel spread sheet using VBA into Access, and here as follows is my 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:\try.mdb")
' open the database
Set rs = db.OpenRecordset("Bond_Info_Table", dbOpenTable)
' get all records in a table
r = 2 ' 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("Product_ID") = Range("A" & r).Value
.Fields("Company_ID") = Range("B" & r).Value
.Fields("Company_Name") = Range("C" & r).Value
.Fields("Week") = Range("D" & r).Value
.Fields("Month") = Range("E" & r).Value
.Fields("Year") = Range("F" & r).Value
.Fields("Maturity_Years") = Range("G" & r).Value
.Fields("Currency") = Range("H" & r).Value
.Fields("Reference_Treasury") = Range("I" & r).Value
.Fields("Treasury_Yield") = Range("J" & r).Value
.Fields("Re_Offer_Spread") = Range("K" & r).Value
.Fields("Re_Offer_Yield") = Range("L" & r).Value
.Fields("Spread_to_Libor") = Range("M" & 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

However, when I start to run it, it appears a msgbox: 'application definded or object definded error'...and i dont know how to deal with it!:dunno



Thanks very much for any comments!!!!!!:bow:

[Norie]

Where are you actually running this code?

By the way do you actually need to use code?

[Nicky]

it has another error when I put it in Access ( it highlights the 'range sentence') and I put it in Excel, that error disappears but this error comes.

Yes, I know we can use menu to import, but I just wnat to try with VBA:)

[Norie]

Nicky

So where are you trying to/want to run the code from?

If it's in Access VBA of course Range will cause an error, ranges don't exist in Access.

PS If you really want to use code do you really need to loop?:)

[XLGibbs]

What Norie is implying is that you can set this up using the macro wizard and do a transfer spreadsheet. You just specify the location of the excel file.

If it is a one time thing, you can also just right click the table area and choose ti Import Data.

You can also LINK the excel file to the database and run queries against it as if it was "there" in the database to begin with.

[akn112]

u must prepend all ranges with a . (period)
Could you also edit your post to include the VBA tags? Might help us figure out whats going on. Also you should check if you have the proper references set up.
Goto Tools->references-> Microsoft Excel ## Object Library