mdstalla
asked on
MS Access 2013: Export Multiple Access Tables to Same Excel Spreadsheet (Multiple Sheets)
MS Access 2013: I have a database with 6 tables. It would be very useful to me if I could get a code that, upon the click of a Control Button (Command15), would automatically export each of these tables to an MS Excel Spreadsheet (each Table is placed into a separate sheet). Attached is my Database and the 6 tables I would like exported are:
InputT1
InputT2
SentenceOptionT1
SentenceOptionT2
NotesT1
NotesT2
The Excel Spreadsheet (Path) is:
C:\Users\Matt\Desktop\Acce
Can anyone help with this one?
Big-Test.accdb
InputT1
InputT2
SentenceOptionT1
SentenceOptionT2
NotesT1
NotesT2
The Excel Spreadsheet (Path) is:
C:\Users\Matt\Desktop\Acce
Can anyone help with this one?
Big-Test.accdb
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Capricorn1. Your codes did not work.
Hnasr: You codes worked-- however, I would like it if every time Access Exports data to that Spreadsheet, it replaces any/all data that my already exist (say, from a previous export).
Could you take a look at it and adjust your coding? You are the MAN!
Hnasr: You codes worked-- however, I would like it if every time Access Exports data to that Spreadsheet, it replaces any/all data that my already exist (say, from a previous export).
Could you take a look at it and adjust your coding? You are the MAN!
@ mdstalla
what did not work ? what is the problem?
what did not work ? what is the problem?
ASKER
I don't know... I plugged them in, hit the Control Button, opened the Spreadsheet and; Nada!
Regardless, it ends up that there's some refining to do with the ultimate function I had in mind. If you've got time and are willing to put up with me; let me ask you this question (related):
I'd still like to do what you were helping me out with before… only, the sheets within Excel cannot be brand new every time. My spreadsheet (and its sheets) are already made, and any Access exports to them should only re-write data, not create entirely new spreadsheets.
Now here's the real kicker: Upon exporting from Access to Excel, I would like the layout of Column’s and Rows to be reversed. Example: Records exported from Access that would normally be displayed as Column’s: 'A, B, C and D etc.' should show up as Rows in Excel: 1, 2, 3 and 4, etc.
Buddy, if you can give me direction on how to do this; I will give you accolades for the next 3 weeks straight! Shit, I’ll Western Union you $50!
Thanks.
Regardless, it ends up that there's some refining to do with the ultimate function I had in mind. If you've got time and are willing to put up with me; let me ask you this question (related):
I'd still like to do what you were helping me out with before… only, the sheets within Excel cannot be brand new every time. My spreadsheet (and its sheets) are already made, and any Access exports to them should only re-write data, not create entirely new spreadsheets.
Now here's the real kicker: Upon exporting from Access to Excel, I would like the layout of Column’s and Rows to be reversed. Example: Records exported from Access that would normally be displayed as Column’s: 'A, B, C and D etc.' should show up as Rows in Excel: 1, 2, 3 and 4, etc.
Buddy, if you can give me direction on how to do this; I will give you accolades for the next 3 weeks straight! Shit, I’ll Western Union you $50!
Thanks.
For the new requirement, you need to start a new question.
private sub cdmExport_click()
docmd.transferspreadsheet acexport,10, "InputT1", "C:\Users\Matt\Desktop\Acc
docmd.transferspreadsheet acexport,10, "InputT2", "C:\Users\Matt\Desktop\Acc
docmd.transferspreadsheet acexport,10, "SentenceOptionT1", "C:\Users\Matt\Desktop\Acc
docmd.transferspreadsheet acexport,10, "SentenceOptionT2", "C:\Users\Matt\Desktop\Acc
docmd.transferspreadsheet acexport,10, "NotesT1", "C:\Users\Matt\Desktop\Acc
docmd.transferspreadsheet acexport,10, "NotesT2", "C:\Users\Matt\Desktop\Acc
end sub