How to Export as text file with NO delimiters or modifications?

hello all,

I am simply trying to save a sheet as a text file with the column
contents saved as a simple long string of data, regardless of content.

Ex. If my columns were this: (underscores represents columns, the pipes
and ampersands are actual cell content).

a213_____|_____text1_____&_____14.44000_____|_____ endtext

I would want the text file to read:

a213|text1&14.44000|endtext

None of the "Save As" choices allow this. All end up with at least
tab's separating the original cell contents. Also tried changing the
delimiter in the control panel, but NULL is not an allowed choice. And
opening the csv in any text editor still has tabs as separators
anyway.

Thanks
Scott


--
MojoNixon
------------------------------------------------------------------------
MojoNixon's Profile: http://www.excelforum.com/member.php...o&userid=37251
View this thread: http://www.excelforum.com/showthread...hreadid=569672

Try saving the file as a .dbf file - these are fixed width with no
delimeters.

Hope this helps.

Pete

MojoNixon wrote:
hello all,

I am simply trying to save a sheet as a text file with the column
contents saved as a simple long string of data, regardless of content.

Ex. If my columns were this: (underscores represents columns, the pipes
and ampersands are actual cell content).

a213_____|_____text1_____&_____14.44000_____|_____ endtext

I would want the text file to read:

a213|text1&14.44000|endtext

None of the "Save As" choices allow this. All end up with at least
tab's separating the original cell contents. Also tried changing the
delimiter in the control panel, but NULL is not an allowed choice. And
opening the csv in any text editor still has tabs as separators
anyway.

Thanks
Scott


--
MojoNixon
------------------------------------------------------------------------
MojoNixon's Profile: http://www.excelforum.com/member.php...o&userid=37251
View this thread: http://www.excelforum.com/showthread...hreadid=569672

Hi Scott.
If I understand you right, this macro will do it. Put your own filename
and path in place of c:\temp\output100.txt. I also made the assumption
that there are no blank cells in column A, and that all rows end with a
cell containing "endtext". The macro can be modified if this isn't
true. As written, it will do all rows starting at 1 until it gets to a
blank cell in column A. I put in an arbitrary limit of 100 columns too,
in case there isn't an endtext cell. If you need help entering or
running the macro, post back and I will help with it.
Len


Sub MakeTextFile()
Dim strTemp As String
Dim strCellText As String
Dim strFileName As String
Dim intOutFile As Integer
Dim intColumn As Integer

intOutFile = FreeFile
strFileName = "c:\temp\output100.txt" 'or whereever/whatever
Open strFileName For Output As intOutFile
Range("A1").Activate 'change A1 to where you want to start
strTemp = ActiveCell.Value
Do While Len(strTemp) 0
intColumn = 1
strCellText = "" 'initialize
'loop thru the columns until endtext (or max columns)
Do While UCase(strCellText) "ENDTEXT" And intColumn 100
strCellText = Cells(ActiveCell.Row, intColumn).Value
Print #intOutFile, strCellText; 'semicolon prevents new line
intColumn = intColumn + 1
Loop
'go to a new line in the output file
Print #intOutFile,

ActiveCell.Offset(1, 0).Activate 'go down a row
strTemp = ActiveCell.Value
Loop
Close intOutFile
End Sub


MojoNixon wrote:
hello all,

I am simply trying to save a sheet as a text file with the column
contents saved as a simple long string of data, regardless of content.

Ex. If my columns were this: (underscores represents columns, the pipes
and ampersands are actual cell content).

a213_____|_____text1_____&_____14.44000_____|_____ endtext

I would want the text file to read:

a213|text1&14.44000|endtext

None of the "Save As" choices allow this. All end up with at least
tab's separating the original cell contents. Also tried changing the
delimiter in the control panel, but NULL is not an allowed choice. And
opening the csv in any text editor still has tabs as separators
anyway.

Thanks
Scott

LenB

Thanks for your help. Yes, please I do need help in creating this macro
if you can give a quick hot-to. Also, if there is no "end text", but
there is a fixed amount of column's, can that be used to in place of
the end text?

thanks again,

Scott


--
MojoNixon
------------------------------------------------------------------------
MojoNixon's Profile: http://www.excelforum.com/member.php...o&userid=37251
View this thread: http://www.excelforum.com/showthread...hreadid=569672

Okay, here's a new version for a fixed number of columns.

The line: For intColumn = 1 To 7 is the loop for getting the value from
each column. Change the 7 to the highest column you are using. If you
didn't start in column A or Row 1, change the 1 to the start column, and
change Range("A1").Activate to activate the correct starting cell.
To create the macro, I suggest starting with a copy of your xls file.

In Tools - Options, on the Security Tab (in Excel 2002, maybe different
in other versions) click macro security and set it to medium. If it is
high, you can't run macros. Medium will ask every time you open a file
containing macros. If low, it never asks. Maybe risky for some. I
like medium.

Press Alt+F11 to get into the Visual Basic Editor (VBE).
There should be a project tree visible, probably on the left. If not
visible, press ctrl+R, or View - Project Explorer.

Right click on the line "VBAProject (your file name)", click insert,
click module. That should create a module1 at the bottom of the tree
and open a code window. This is where you paste the macro. Everything
including the End Sub line.

This is a good time to save! Saving in the VBE saves the xls file, just
like in the Excel window.

Make sure the output file name and path are ok. I used
c:\temp\output100.txt. It can also get the file name from a cell if you
like. If the file name is in K1, Use something like strFileName =
Range("K1").value, or if you named a cell, use the cell name in place of K1.
To run it right thru, press F5 while in the VBE. You can also step thru
the code and watch it work using F8 instead of F5. I usually have the
VBE window unmaximized and off to the right with the excel sheet visible
to the left. Be careful about moving the active cell in Excel while
running a macro. Until you are familiar with the macro, it is best not
to click in the Excel window. Back in the VBE, every press of F8 runs
one line. The yellow highlighted line is the next line to be executed.
You can hover the mouse cursor on a variable name (strTextOut is a
good one here) and a tooltip will show the value of the variable. If
you step thru the inner For-Next loop while hovering on strTextOut, you
will see how strTextOut is created one cell at a time.
Press F5 anytime while stepping thru to run to the end. Also right
click on a line gives you some options. A useful one is "Run To
Cursor". That will stop at the line where the cursor is. "Set Next
Statement" makes that line the next one to be run. Pressing F9 makes a
breakpoint on the line with the cursor. The macro will always stop on
that line waiting for an F5 or F8.
Hope this is clear enough and that I didn't miss anything. If you have
to run it often, you can create a toolbar button, or a Ctrl-Shift-letter
combination, or just press Alt + F8 and run the macro from there. No
need to have the VBE open.

Len


Sub MakeTextFile()
Dim strTemp As String
Dim strTextOut As String
Dim strFileName As String
Dim intOutFile As Integer
Dim intColumn As Integer

intOutFile = FreeFile 'get an available file number
strFileName = "c:\temp\output100.txt" 'or whereever/whatever
Open strFileName For Output As intOutFile
Range("A1").Activate 'start at the top
strTemp = ActiveCell.Value
Do While Len(strTemp) 0 'goes until a blank cell in column A
strTextOut = "" 'initialize
'loop thru the columns. Change 7 to the
'highest column number used
For intColumn = 1 To 7
strTextOut = strTextOut & Cells(ActiveCell.Row, intColumn).Value
Next
Print #intOutFile, strTextOut 'send it to the output file
ActiveCell.Offset(1, 0).Activate 'go down a row
strTemp = ActiveCell.Value
Loop
Close intOutFile
End Sub



MojoNixon wrote:
LenB

Thanks for your help. Yes, please I do need help in creating this macro
if you can give a quick hot-to. Also, if there is no "end text", but
there is a fixed amount of column's, can that be used to in place of
the end text?

thanks again,

Scott

LenB

Thanks! This was perfect. Just changed the column count and it gave me
exactly what I needed.

Rgds


--
MojoNixon
------------------------------------------------------------------------
MojoNixon's Profile: http://www.excelforum.com/member.php...o&userid=37251
View this thread: http://www.excelforum.com/showthread...hreadid=569672