X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Export To Excel Stops Working After Three Exports, Access 2016    
post Aug 1 2019, 08:57 AM

Posts: 158
Joined: 7-February 06


I have a form with many criteria selections which dictate the data displayed in a datasheet subform. To export the datasheet subform information, I have a button with the following code:

Private Sub cmdExport_Click()
On Error GoTo Proc_Err

    Dim sFileName As String
    Dim sFolder As String
    Dim sQuery As String
    'check for data in subform
    If Me.frmSubscriptionFilterSubform.Form.RecordsetClone.RecordCount = 0 Then Exit Sub
    sFileName = "SubscriptionFilterList_" & Format(CDate(DateValue(Now())), "yyyy-mm-dd") & ".xlsx"
    sFolder = GetFolderName(FolderEnding(Nz(DLookup("FolderExportFiles", "usys_global_defaults"), CurrentProject.Path)))
    sQuery = Me.frmSubscriptionFilterSubform.Form.RecordSource
    'populate tempFilterSubscription with current filter settings
    CurrentDb.Execute "DELETE * FROM tempFilterSubscription", dbFailOnError
    CurrentDb.Execute "INSERT INTO tempFilterSubscription " & sQuery, dbFailOnError
    'transfer tempFilterSubscription to Excel
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "tempFilterSubscription", sFolder & sFileName  ', True
    MsgBox "File exported to:" & vbCrLf & sFolder & sFileName, vbOKOnly + vbInformation, "Export Complete"

    Exit Sub      ' or function

    PostErrorMsg Err.Number, Err.Description, Nz(Me.Module, ""), Nz(Me.RecordSource, ""), GetWinUser()
    Resume Proc_Exit

End Sub

This runs fine for the first three exports and then the Excel file won't update according to the data in the subform. Any ideas why it stops updating after three exports?

Go to the top of the page
post Aug 2 2019, 03:23 PM

Posts: 10
Joined: 14-March 19

have you stepped through the code debugging line by line to test for the values of all of the various variables and conditions defined in your code? that is where I would start.

also, are you experiencing any errors?

last, can you upload a zipped copy of a sanitized version of dummy data including code? maybe with slight alterations to remove any organization/environment-specific functions like postmessage etc.
Go to the top of the page
post Aug 29 2019, 01:10 PM

Posts: 158
Joined: 7-February 06

Thank you for your response, my apologies for the late reply.

I have stepped through the code many times. I remove all records from a temp table and then repopulate the temp table using the RecordSource from a subform that lists records according to selected criteria. I've stepped through this process and printed out the value of the subform's RecordSource and made a working query from the resulting SQL - with all iterations of the criteria. This is why it's strange, no matter the order of criteria I select, it runs three exports and then stops updating the Excel file.

I do not experience any errors.

I will work on the sanitized version asap.

Go to the top of the page
post Oct 1 2019, 04:32 PM

Posts: 158
Joined: 7-February 06

I was using the same file name for each export. I had a string and date concatenated for the file name so it wouldn't overwrite until the next day. After adding time to the file name, it now creates a new file each time with the correct data.
Go to the top of the page
post Oct 1 2019, 07:18 PM

UtterAccess VIP
Posts: 7,014
Joined: 30-June 11

Glad you got it sorted out. We've all been there and done that!

Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc

All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    16th December 2019 - 02:19 AM