TransferSpreadsheet

  • Thread starter Frustrated in Magna
  • Start date
F

Frustrated in Magna

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?
 
M

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?
Click to expand...

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?"
 
F

Frustrated in Magna

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?
 
M

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?
Click to expand...

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?"
 
V

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

Frustrated in Magna

How do I get around the 255 character truncate?

Van T. Dinh said:
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.
Click to expand...
 
J

Jamie Collins

Van T. Dinh said:
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.
Click to expand...

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.

--
 
V

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

Jamie Collins

Van T. Dinh said:
I was thinking of the "Group By" aggregation which truncates Memo to 255
characters.
Click to expand...

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

Jamie.

--
 
Top