Not recognizing a Const

[Dan T]

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?

 

[Rob Bovey]

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?

Hi Dan,

The first instance is the one you want, but you have the function name
spelled wrong. It's Chr not Char. You can't use the results of a function to
create a constant, so the second option will not work.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

 

[Dan T]

literal to set the delimiter to.

Hi Dan,

The first instance is the one you want, but you have the function name
spelled wrong. It's Chr not Char. You can't use the results of a function to
create a constant, so the second option will not work.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

Thanks rob that worked, but I thought it would replace the commas and quotes with the Tab, how do I remove the commas and quotes so I just have the Tab?

 

[Dan T]

literal to set the delimiter to.

Hi Dan,

The first instance is the one you want, but you have the function name
spelled wrong. It's Chr not Char. You can't use the results of a function to
create a constant, so the second option will not work.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

Thanks Rob, that worked, but I thought it would replace the commas and quotes with the tab. How do I have just a Tab delimiter without the quotes and commas?

 

[Dave Peterson]

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]

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!)

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]

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

 

[Tom Ogilvy]

Why not post the code you use to write the file and perhaps someone can
suggest where you need to make changes.

--
Regards,
Tom Ogilvy

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

have the tab without the commas or quotes.