I'm not sure what you're doing, but maybe this'll give you some ideas:

Option Explicit
Sub testme()

Dim myRow As Range
Dim myCell As Range
Dim wks As Worksheet

Dim myFileName As String
Dim FileNum As Long
Dim myLine As String

myFileName = "C:\test.txt"

Set wks = Worksheets("sheet1")

With wks

FileNum = FreeFile
Close FileNum
Open myFileName For Output As FileNum

For Each myRow In .UsedRange.Rows
myLine = ""
For Each myCell In myRow.Cells
myLine = myLine & vbTab & myCell.Text
Next myCell
If myLine < "" Then
Print #FileNum, Mid(myLine, 2)
End If
Next myRow
Close FileNum
End With

End Sub

If this doesn't help, there's lots of code you might want to look at to help:

Earl Kiosterud's Text Write program:
http://www.tushar-mehta.com/
Look for Text Write in the left hand frame.

Chip Pearson's:
http://www.cpearson.com/excel/imptext.htm

J.E. McGimpsey's:
http://www.mcgimpsey.com/excel/textfiles.html

(Although DELIMITER in all caps looks like J.E.'s code to start.)

Dan T wrote:

"Dave Peterson" wrote:

And you could even just use:
vbTab
it's a constant builtin to VBA.

Const DELIMITER As String = vbTab

(And if delimiter isn't going to change in future versions, vbtab is quicker to
type!)

Dan T wrote:

I'm writing a routine to write to a test file. I've had two suggestions;
delimiter=Char(9). This gives me an invalid function all. And;
Const DELIMITER As String = CHR(9), this gives me an error of "Const expression required"
It does not seem to be accepting the chr(9) or the char(9) as a valid literal to set the delimiter to.
Any ideas why?

--

Dave Peterson



Still has commas and quotes, just adds tab to it. It there a way to just have the tab without the commas or quotes.

--

Dave Peterson