Unable to get the Text property of the WorksheetFunction class
 

I have a spreadsheet that I'm exporting data to a text file from. It
reads each cell and prints them out in a specific way in the text
file. When the value of one of the cells gets a couple lines worth of
data, it give the error listed in the subject. The variable is
defined as a string. I copy and paste the content into notepad but do
not see any issues with carriage return or anything so not sure what's
going on here. If I shorten the length of the cell it works fine.
Anyone know what's going on here?

Thanks.

JR

Unable to get the Text property of the WorksheetFunction class
 

show the actual code which produces the error

Tim.


"John" wrote in message
om...
I have a spreadsheet that I'm exporting data to a text file from. It
reads each cell and prints them out in a specific way in the text
file. When the value of one of the cells gets a couple lines worth of
data, it give the error listed in the subject. The variable is
defined as a string. I copy and paste the content into notepad but do
not see any issues with carriage return or anything so not sure what's
going on here. If I shorten the length of the cell it works fine.
Anyone know what's going on here?

Thanks.

JR

Unable to get the Text property of the WorksheetFunction class
 

Tim,

Pretty Simple the part of the code that is erroring:

Dim comments As String

CellValue = _
Application.WorksheetFunction.Text
(Cells(RowNdx, ColNdx).Value, _
Cells(RowNdx, ColNdx).NumberFormat)

comments = CellValue

Print #FNum, "comments=" & comments

So it reads in each cell of each row and outputs the contents to a
text file. This code works fine for every field except for one that I
have not restricted. Figured out it seems I am running what I've read
on the internet is a 254 character limit or something. The field can
hold more than this, but when I try and read/write out that cell when
it exceeds this length it gives the above error.

Any ideas how to work around this? Does Excel 2003 still have this
problem? I'm working in Excel 2000 right now.

Thanks.

JR

"Tim Williams" <saxifrax at pacbell dot net wrote in message ...
show the actual code which produces the error

Tim.


"John" wrote in message
om...
I have a spreadsheet that I'm exporting data to a text file from. It
reads each cell and prints them out in a specific way in the text
file. When the value of one of the cells gets a couple lines worth of
data, it give the error listed in the subject. The variable is
defined as a string. I copy and paste the content into notepad but do
not see any issues with carriage return or anything so not sure what's
going on here. If I shorten the length of the cell it works fine.
Anyone know what's going on here?

Thanks.

JR

Unable to get the Text property of the WorksheetFunction class
 

Tim,

Pretty Simple the part of the code that is erroring:

Dim comments As String

CellValue = _
Application.WorksheetFunction.Text
(Cells(RowNdx, ColNdx).Value, _
Cells(RowNdx, ColNdx).NumberFormat)

comments = CellValue

Print #FNum, "comments=" & comments

So it reads in each cell of each row and outputs the contents to a
text file. This code works fine for every field except for one that I
have not restricted. Figured out it seems I am running what I've read
on the internet is a 254 character limit or something. The field can
hold more than this, but when I try and read/write out that cell when
it exceeds this length it gives the above error.

Any ideas how to work around this? Does Excel 2003 still have this
problem? I'm working in Excel 2000 right now.

Thanks.

JR

"Tim Williams" <saxifrax at pacbell dot net wrote in message ...
show the actual code which produces the error

Tim.


"John" wrote in message
om...
I have a spreadsheet that I'm exporting data to a text file from. It
reads each cell and prints them out in a specific way in the text
file. When the value of one of the cells gets a couple lines worth of
data, it give the error listed in the subject. The variable is
defined as a string. I copy and paste the content into notepad but do
not see any issues with carriage return or anything so not sure what's
going on here. If I shorten the length of the cell it works fine.
Anyone know what's going on here?

Thanks.

JR

Unable to get the Text property of the WorksheetFunction class
 

There does seem to be a limitation on the TEXT function.
If you're expecting the value of this field to be text, why not just use

Cells(RowNdx, ColNdx).Value

?

If you're writing this out to a file you might also want to strip out any CR
or LF characters.

Tim.



"John" wrote in message
om...
Tim,

Pretty Simple the part of the code that is erroring:

Dim comments As String

CellValue = _
Application.WorksheetFunction.Text
(Cells(RowNdx, ColNdx).Value, _
Cells(RowNdx, ColNdx).NumberFormat)

comments = CellValue

Print #FNum, "comments=" & comments

So it reads in each cell of each row and outputs the contents to a
text file. This code works fine for every field except for one that I
have not restricted. Figured out it seems I am running what I've read
on the internet is a 254 character limit or something. The field can
hold more than this, but when I try and read/write out that cell when
it exceeds this length it gives the above error.

Any ideas how to work around this? Does Excel 2003 still have this
problem? I'm working in Excel 2000 right now.

Thanks.

JR

"Tim Williams" <saxifrax at pacbell dot net wrote in message
...
show the actual code which produces the error

Tim.


"John" wrote in message
om...
I have a spreadsheet that I'm exporting data to a text file from. It
reads each cell and prints them out in a specific way in the text
file. When the value of one of the cells gets a couple lines worth of
data, it give the error listed in the subject. The variable is
defined as a string. I copy and paste the content into notepad but do
not see any issues with carriage return or anything so not sure what's
going on here. If I shorten the length of the cell it works fine.
Anyone know what's going on here?

Thanks.

JR

Unable to get the Text property of the WorksheetFunction class
 

And maybe just this would be sufficient:

myComments = Cells(RowNdx, ColNdx).Text

But you can get "######" back when you look at .text--if the columnwidth is too
narrow for a number/time/date.

John wrote:

Tim,

Pretty Simple the part of the code that is erroring:

Dim comments As String

CellValue = _
Application.WorksheetFunction.Text
(Cells(RowNdx, ColNdx).Value, _
Cells(RowNdx, ColNdx).NumberFormat)

comments = CellValue

Print #FNum, "comments=" & comments

So it reads in each cell of each row and outputs the contents to a
text file. This code works fine for every field except for one that I
have not restricted. Figured out it seems I am running what I've read
on the internet is a 254 character limit or something. The field can
hold more than this, but when I try and read/write out that cell when
it exceeds this length it gives the above error.

Any ideas how to work around this? Does Excel 2003 still have this
problem? I'm working in Excel 2000 right now.

Thanks.

JR

"Tim Williams" <saxifrax at pacbell dot net wrote in message ...
show the actual code which produces the error

Tim.


"John" wrote in message
om...
I have a spreadsheet that I'm exporting data to a text file from. It
reads each cell and prints them out in a specific way in the text
file. When the value of one of the cells gets a couple lines worth of
data, it give the error listed in the subject. The variable is
defined as a string. I copy and paste the content into notepad but do
not see any issues with carriage return or anything so not sure what's
going on here. If I shorten the length of the cell it works fine.
Anyone know what's going on here?

Thanks.

JR

--

Dave Peterson