Ms Access Form export to Ms Excel

You have got a few different ways depending on what exactly you want to import, this may be multiple tables or just a single table.

You can possibly write queries in Access then you right click and export that to Excel.

You can also open Excel first then select Get Data from Database and connect to the Access database and import tables etc.

The closest you can get to your Form's existing layout without much code will most likely be using the output to command through VBA:

DoCmd.OutputTo acOutputForm, "YourForm", acFormatXLSX, "test123.xlsx", True

Select allOpen in new window

Unfortunately, it most likely will be a poor approximation of your form's layout.  Same thing when using the Excel button on the quick access toolbar.

If your form is in a simple datasheet view, you can use that same outputTo command to export the underlying table or query.

Otherwise the *closest* you will get to the layout you want will likely be a bit more work, by automating Excel.  It's more work but gives you a lot of flexibility.  The accepted answer here should give you a general idea:

https://www.experts-exchange.com/questions/28011280/Export-Access-data-to-Excel-using-Access-VBA.html

Hi,

This sound hardly doable since MS access forms can be linked to data sources, can be continuous (or not), can hold more or less complex VBA code (wich may not be compatible with MS Excel) as well as various graphic componments.

You'll need to provide more détails about what you want to achieve.

Thank you all for your comments.

I just uploaded 2 files: an image representing my form and  ms excel file, the one I want to get.
C--Local-vba_access-frm_report_1.jpg
C--Local-vba_access-frm_report.xlsx

What are you trying to achieve?  If you want to just provide a picture of the form, convert it to a report and export to PDF.  The only time you would want to recreate a "form" in Excel is if you wanted the user to be able to modify the data and that's a whole different problem.

Access' built in export to Excel is pretty poor.  Your best option if you just have to do this is to create a template.  Then your access code would use OLE automation to open Excel, Open the template, populate the cells that need data one at a time.  Save the file and close excel

You can do this, but it is going to involve automating Excel from within Access, and probably quite a bit of code.  You obviously already have a shell (template) built, but you would need to build that pieces which copy this template to a new worksheet, then use the CopyFromRecordset method to populate your Excel cells based upon one or more recordsets.  Are you using subforms on the main form to categorize your data?

Dale Fye I'm not using subforms on the main form but data from multiple tables.

PatHartman the need is to let users to modify the ms excel file.

No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I have recommended this question be closed as follows:

Accept: PatHartman (https:#a42469160)

If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.

broomee9
Experts-Exchange Cleanup Volunteer