Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel To Access: Transfer multiple rows from excel to access
Hi All,
How can I transfer a Bock of data to Access from excel by clicking a "Submit" button? eg: I have designed a "Submit" button on the excel sheet that exports all of the student data into access in a single row... BUT I want to insert all this data in access in seperate rows. Here is what My excel table looks like: Student_ID Subjects Grades 123456 Eng A 123456 Hist B 123456 Math B+ 123456 Bio B- So, once we click "Submit" I want the data displayed above to go to access. NOTE: it should look exactly the same in access, each row in a seperate row in access What I have now: I can get this data into access but all in one single row, which looks like this: Student_ID Subjects Grades Subjects2 Grades2 Subjects3 Grades3 123456 Eng A Hist B Math B+ What I want: Student_ID Subjects Grades 123456 Eng A 123456 Hist B 123456 Math B+ 123456 Bio B- So basically It should look the same in access like it looks in excel (transfer the entire data shown below in access at the same time, each in new row). Hope I made it clear Thanks in advance |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel To Access: Transfer multiple rows from excel to access
Use the Access object model and the "transferspreadsheet" command. You must
have the Access object reference, a license for Access, and use vba. Something like this (Not tested or compiled...) Dim objAccess as new Access.Database objAccess.Open........(database name, etc.) objAccess.DoCmd.TransferSpreadsheet........ (you'll need to have the data in a single sheet or a named range...) objAccess.Close Again, very rough. You're basically "becoming" Access and giving it instructions to draw from your spreadsheet. There may be an issue with the spreadsheet being open when you do this. If that's the case, try doing the whole thing from Access. -- Jim "sam" wrote: Hi All, How can I transfer a Bock of data to Access from excel by clicking a "Submit" button? eg: I have designed a "Submit" button on the excel sheet that exports all of the student data into access in a single row... BUT I want to insert all this data in access in seperate rows. Here is what My excel table looks like: Student_ID Subjects Grades 123456 Eng A 123456 Hist B 123456 Math B+ 123456 Bio B- So, once we click "Submit" I want the data displayed above to go to access. NOTE: it should look exactly the same in access, each row in a seperate row in access What I have now: I can get this data into access but all in one single row, which looks like this: Student_ID Subjects Grades Subjects2 Grades2 Subjects3 Grades3 123456 Eng A Hist B Math B+ What I want: Student_ID Subjects Grades 123456 Eng A 123456 Hist B 123456 Math B+ 123456 Bio B- So basically It should look the same in access like it looks in excel (transfer the entire data shown below in access at the same time, each in new row). Hope I made it clear Thanks in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HELP PLEASE: Populate multiple excel rows in access at the same ti | Excel Programming | |||
Exporting multiple rows from access to excel, each in a new row | Excel Programming | |||
Delete Rows of Data in multiple Excel Sheets upon importing to Access | Excel Programming | |||
Access transfer to Excel | Excel Programming | |||
ADO Transfer from Excel to Access | Excel Discussion (Misc queries) |