Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MS Access 2013:  Export Multiple Access Tables to Same Excel Spreadsheet (Multiple Sheets)

Posted on 2013-12-22
7
Medium Priority
?
4,716 Views
Last Modified: 2013-12-24
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\Access Table Dump.xlsx

Can anyone help with this one?
Big-Test.accdb
0
Comment
Question by:mdstalla
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 31

Accepted Solution

by:
hnasr earned 1500 total points
ID: 39735243
Try: This command for Office 2013.

Excel file: D:\xl.xlsx
Use sheet names not present in workbook.

CurrentDb.Execute "SELECT * INTO [Excel 12.0;DATABASE=" _
    & "D:\xl.xlsx" & "].[" & "InputT1" & "] FROM [" & "InputT1" & "]"

Repeat for other tables.

Access reported error when appending.

Check database.
Big-Test-1.accdb
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39735415
using a click event of a button

private sub cdmExport_click()

docmd.transferspreadsheet acexport,10, "InputT1", "C:\Users\Matt\Desktop\Access Table Dump.xlsx", True, "InputT1"

docmd.transferspreadsheet acexport,10, "InputT2", "C:\Users\Matt\Desktop\Access Table Dump.xlsx", True, "InputT2"

docmd.transferspreadsheet acexport,10, "SentenceOptionT1", "C:\Users\Matt\Desktop\Access Table Dump.xlsx", True, "SentenceOptionT1"

docmd.transferspreadsheet acexport,10, "SentenceOptionT2", "C:\Users\Matt\Desktop\Access Table Dump.xlsx", True, "SentenceOptionT2"

docmd.transferspreadsheet acexport,10, "NotesT1", "C:\Users\Matt\Desktop\Access Table Dump.xlsx", True, "NotesT1"

docmd.transferspreadsheet acexport,10, "NotesT2", "C:\Users\Matt\Desktop\Access Table Dump.xlsx", True, "NotesT2"


end sub
0
 

Author Comment

by:mdstalla
ID: 39737223
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!
0
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39737299
@ mdstalla

what did not work ? what is the problem?
0
 
LVL 31

Expert Comment

by:hnasr
ID: 39737332
Try this version:
Added code to delete previous sheets.
Big-Test-2.accdb
0
 

Author Comment

by:mdstalla
ID: 39737335
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.
0
 
LVL 31

Expert Comment

by:hnasr
ID: 39737490
For the new requirement, you need to start a new question.
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question