Posted to microsoft.public.excel.programming
|
|
Save file as Tilda Delimited without spaces or commas between
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
|