Save file as Tilda Delimited without spaces or commas between colu

[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

 

[Patrick Molloy]

save it as a TAB separated file

 

[Jacob Skaria]

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

 

[PVANS]

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

 

[Jacob Skaria]

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

 

[PVANS]

Thanks Jacob,

Code worked brilliantly

Enjoy the rest of your day

 

[Jacob Skaria]

You are welcome. Thanks for the feedback.

If this post helps click Yes

 

[Jacob Skaria]

JustFYI..

http://www.microsoft.com/wn3/locales/help/help_en-us.htm#RateAPost

If this post helps click Yes