button to import data from excel to access table

[bkwjr44]

I have zero experience with programming and I have managed to get this far. I am hoping that some feedback on the following code will get this to work correctly.

Basically I have created a button that when pressed will Import data from certain cells in an excel spreadsheet into a table in access.

The excel file will always be the same name..it just gets over written daily and the starting cell will always be B-25.

The error I am getting is Compile Error: user-defined type not defined.
I did some research and this seems like a pretty vague error. So any help is greatly appreciated.

Dim myRec As DAO.Recordset
Dim xlApp As Excel.Application
Dim xlWrksht As Excel.worksheet
r As Long

Set myRec = CurrentDb.OpenRecordset("DailyActivityReport")
Set xlApp = CreateObject("Excel.Application")
Set xlWrksht = xlApp.Open("C:\GiftData\GFTDLY227.xls").Worksheets("DAILY 1")
r = 25 ' the start row in the worksheet
Do While Len(Range("B" & r).Formula) > 0
' repeat until first empty cell in column B
With myRec
.AddNew
.Fields("CardNumber") = Range("B" & r).Value
.Fields("TransAmount") = Range("F" & r).Value
.Fields("Request") = Range("D" & r).Value
.Fields("ResponseCode") = Range("G" & r).Value
.Fields("Authcode") = Range("O" & r).Value
.Fields("TermTxn") = Range("Q" & r).Value
.Fields("TimeStamp") = Range("K" & r).Value
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing


Please keep in mind I really did just put this together using google to research what I wanted to accomplish and have very limited experience.

Thank you

 

[ghudson]

Using the TransferSpreadsheet function would be a lot easier. Search the help files or the forum for more info.

 

[bkwjr44]

OK but I have a couple of Questions...
I am only importing certain columns and I am not sure what the ending cell will be
how do I accomplish that using the transferspreadsheet

 

[ghudson]

Import all data from the spreadsheet into a temp table A. Then append the data into your table B. That also allows you to do some testing of the records in the temp table A to ensure the data is ok before you append or update the records in your table B.

 

[bkwjr44]

Thanks for that suggestion. I tried and to no avail....

At the top of this spread sheet is a report that when imported throws things off. I am unable to get all of the data.

Any other suggestions??

I do appreciate it!

 

[ghudson]

You can define an import range with the TransferSpreadsheet function. You need to know the worksheet name.