Results 1 to 10 of 10
  1. #1
    jpkeller55 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Sep 2010
    Posts
    169

    Multiple exports to Excel from Access 2010 based on criteria from another table

    Hello, I am trying to use the DoCmd.TransferSpreadsheet command to export a query to Excel multiple times based on a field value from another table. My code is attached as well as a sample DB. I have the VBA code attached to the on click property of the button on Form1. It is not working for me. I get an error message "Run-time error '31532': Microsoft Access was unable to export the data". Any help in solving would be appreciated! Thanks, Jim



    Code:
    Private Sub TJC1_Click()
        Dim dbCurr As DAO.Database
        Dim rsCurr As DAO.Recordset
        Dim qdfCurr As DAO.QueryDef
        Dim strFolder As String
        Dim strSQL As String
      
            strFolder = "C:\Users\jpkeller\Documents\Roster\"
        Set dbCurr = CurrentDb()
        Set rsCurr = dbCurr.OpenRecordset("SELECT SVCBR_ID FROM SB")
        Set qdfCurr = dbCurr.CreateQueryDef("qryTemp")
         Do While rsCurr.EOF = False
            strSQL = "SELECT SVCBR_ID, CITY, STATE FROM RosterDetail WHERE SVCBR_ID = '" & rsCurr!SVCBR_ID & "'"
            qdfCurr.SQL = strSQL
            qdfCurr.Close
            DoCmd.TransferSpreadsheet acExport, , "qryTemp", strFolder & "Details for " & rsCurr!SVCBR_ID & ".xlsx", True
            rsCurr.MoveNext
        Loop
            rsCurr.Close
        Set rsCurr = Nothing
        Set qdfCurr = Nothing
        Set dbCurr = Nothing
         
    End Sub
    Attached Files Attached Files
    Last edited by jpkeller55; 08-25-2016 at 02:58 PM.

  2. #2
    jpkeller55 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Sep 2010
    Posts
    169
    Solved it...stupid mistake.
    Code:
    strSQL = "SELECT SVCBR_ID, CITY, STATE FROM RosterDetail WHERE SVCBR_ID = " & rsCurr!SVCBR_ID & ""
    Needed to take out the single quotes

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Code:
    strSQL = "SELECT SVCBR_ID, CITY, STATE FROM RosterDetail WHERE SVCBR_ID = " & rsCurr!SVCBR_ID & ""
    Since "sCurr!SVCBR_ID" appears to be numeric, you could also drop the text in RED.... (doesn't hurt to have it , but not necessary)

    (the single quotes are delimiters for text)

    Good job of troubleshooting!
    And good luck with your project.

  4. #4
    jpkeller55 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Sep 2010
    Posts
    169
    OK, now I realize I have another problem. I need the exported excel file to be filtered for DaysSinceLastShip <91 and then the result to be sorted by City, then State. I have read that the DoCmd.TransferSpreadsheet command cannot handle that. Anybody know how to do that? Reattaching the sample DB to original post. Thanks!

  5. #5
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Shouldn't have anything to do with DoCmd.TransferSpreadsheet. Modify the SQL statement......
    See attached
    Attached Files Attached Files

  6. #6
    jpkeller55 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Sep 2010
    Posts
    169
    Steve, thank you so much. That did the trick. I like the way this is formatted too...makes it easy to follow the logic. Much appreciated!

  7. #7
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You're welcome.

    Good luck with your project.....

  8. #8
    jpkeller55 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Sep 2010
    Posts
    169
    Original post is solved, but wondering if anybody has any tips for putting some formatting on the excel file that gets generated. That is, like a bold font or colored cells.

  9. #9
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    That would require Automation.

    What I have done is to have code in Access that opens the Excel spreadsheet, adds a couple of rows, changes the header row to bold font, adds a totals row at the bottom of the data, then closes & saves the spreadsheet.
    I am exporting a report, so I use the "OutputTo" option of DoCmd. Then I call the sub to format the spreadsheet.

  10. #10
    jpkeller55 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Sep 2010
    Posts
    169
    OK, thanks Steve...I will mess around with that.
    Last edited by jpkeller55; 08-26-2016 at 03:18 PM.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 04-07-2016, 04:36 AM
  2. Replies: 22
    Last Post: 12-29-2015, 10:41 PM
  3. Replies: 9
    Last Post: 05-13-2014, 08:22 AM
  4. Replies: 1
    Last Post: 03-11-2014, 09:29 AM
  5. Problem with Query exports to Excel
    By nokeefe in forum Access
    Replies: 9
    Last Post: 12-07-2011, 12:50 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums