Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2021
    Posts
    23

    Export to Excel: Merge different entries of the same ID of Access-Query into one Cell in Excel-Sheet

    Hello guys,

    I established a connection from my MS Access DB to an Excel-File and want to open it via a Button with VBA. That works.
    I want to export some queries from MS Access to Excel (this also worked fine so far) but here is my problem:

    The queries have multiple recordsets with the same ID from a table (that has been selected so it is included in the query). It kind of looks like this:

    ID = 1, Entry 1 BUT I want to have it automatically changed in the Excel-Sheet so that it looks like this:
    ID = 1, Entry 2
    ID = 1, Entry 3
    ID = 2, Entry 4
    ID = 2, Entry 5
    ID = 2, Entry 6

    ...

    BUT I want to have it automatically changed in the Excel-Sheet so that it looks like this (all entries in one cell, so that ID is only shown once). I Also want to merge several appropiately changed queries in registers to one Excel-register that matches with the same ID. Is there a problem to that?

    ID = 1, Entry 1
    Entry 2
    Entry 3
    ID = 2, Entry 4
    Entry 5
    Entry 6

    I'd be grateful for all helpful advise Thank you guys, you are doing a tremendous work!

    Greetings,



    FinisherProgrammer21

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You can use one of the concatenation functions available, I like theDBGuy's SimpleCSV (http://www.accessmvp.com/thedbguy/co...itle=simplecsv) as seems the fastest but you can also try Allen's (http://allenbrowne.com/func-concat.html) or have a look at modJoinFromArray on my website (http://forestbyte.com/vba-code-samples/). Use vbCrLf as the delimiter so Entry 1, Entry 2 will be on new lines inside the Excel cell.

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    Join Date
    Jan 2021
    Posts
    23
    Quote Originally Posted by Gicu View Post
    You can use one of the concatenation functions available, I like theDBGuy's SimpleCSV (http://www.accessmvp.com/thedbguy/co...itle=simplecsv) as seems the fastest but you can also try Allen's (http://allenbrowne.com/func-concat.html) or have a look at modJoinFromArray on my website (http://forestbyte.com/vba-code-samples/). Use vbCrLf as the delimiter so Entry 1, Entry 2 will be on new lines inside the Excel cell.

    Cheers,
    Vlad

    Thank you, I solved my problem I used the Excel function to Custom a List and used the Code "Table.Column" to write all values that matches the same ID into a customed List. And then, I used a line break which you can select as a delimiter
    I've been on this forum a while now and I just wanted to provide you guys with the source video that helped me.

    https://www.youtube.com/watch?v=nJ7LzwiSwnw

    So that I can finally also contribute and not only ask for help. So guys, wish you all a good day and stay tuned!

    Greetings,

    FinisherProgrammer21

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

Similar Threads

  1. export access queries to excel sheet
    By akapag22 in forum Import/Export Data
    Replies: 1
    Last Post: 06-30-2015, 12:49 AM
  2. Replies: 1
    Last Post: 10-28-2013, 12:32 PM
  3. Export query or report to preformatted excel sheet
    By andrewmo in forum Import/Export Data
    Replies: 3
    Last Post: 08-13-2013, 10:11 AM
  4. Export Query to specific excel sheet
    By Perceptus in forum Queries
    Replies: 2
    Last Post: 12-20-2012, 11:50 AM
  5. Replies: 1
    Last Post: 04-17-2011, 04:16 PM

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