Export form and subform fields to excel via button

from my iPad, so this is generic.

First thing I would do is create a query (qry_FormName_ExportToExcel) which contains the table that your main form is based upon and the table that the subform is based upon, joined by whatever field is appropriate.  I would then add a criteria on the primary key field of that query that looks like:

= Forms!yourFormname!txt_PKID

obviously, replace "yourFormName" with the name of your form and replace txt_PKID with the name of the control on your main form which contains the autonumber field associated with the current record.

It would be something like:

Private Sub cmd_Export_Click

    dim strFilename
    me.dirty = false    'this will save the current record
    
    strFilename = "C:\Somepath\appname\backups\ID_" & Format(me.txt_PKID, "0000") & "_" & format(now(), "yyyy-mm-dd_hh-mm") & ".xlsx"
    docmd.OutputTo acOutputQuery, "qry_FormName_ExportToExcel", acFormatXLSX, strfilename, true 

End Sub

Select allOpen in new window

This example would save the file with a name that looks like:

C:\Somepath\appname\backups\ID_0001_2018-08-22_14-30.xlsx

You can change that however you would like, but this would give you the ability to sort by ID and would see the date the record was created in the file name.

Hi Dale, this was very helpful thank you very much. It took me a while to put it all together with my basic knowledge but got it to work. It looks like creating the code based on a query uses much less code than the other complete VBA solutions I have seen and very easy for me to change the query.

However, the only problem is this creates a new Excel file each time the button is clicked. The goal was to append to an Excel spreadsheet. What do I need to change to get it to do that? Also, it opens the spreadsheet each time. I just need it to append it silently if possible.

I hope you can help and thanks again.

Dennis,

sorry, I missed the part about the add to an existing excel spreadsheet.

to do that, you are going to have to use the TransferSpreadsheet method, or (the method I prefer) use Office automation to actually open Excel from Access, check for an empty spreadsheet, add one if there isn't one, and then use the recordset CopyFromRecordset method to paste the data into the data into Excel.  Unfortunately, I'm slammed right now, so am unable to assist with this at the moment, although I'll keep your post in my email as a reminder to check back in.

I would recommend you start by looking into the TransferSpreadsheet method, that is the easiest, and might meet your needs.

Dale

Hi Dale, thanks for the update. Unfortunately after searching the TransferSpreadsheet method and CopyFromRecordset I can not find a way to use it in your code so stuck again.

As mentioned, I'm new to VBA and Access so my knowledge is limited to adding a script and minor editing. I'm sure the extra code is simple for most developers. If you get a chance it would be great to know what I need to add to your code or if someone else had any ideas?

Dennis