Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2014
    Posts
    2

    Unanswered: TransferSpreadsheet with unique field

    Hi

    I have been battling with this code for days now and really searched everywhere. Please can someone assist me with this.

    I am working with Access 2010 and have a query called"Commission Statement - 1Life Broker Services" and I would like to export this to excel and create a separate workbook for every unique filed called "brokerage"

    My code below so far exports and saves the xls file per "brokerage" as it should but in each workbook is ALL the data and it is not filtered to only export per "brokerage"

    Any help would be great and appreciated


    Private Sub Commission_Excel_Click()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim MyFileName As String
    Dim temp As String
    Dim mypath As String
    Dim xlSheet As Object


    mypath = "C:\Users\Richard\Desktop\1Life Broker Services\Automatic Reports\Commission Excels\Commission Excel - "

    Set db = CurrentDb()

    Set rs = db.OpenRecordset("SELECT distinct [Brokerage] FROM [Commission Statement - 1Life Broker Services]", dbOpenSnapshot)

    Do While Not rs.EOF

    temp = rs("brokerage")
    MyFileName = rs("Brokerage") & ".xls"

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Commission Statement - 1Life Broker Services", mypath & "" & MyFileName & Format(Date, "dd/mm/yyyy") & ".xls"

    DoEvents

    rs.MoveNext
    Loop


    rs.Close
    Set rs = Nothing
    Set db = Nothing

    End Sub

  2. #2
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    You"ll need a nested loop to do this. Right now you've got the outer loop which grabs the unique brokerages. You need to apply rs![brokerage] as criteria to Commission Statement - 1Life Broker Services before exporting.

    Open Commission Statement - 1Life Broker Services in SQL view and copy the SQL.

    In your code create another loop within the first loop

    create a recordset (rsDetail) within the new loop and set it to the SQL from your query with WHERE brokerage = '" & rs![brokerage] & "'" added

    export rsDetail to excel

  3. #3
    Join Date
    Jul 2014
    Posts
    2
    Quote Originally Posted by rogue View Post
    You"ll need a nested loop to do this. Right now you've got the outer loop which grabs the unique brokerages. You need to apply rs![brokerage] as criteria to Commission Statement - 1Life Broker Services before exporting.

    Open Commission Statement - 1Life Broker Services in SQL view and copy the SQL.

    In your code create another loop within the first loop

    create a recordset (rsDetail) within the new loop and set it to the SQL from your query with WHERE brokerage = '" & rs![brokerage] & "'" added

    export rsDetail to excel
    Thanks for the reply Rogue.

    VBA is new to me is it at all possible for you to throw a dog a bone and adapt my code with your suggestions as I have tried and actually dont know how to do it myself. I know its a hassle but please see what you can do to put me onto the right track.

    Thanks

    Richard

Tags for this Thread

Posting Permissions

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