Save file as Tilda Delimited without spaces or commas between colu

  • Thread starter Thread starter PVANS
  • Start date Start date
P

PVANS

Good morning,

I am currently attempting to create a macro that will copy all the data from
an existing worksheet into a new workbook (this part I can do), and save as a
..csv file type (again this part I can do).

However, I would like to save the data in the worksheet to a file with no
delimiters between the columns. Could someone please suggest a method to
achieve this?

Thank you very much.

Regards
 
Try the below macro which will save the activesheet contents (Used range) to
a tilde delimited file.

Sub SaveAsCustomDelimited()
Dim intFile As Integer, strData As String
Dim rngRow As Range, cell As Range

intFile = FreeFile
Open "c:\test.txt" For Output As #intFile
For Each rngRow In ActiveSheet.UsedRange.Rows
For Each cell In rngRow.Cells
strData = strData & "~" & Trim(cell)
Next
Print #intFile, Mid(strData, 2): strData = vbNullString
Next
Close #intFile
End Sub


If this post helps click Yes
 
Hi Jacob,

Thanks for the code. After initially running and testing it, I discovered
that it has created too many "~". I therefore edited your code like so:

Sub SaveAsCustomDelimited()
Dim intFile As Integer, strData As String
Dim rngRow As Range, cell As Range

intFile = FreeFile
Open "c:\test.txt" For Output As #intFile
For Each rngRow In ActiveSheet.UsedRange.Rows
For Each cell In rngRow.Cells
strData = strData & "" & Trim(cell) 'simply removed tilda from between
""
Next
Print #intFile, Mid(strData, 2): strData = vbNullString
Next
Close #intFile
End Sub

This resulted in an almost perfect result. Unforetunately, I now am missing
a single "~" that should be appearing at the start of every row. Would you
have any suggestions on a code I could run to simply place a ~ at the start
of each row?

Once again, thanks for your assistance up to this point, and beyond

Regards
 
Try the below...

Sub SaveAsCustomDelimited()
Dim intFile As Integer, strData As String
Dim rngRow As Range, cell As Range

intFile = FreeFile
Open "c:\test.txt" For Output As #intFile
For Each rngRow In ActiveSheet.UsedRange.Rows
strData = "~"
For Each cell In rngRow.Cells
strData = strData & Trim(cell)
Next
Print #intFile, strData
Next
Close #intFile
End Sub
 
Back
Top