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

Importing Excel 2003 to Access 2003

P: 16
Hi all,
My name is Daniel
Iam trying to learn visual basic and I have been having problems building a marco in Access 2003 that will allow me to extract data from my Excel 2003 spreadsheet into my Access table (fields have the same name, i just need to change the data). The data in the spreadsheet is huge with 32 columns and about 7000 rows. I have tried to wirte code but i keep getting error messages.
Maybe i need to start with something with smaller data.

This is what i have done so far. Does anyone know how to help?


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


I would appriciate any help or suggestion
Many Thanks

Regards Daniel
Sep 11 '07 #1
Share this Question
Share on Google+
5 Replies


VBPhilly
P: 95
Hi all,
My name is Daniel
Iam trying to learn visual basic and I have been having problems building a marco in Access 2003 that will allow me to extract data from my Excel 2003 spreadsheet into my Access table (fields have the same name, i just need to change the data). The data in the spreadsheet is huge with 32 columns and about 7000 rows. I have tried to wirte code but i keep getting error messages.
Maybe i need to start with something with smaller data.

This is what i have done so far. Does anyone know how to help?


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


I would appriciate any help or suggestion
Many Thanks

Regards Daniel
Hello Daniel,
what error are you getting and on what code/line is it erroring at?
Your code looks ok to me, so far. Just need more detail.
Sep 11 '07 #2

P: 16
Well basically when i Press F8 to step through the code i get error signs after the first bit of code.

I'm not sure how to correct it tho?

What should i do?
Sep 12 '07 #3

VBPhilly
P: 95
Well basically when i Press F8 to step through the code i get error signs after the first bit of code.

I'm not sure how to correct it tho?

What should i do?
an error could be many things. usually when you get an error, a messagebox comes up saying what the error was.
also, note the line of code the error 'breaks' on.
post what you find.
Sep 13 '07 #4

codeCruncher
P: 8
Hi Daniel! Im having the same problem with my code. Have u tried adding the reference?
Sep 14 '07 #5

P: 16
Many thanks...I have tried adding the reference yet?

I guessing i have made errors cause the data i am trying to import is very huge
Sep 19 '07 #6

Post your reply

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