Combine Fields from Multiple Records into One Long String

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,092
Office Version
365
Platform
Windows
I have an Excel macro that I wrote years ago that will take all the values from a certain column on a spreadsheet and concatenate them into one long string, with text qualifiers and delimiters of my choosing. It could convert this:
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p> </o:p>
joe@abc.com
tom@abc.com
jill@xyz.com
<o:p> </o:p>
to something like this:
joe@abc.com;tom@abc.com;jill@xyz.com
<o:p> </o:p>
I use this for two distinct purposes:
1. Combine a list of email addresses I want to send something to;
2. Use to build criteria for a SQL statement (i.e. using “IN”)
<o:p> </o:p>
A client of mine has an Access database that has a Contact table that contains an email address field. On occasion, they would like to email a subset of that table (can easily select which records with a query). Currently, they export the results of the query to Excel and use my macro to create the email address list. I would like to make this more dynamic, and bypass the Excel step altogether, i.e. have Access combine the list (and maybe export to single record text file).
<o:p> </o:p>
I imagine it is going to require VBA (which I have done a lot of), but I haven’t ever really tried combining field values from multiple records down into a single record. Does anyone have any good code links or tips?
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,092
Office Version
365
Platform
Windows
Well, its a bit different, as that link combines multiple fields within a single record, whereas I am combining a single field from multiple records.

In the the instance you worked on, you can get away with a basic User Defined Function instead of having to use RecordSets. I admit that RecordSets are not my forte, by I think that part I can probably work my way through. The more difficult part is using VBA to write the results to a single record text file. I hardly ever try to create text files directly from Access VBA.

I was just wondering/hoping if someone had already done something like this before I go ahead and struggle through it and recreate the wheel here.
 

revans

Well-known Member
Joined
Apr 5, 2010
Messages
576
Try this. You'll want to make change the sql variable to return the column you want and the !Myfield to the name of the field.

Code:
Public Function MakeList() As String
Dim rst As adodb.Recordset
Dim sql As String
Dim temp As String
sql = "SomeQueryThatListsTheRecordsYouWant"
Set rst = New adodb.Recordset
With rst
  .Open sql, CurrentProject.AccessConnection, adOpenDynamic, adLockOptimistic
  If Not (.EOF And .BOF) Then
    .MoveFirst
    Do
      temp = temp & ";" & ![MyField]
      .MoveNext
    Loop Until .EOF
  End If
  .Close
End With
Set rst = Nothing
If Len(temp) > 0 Then 'ditch the first and last delimiter
  temp = Mid(temp, 2, Len(temp) - 1)
End If
MakeList = temp
End Function
There are various ways to write a text file to disc. I like the filesystemobject. So you could use this.

Code:
Public Sub WriteList(strFileName As String)
'needs reference to Microsoft Scripting Runtime
 Dim fso As New FileSystemObject
Dim ts As TextStream
Set ts = fso.CreateTextFile("C:\" & strFileName)
  ts.Write (MakeList)
Set ts = Nothing
Debug.Print "done"
End Sub
You could call it from the immediate window (or however you like) like this

Code:
call writelist("testorama.txt")
hth,

Rich
 

gauntletxg

Well-known Member
Joined
Jul 15, 2008
Messages
636
Something like this maybe? You can pass your query and the field you want to combine to the function

Code:
Function CreateList(strSource As String, strField As String, strDelim As String) As String
Dim db As Database, rs As Recordset, temp As String, i As Long

On Error Resume Next
i = DCount(strField, strSource)
On Error GoTo 0
If i > 0 And Not Err Then
    Set db = CodeDb
    Set rs = db.OpenRecordset(strSource, dbOpenSnapshot, dbReadOnly, dbReadOnly)
    With rs
        .MoveFirst
        Do Until .EOF
            temp = temp & rs(strField) & strDelim
            .MoveNext
        Loop
        .Close
    End With
    Set rs = Nothing
    Set db = Nothing
End If

If temp <> vbNullString Then temp = Left(temp, Len(temp) - 1)
CreateList = temp
End Function
edit: pretty much the same thing as revans
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,092
Office Version
365
Platform
Windows
Thanks to the both of you.
I hope to get some time to start playing around with it later today. I'll let you know how it goes.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,092
Office Version
365
Platform
Windows
Thanks Marbles, that code is pretty much along the same lines as the other two above.

Building the string was easier than expected, I just wasn't sure about writing it out to a file (just because I have never written out to a text file before directly from Access VBA without using TransferText, TransferSpreadsheet, etc).

Borrowing from the posts made, I was able to create something to work for me. I added an additional argument for a Text Qualifer, and use DAO recordsets (just because that is what I am used to). Here is what I came up with.

First the code to build the string:
Code:
Function CombineList(strSource As String, strField As String, strDelim As String, Optional strTextQual As Variant) As String
'   Combines a single column from multiple records into a single string
'   Arguments
'       strSource = name of query or table
'       strField = name of field you want to combine
'       strDelim = delimiter to use to separate each value
'       strTextQual = text qualifer to use around each field (optional)
 
    Dim myDB As DAO.Database
    Dim myRS As DAO.Recordset
    Dim myRecCount As Long
    Dim myTempString As String
    
    Set myDB = CurrentDb
    Set myRS = myDB.OpenRecordset(strSource, dbOpenDynaset)
    
    myRecCount = DCount(strField, strSource)
    If myRecCount > 0 Then
        With myRS
            .MoveFirst
            Do While Not .EOF
                myTempString = myTempString & strTextQual & myRS(strField) & strTextQual & strDelim
                .MoveNext
            Loop
        End With
    End If
    Set myRS = Nothing
    Set myDB = Nothing
    If Len(myTempString) > 0 Then CombineList = Left(myTempString, Len(myTempString) - 1)
    
End Function
Next, the code to write out the text file:
Code:
Public Sub MyWriteList(strFullFileName As String, strExportString As String)
'   Export a built string to a text file
'   (include reference to Microsoft Scripting Runtime)
     
    Dim fso As New FileSystemObject
    Dim ts As TextStream
    Set ts = fso.CreateTextFile(strFullFileName)
    ts.Write (strExportString)
    Set ts = Nothing
    MsgBox "File exported to:" & vbCrLf & strFullFileName
    
End Sub
Now, to run it all, I created a Form with 5 text boxes where they can enter the 4 arguments for my function, and the export file name for the last procedure. I then added a Command Button to the Form to run it all, which has the following VBA code:
Code:
Private Sub cmdCreateFile_Click()
'   Creates a text file that combines all records from a single field into one line with delimiters and text qualifiers
 
    Dim myCombinedString As String
    
'   Using four text boxes on form, build string
    myCombinedString = CombineList(txtQuery, txtField, txtDelimiter, txtTextQual)
    
'   Write out to file, using string calculated above and file name from text box on form
    Call MyWriteList(txtFullFileName, myCombinedString)
 
End Sub
Everything appears to work well.

Thanks for all the help!
 

drew8631

New Member
Joined
Apr 29, 2011
Messages
3
Hi Guys,

I'm trying to write a similar code for my access project.
I understand that you first have to build the string and then use some method to export it to a text file or where ever you want it to go.
Looking through this thread, I've found several different codes to create the string which seems to work fine. I simply copied and pasted the vb code into a module in access.
The problem I'm having is getting the string to export to a text file.
Joe4's code would work perfect for me, but I can't figure out how to "include reference to Microsoft Scripting Runtime".
I'm guessing what I'm looking for is some code I can stick into a command button on a form to export my created string into a text file.

Your help is greatly appreciated.

Thanks,

Andrew
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,092
Office Version
365
Platform
Windows
Joe4's code would work perfect for me, but I can't figure out how to "include reference to Microsoft Scripting Runtime".
If you go into the VB Editor, go to the Tools menu and select References. From there, scroll down the list and find "Microsoft Scripting Runtime" and check that box.

Is that what you are looking for?
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,999
Messages
5,508,705
Members
408,690
Latest member
Lip Renan

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top