Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2005
    Posts
    21

    Unanswered: Sorting EXCEL spreadsheet Automatically upon EXPORT from Access 2010

    I have a database and the user can click on the "EXPORT" button which works. This gives the user an EXCEL spreadsheet with all the data. But what I am trying to do is have the "LastName" column sorted alphabetically during EXPORT for the user. The users input name everyday so I want to ensure they have one last step to do when they get the spreadsheet. Here is what I have so far:

    Code:
    Private Sub btnExport_Click()
    Dim curPath As String
        Dim xlApp As Object
    
             curPath = CurrentProject.Path & "\Student - " & Format(Date, "mm-dd-yyyy" & "Sort.SortFields.Add Key:(B1:B2500")
             DoCmd.TransferSpreadsheet acExport, 10, "Student", curPath, -1
    
            Set xlApp = CreateObject("Excel.Application")
            xlApp.Workbooks.Open (curPath)
            xlApp.Visible = True
    End Sub
    The export worked when I did not have the "Sort.SortFields.AddKeyB1:B2500") line of code. But now I get this error message:
    "Run-time error '3027' Cannot update. Database or object is read-only. So not sure I have not formatted the code correctly or have it messed up completely. Thank you for any assistance.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    That appears to be within the Format() function? I'd probably try exporting a sorted query rather than a table.
    Paul

  3. #3
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    sort the query BEFORE you export it

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •