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

save it as a TAB separated file


"PVANS" wrote:

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
---------------
Jacob Skaria


"PVANS" wrote:

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

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" wrote:

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
---------------
Jacob Skaria


"PVANS" wrote:

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...

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

--
If this post helps click Yes
---------------
Jacob Skaria


"PVANS" wrote:

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" wrote:

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
---------------
Jacob Skaria


"PVANS" wrote:

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

Thanks Jacob,

Code worked brilliantly

Enjoy the rest of your day

"Jacob Skaria" wrote:

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

--
If this post helps click Yes
---------------
Jacob Skaria


"PVANS" wrote:

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" wrote:

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
---------------
Jacob Skaria


"PVANS" wrote:

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

You are welcome. Thanks for the feedback.

If this post helps click Yes
---------------
Jacob Skaria


"PVANS" wrote:

Thanks Jacob,

Code worked brilliantly

Enjoy the rest of your day

"Jacob Skaria" wrote:

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

--
If this post helps click Yes
---------------
Jacob Skaria


"PVANS" wrote:

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" wrote:

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
---------------
Jacob Skaria


"PVANS" wrote:

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

JustFYI..

http://www.microsoft.com/wn3/locales....htm#RateAPost

If this post helps click Yes
---------------
Jacob Skaria


"PVANS" wrote:

Thanks Jacob,

Code worked brilliantly

Enjoy the rest of your day

"Jacob Skaria" wrote:

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

--
If this post helps click Yes
---------------
Jacob Skaria


"PVANS" wrote:

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" wrote:

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
---------------
Jacob Skaria


"PVANS" wrote:

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