Excel truncates memo field to 255 chars

[Danny J. Lesandrini]

Well, I've searched Google for this and found some references
to KB article that says it should work fine for Excel 2003, but
that's not my experience.

Has anyone found a reasonable workaround?

I'm using automation, creating Excel.Application and workbook
objects. I populate the worksheet one cell at a time from data
in a DAO Recordset. What does one have to do to get more than
255 characters into an Excel cell?

 

[Joseph Meehan]

I don't think you can put more than 255 characters in an Excel cell.

 

[Ken Snell \(MVP\)]

My guess is that your recordset's query is at fault here. Queries can
truncate memo fields if you do any type of grouping on the memo field.

Post the recordset's query's SQL statement and provide some explanation of
the data types of each of the fields in that query.

 

[Ken Snell \(MVP\)]

An EXCEL cell can hold lots of characters... If the cell is formatted as
Text, it'll show ## characters when you exceed the 255-character amount, but
if formatted as General it'll hold tens of thousands of characters.

 

[Danny J. Lesandrini]

I found the answer (or answers)

You CANNOT use Distinct
(my query didn't actually use it)

You CANNOT have ANY formats set in the properties of ANY columns
You CANNOT apply ANY functions to Memo fields (Nz(), IIF(), Left(), etc)
(my query did all of these things)

I'm sure the version of Excel matters too, but as I mentioned in my case,
the version was 2003.

I think this info is going to me more valuable as more code starts to break
on account of the change to Access from the law suit loss that Microsoft
suffered. I'm glad to have it fixed, but it seems tenuous and fragile.

 

[Ken Snell \(MVP\)]

Your list is not correct.... you can use Left, Nz, etc. functions with memo
fields.

As I said, if you post the SQL statement of the query, we can help identify
what was causing your trunction problem in this particular instance.

 

[Danny J. Lesandrini]

Ken:

I respect you, and you may be correct, but I know what I experienced.
The first column of the query had a date format in the format property
which was a literal, "dd-mm-yy" for European dates.

The SQL is pasted below, but not the whole thing. What you see is
representative of the whole. I was magnifing my vexation when I said
that Left() causes the problem, but the offending column (f7) contained
IIF() and IsNull(). Before I removed them, it failed. After I took out
the entire logic of it and left only the field itself, the output worked
and dumped ALL characters, no matter what the length.
--

Danny J. Lesandrini
(e-mail address removed)
http://amazecreations.com/datafast

Your list is not correct.... you can use Left, Nz, etc. functions with memo fields.

As I said, if you post the SQL statement of the query, we can help identify what was causing your trunction problem in
this particular instance.

--

SELECT
IIf(IsNull([RevisionDate])," ",[RevisionDate]) AS f3
, IIf(IsNull([StatusStatus])," ",[StatusStatus]) AS f4
, IIf(IsNull([ReleaseOrderNumber])," ",[ReleaseOrderNumber]) AS f5
, IIf(IsNull([ApprovalDate])," ",[ApprovalDate]) AS f6
, IIf(IsNull([StatusHistoryAction])," ",[StatusHistoryAction]) AS f7
, IIf(IsNull([StatusEndUserCountry])," ",[StatusEndUserCountry]) AS f8
, IIf(IsNull([ProdInfoItemNumber])," ",[ProdInfoItemNumber]) AS f9
, IIf(IsNull([ProdInfoPartNumber])," ",[ProdInfoPartNumber]) AS f10
, IIf(IsNull([ProdInfoItemDescription])," ",[ProdInfoItemDescription]) AS f11
, IIf(IsNull([ProdECCNCode])," ",[ProdECCNCode]) AS f12
, IIf(IsNull([BusinessDescription])," ",[BusinessDescription]) AS f33
, IIf(IsNull([UseApplication])," ",[UseApplication]) AS f34
, IIf(IsNull([MatchingAndScreeningComments])," ",[MatchingAndScreeningComments]) AS f56
, IIf(IsNull([PreliminaryReviewComments])," ",[PreliminaryReviewComments]) AS f57
, IIf(IsNull([FinalReviewComments])," ",[FinalReviewComments]) AS f58
FROM
tblWorkingMaster;

 

[Ken Snell \(MVP\)]

I went back to some KB articles to refresh my memory. I apologize...You are
correct that an expression will truncate the data to 255 characters when you
*export* the query. This KB article discusses this in very general terms:
http://support.microsoft.com/default.aspx?scid=kb;en-us;178743

Thanks for the memory jog (I won't use "It's Monday" as an excuse < g >).
Exporting is a weird animal.

--

Ken Snell
<MS ACCESS MVP>

Ken:

I respect you, and you may be correct, but I know what I experienced.
The first column of the query had a date format in the format property
which was a literal, "dd-mm-yy" for European dates.

The SQL is pasted below, but not the whole thing. What you see is
representative of the whole. I was magnifing my vexation when I said
that Left() causes the problem, but the offending column (f7) contained
IIF() and IsNull(). Before I removed them, it failed. After I took out
the entire logic of it and left only the field itself, the output worked
and dumped ALL characters, no matter what the length.
--

Danny J. Lesandrini
(e-mail address removed)
http://amazecreations.com/datafast

Your list is not correct.... you can use Left, Nz, etc. functions with
memo fields.

As I said, if you post the SQL statement of the query, we can help
identify what was causing your trunction problem in this particular
instance.

--

SELECT
IIf(IsNull([RevisionDate])," ",[RevisionDate]) AS f3
, IIf(IsNull([StatusStatus])," ",[StatusStatus]) AS f4
, IIf(IsNull([ReleaseOrderNumber])," ",[ReleaseOrderNumber]) AS f5
, IIf(IsNull([ApprovalDate])," ",[ApprovalDate]) AS f6
, IIf(IsNull([StatusHistoryAction])," ",[StatusHistoryAction]) AS f7
, IIf(IsNull([StatusEndUserCountry])," ",[StatusEndUserCountry]) AS f8
, IIf(IsNull([ProdInfoItemNumber])," ",[ProdInfoItemNumber]) AS f9
, IIf(IsNull([ProdInfoPartNumber])," ",[ProdInfoPartNumber]) AS f10
, IIf(IsNull([ProdInfoItemDescription])," ",[ProdInfoItemDescription]) AS
f11
, IIf(IsNull([ProdECCNCode])," ",[ProdECCNCode]) AS f12
, IIf(IsNull([BusinessDescription])," ",[BusinessDescription]) AS f33
, IIf(IsNull([UseApplication])," ",[UseApplication]) AS f34
, IIf(IsNull([MatchingAndScreeningComments]),"
",[MatchingAndScreeningComments]) AS f56
, IIf(IsNull([PreliminaryReviewComments]),"
",[PreliminaryReviewComments]) AS f57
, IIf(IsNull([FinalReviewComments])," ",[FinalReviewComments]) AS f58
FROM
tblWorkingMaster;

 

[Danny J. Lesandrini]

Yup ... the IIF(IsNull([MyMemoField])) call is what breaks it.
Just retested the thing. Now I should try putting the date format
back in the query just to see if that matters.

 

[Danny J. Lesandrini]

Yeah babby!

The format property is irrelevant. It was the IIF and IsNull functions
that caused the problem. I didn't test for any other functions, but if
I hit the problem, they go on a short list for "the usual suspects."

Thanks Ken for your input and for pushing this question a little further.
I have a feeling this is going to be a popular topic for a few weeks.

 

[Douglas J. Steele]

Pardon my ignorance, but how does this relate to not being able to update
linked Excel sheets?

 

[Danny J. Lesandrini]

Ahhh ... that is not very obvious, is it?

When you used to be able to move data to a linked Excel spreadsheet
using an Append Query, the 255 limit didn't apply. There was no
problem, for some reason. (I suppose that's why the guy who figured
it out got an 8 million dollar check from Microsoft.)

So, you're going to have to start using Automation to move data to
Excel, and you're going to start noticing that your memo fields all
show no more than 255 characters of data. Surprise!

I didn't expect this result and was caught off-guard. Just remember
to remove DISTINCT from the query and as many functions from the
memo fields as you can.

 

[Douglas J Steele]

Sorry, but I'm still not getting it Danny! I'd expect it to be truncated
when you're reading the data if you're using any of the functions you
identified (the same's true with a Memo field in an Access table) Are you
saying that when you're using Automation to write to Excel, the text gets
truncated at 255 characters? What about if you use Automation to read the
data? (sorry, I only have Access 97 on this machine, although Excel 2003, so
I can't test myself)

 

[Danny J. Lesandrini]

I actually haven't experienced a problem reading data ... at least, not yet.
The problem I'm having is when I write.

The previous developer followed this process:
1) Copy an Excel template with column names, etc to new location
2) Link to that new Excel file
3) Run an Append query, which by default loaded data at first empty line.

This was pretty straightforward and allowed him to include custom headers
and extra sheets, as he used a named range (tab name) in the link code. The
behaviour he experienced with the Append Query does not carry over to the
automation code. Memo fields are truncated and NULLs are behaving funky.
I'm going to have to redesign this process from scratch ;-(
--

Danny J. Lesandrini
(e-mail address removed)
http://amazecreations.com/datafast/



"Douglas J Steele" <NOSPAM_djsteele wrote ...