Append character to cell & export column range as a 1 line text file

[mkrautler]

I have a csv file that I need to manipulate so that I can imported into another application. I need to add a comma & a space to the end of each cell with data in it (minus the header row) & then export the entire range into a single line in a text file. For example, the contents of column A in the attached file should be exported into a txt file looking like below. The amount & data in column A will always change and I am having issues even saving the file as a text file. When I use the code below it will create a text file but with no data in it, any help would be greatly appreciated!

105J1V3, 10RHPV2, , 10T0C53, 10WXMM3, 115J1V3, 11WXMM3, 123B043, 135J1V3, 13GXMN3, 1485Z43, 14C9LN3, 15605K3

Private Sub Workbook_Open()

Dim rng As Range
    Set rng = ActiveSheet.UsedRange
    
    Set rng = rng.Offset(1).Resize(rng.Rows.Count - 1)
    
    rng.Select
    
Dim c As Range
    For Each c In Selection
    If c.Value <> "" Then c.Value = c.Value & ", "
Next

'object to use as folder
Dim fld As Object
Set fld = CreateObject("Scripting.FileSystemObject")

'using create text file method
Dim myFile As Object
Set myFile = fld.CreateTextFile("C:\temp\MKrautler\Tags.txt", True)
    
End Sub

 

[Domenic]

After you've created a text stream using the CreateTextFile method, you'll need to write to the text stream...

myFile.write Join(Application.Transpose(rng))

Then you should close the text stream...

myFile.Close

However, here's another way...

Option Explicit

Private Sub Workbook_Open()

    ExportRangeToTextFile
   
End Sub

Private Sub ExportRangeToTextFile()

    Dim data As Variant
    With ThisWorkbook.Worksheets("Sheet1") 'change the sheet name accordingly
        data = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)).Value
    End With
   
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
   
    Dim ts As Object
    Set ts = fso.createtextfile("C:\temp\MKrautler\Tags.txt", True)
   
    ts.write Join(Application.Transpose(data), ", ")
   
    ts.Close

End Sub

Note that the code will leave your worksheet unchanged.

Hope this helps!

 

[alansidman]

Here is a power query solution to put the data into a sheet

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Transposed Table" = Table.Transpose(Source),
    #"Merged Columns" = Table.CombineColumns(#"Transposed Table",{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged")
in
    #"Merged Columns"

 

[mkrautler]

Here is a power query solution to put the data into a sheet

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Transposed Table" = Table.Transpose(Source),
    #"Merged Columns" = Table.CombineColumns(#"Transposed Table",{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged")
in
    #"Merged Columns"

I didn't try this solution yet since another solution already gave me what I was looking for. I will definitely keep the code, if you don't mind & use it at a later date. Thanks for replying!

 

[mkrautler]

After you've created a text stream using the CreateTextFile method, you'll need to write to the text stream...

myFile.write Join(Application.Transpose(rng))

Then you should close the text stream...

myFile.Close

However, here's another way...

Option Explicit

Private Sub Workbook_Open()

    ExportRangeToTextFile
  
End Sub

Private Sub ExportRangeToTextFile()

    Dim data As Variant
    With ThisWorkbook.Worksheets("Sheet1") 'change the sheet name accordingly
        data = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)).Value
    End With
  
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
  
    Dim ts As Object
    Set ts = fso.createtextfile("C:\temp\MKrautler\Tags.txt", True)
  
    ts.write Join(Application.Transpose(data), ", ")
  
    ts.Close

End Sub

Note that the code will leave your worksheet unchanged.

Hope this helps!

That helped tremendously, thank you so much!! I definitely appreciate it.