locked
Need Access VBA Help! RRS feed

  • Question

  • Hello,

    I'm attempting to export data from Access to an Excel document using VBA, but I've run into a few problems.  In our work database we input information on a form and then submit it (using a button with VBA coding) to a few different tables that store the information all in Access.

    What I'm wondering is if there's a way to also submit, daily, select information from the form to an Excel document.  I was thinking of doing it this way, merely because I can't think of an easier way.  Create a new table in the database grabbing the select information when the submit button is clicked, create a form displaying the daily submitted information, and include a button on this form that will export the data to Excel.  Is there a better way?

    If not, how can I do it?  I'm only slightly educated in VBA code...

    Thanks!

    Monday, December 7, 2015 8:17 PM

Answers

  • I'm not sure why you need to create a table; rather create a query to pull the data you want. Then use one line of code to export to Excel: DoCmd.TransferSpreadsheet.

    -Tom. Microsoft Access MVP

    Tuesday, December 8, 2015 3:13 AM
  • Is there any particular reason that you want to store data in Access and in Excel?  This sounds a bit redundant and possibly unnecessary.

    If you're interested in seeing reports of daily entries, you might find it more efficient to develop appropriate queries and perhaps Access report(s) to display the information you want to see.

    If you just have to have your data exported to Excel, then you should follow some of the advice and directions provided by others here.  You don't need an additional table to collect information already stored in your current tables.  All you need is a query that pulls the desired data together.

    Friday, December 11, 2015 8:02 AM

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Office, I'll move your question to the MSDN forum for Access

    http://social.msdn.microsoft.com/Forums/en-US/home?forum=accessdev&filter=alltypes&sort=lastpostdesc

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    George Zhao
    TechNet Community Support


    Please mark the reply as an answer if you find it is helpful.

    If you have feedback for TechNet Support, contact tnmff@microsoft.com.
    Tuesday, December 8, 2015 1:32 AM
  • I'm not sure why you need to create a table; rather create a query to pull the data you want. Then use one line of code to export to Excel: DoCmd.TransferSpreadsheet.

    -Tom. Microsoft Access MVP

    Tuesday, December 8, 2015 3:13 AM
  • From Access to Excel.  Please see these links.

    http://www.accessmvp.com/KDSnell/EXCEL_Export.htm

    http://www.erlandsendata.no/english/index.php?d=envbadacimportado

    http://www.erlandsendata.no/english/index.php?d=envbadacimportdao


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Friday, December 11, 2015 2:29 AM
  • Is there any particular reason that you want to store data in Access and in Excel?  This sounds a bit redundant and possibly unnecessary.

    If you're interested in seeing reports of daily entries, you might find it more efficient to develop appropriate queries and perhaps Access report(s) to display the information you want to see.

    If you just have to have your data exported to Excel, then you should follow some of the advice and directions provided by others here.  You don't need an additional table to collect information already stored in your current tables.  All you need is a query that pulls the desired data together.

    Friday, December 11, 2015 8:02 AM