+ Reply to Thread
Results 1 to 3 of 3

Find duplicate field values and export those records to excel file

  1. #1
    Registered User
    Join Date
    06-25-2013
    Location
    Jacksonville, FL
    MS-Off Ver
    Excel 2007
    Posts
    30

    Find duplicate field values and export those records to excel file

    I need help with the code for functionality that loops through a recordset and finds all records that have the same value in one of the fields and then takes those records and saves them to an excel template. After it does that, I need it to go to the next unique value and save the group of records matching that value to its own excel file.

    I have the code for the export to excel and saving unique files but not for looping and grouping. As you can see, the whole sub is to save unique spreadsheets and them attach them to an email.

    Please Login or Register  to view this content.

  2. #2
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Find duplicate field values and export those records to excel file

    You can do a grouping on one SQL query to get the unique values and then loop through the recordset, creating a new SQL statement with each of the unique values in a where clause.

    something like

    SQL1 = Select ID from tbl_Table group by ID to recordset1
    SQL2 = Select * from tbl_Table where ID=recordset1.fields("ID").value

    looping will be done like

    While not recordset1.eof

    recordset1.movenext
    wend
    Hope this helps

    Sometimes its best to start at the beginning and learn VBA & Excel.

    Please dont ask me to do your work for you, I learnt from Reading books, Recording, F1 and Google and like having all of this knowledge in my head for the next time i wish to do it, or wish to tweak it.
    Available for remote consultancy work PM me

  3. #3
    Registered User
    Join Date
    06-25-2013
    Location
    Jacksonville, FL
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: Find duplicate field values and export those records to excel file

    Thanks for the reply! I am doing all of what you said here except instead of grouping I'm using a SELECT DISTINCT and writing all the unique values to an array and using each array value in a new recordset query.

    I am having one issue though - I am able to find all the unique values and run the second query. Once I run it, it finds all the records with that criteria and writes them to an excel file. It then creates an email and attaches the file. However, on the next iteration of the loop I get a runtime error 424 "Object required" on this line: "r.copyfromrecordset rsXL2"

    Any help? I suspect there is an issue of opening a new workbook on the second iteration, but I could be wrong.

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Export Excel records to seperate text file & compress it to protecte ZIP file
    By firedragon in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-03-2012, 10:47 AM
  2. Duplicate check on export of records
    By mbrady1973 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 12-28-2008, 07:53 PM
  3. [SOLVED] Find duplicate records in Excel 2003
    By Wayne in forum Excel General
    Replies: 1
    Last Post: 03-28-2006, 07:50 PM
  4. Can you find duplicate records in excel
    By Janet in forum Excel General
    Replies: 1
    Last Post: 04-25-2005, 10:06 PM
  5. Replies: 2
    Last Post: 03-03-2005, 02:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1