TransferSpreadsheet

[Guest]

When I transfer a query into excel, it cuts off the information in the
record. Is there any way to transfer everything in the record?

 

[Mike Labosh]

When I transfer a query into excel, it cuts off the information in the

record. Is there any way to transfer everything in the record?

Have you made the Excel columns wider when viewing the data? It sounds more
likely that the data is not getting cut off, but just covered up by the next
column.
--
Peace & happy computing,

Mike Labosh, MCSD

"It's 4:30 am. Do you know where your stack pointer is?"

 

[Guest]

No, that is not what I am getting. The cell is wide enough, it is like the
cell ran out of room. But the funny thing is I can add more to the cell
after it has been transfered to excel. I just stops mid sentences for each
cell. Any other ideas?

 

[Mike Labosh]

No, that is not what I am getting. The cell is wide enough, it is like

the
cell ran out of room. But the funny thing is I can add more to the cell
after it has been transfered to excel. I just stops mid sentences for
each
cell. Any other ideas?

What if you make the rows taller? The text may have done some bizarre
word-wrapping.

--
Peace & happy computing,

Mike Labosh, MCSD

"It's 4:30 am. Do you know where your stack pointer is?"

 

[Van T. Dinh]

If the Field corresponding to the truncated Excel Column is a Memo, you
might have some sorting in the source Query. Since JET cannot sort Memo
Field values, JET truncate the values to 255 characters like Text Field
before sorting. This trucation is carried over to the export to the Excel
spreadsheet.

 

[Guest]

How do I get around the 255 character truncate?

If the Field corresponding to the truncated Excel Column is a Memo, you
might have some sorting in the source Query. Since JET cannot sort Memo
Field values, JET truncate the values to 255 characters like Text Field
before sorting. This trucation is carried over to the export to the Excel
spreadsheet.

 

[Van T. Dinh]

Don't sort / order in the query???

 

[Jamie Collins]

If the Field corresponding to the truncated Excel Column is a Memo, you
might have some sorting in the source Query. Since JET cannot sort Memo
Field values, JET truncate the values to 255 characters like Text Field
before sorting. This trucation is carried over to the export to the Excel
spreadsheet.

I don't think that's correct. Here's my test:

CREATE TABLE Van (MyMemoCol MEMO)
;
INSERT INTO Van (MyMemoColumn) VALUES
('1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678902')
;
INSERT INTO Van (MyMemoCol) VALUES
('1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901')
;
SELECT MyMemoCol
INTO [Excel 8.0;HDR=YES;Database=C:\Van.xls;].NewTable
FROM Van ORDER BY MyMemoCol
;
SELECT LEN(MyMemoCol)
FROM [Excel 8.0;HDR=YES;Database=C:\Van.xls;].NewTable
;

I get two rows, both showing 301, thus proving the data was not
curtailed even when a sort was attempted on export.

How did you do it?

Jamie.

--

 

[Jamie Collins]

How do I get around the 255 character truncate?

Is your Excel table already defined and perhaps contains data? The
problem could be that Jet determines that the data type for the
exising Excel column is TEXT, therefore MEMO data will be coerced to
TEXT by truncating at 255 characters. For details, see:

http://www.dicks-blog.com/excel/2004/06/external_data_m.html

Jamie.

--

 

[Van T. Dinh]

Thanks, Jamie.

I was thinking of the "Group By" aggregation which truncates Memo to 255
characters.

In addition, if the destination Excel file type specified in the
TransferSpreadsheet is one of the ealier Excel versions (Excel 95??? &
earlier), I *think* the length is also restricted to 255 characters. The
problem is that the default destination Excel file in the
TransferSpreadsheet is usually an earlier version than the version
coressponding to the Access version (for example, in A2002, the default
Excel format in TransferSpreadsheet is Excel 97 format, defaults for A2000
and Access97 I am not not sure but likely to be earlier version than
Excel97) and we have the same truncation problem.

 

[Jamie Collins]

I was thinking of the "Group By" aggregation which truncates Memo to 255
characters.

Thanks, Van. I've never noticed that one!

Jamie.

--