Export Memo field to Excel

[Tom]

The issue is that when a table, which contains a Memo field, to Excel 2002
the data in the Memo field has been truncated to 255 characters when viewed
in Excel.

Using Accessess 2002

Any advise/Suggestions how to overcome this problem would be appreciated

TIA

Tom

 

[Ken Snell \(MVP\)]

Use the TransferSpreadsheet action in either a macro or VBA code. It will
not truncate a memo field, whereas OutputTo (macro or VBA) and File | Export
do truncate.

 

[Tom]

Thanks Ken

Have tried your suggestions and notice the following:

1. If I use:

DoCmd.TransferSpreadsheet acExport, , "tblA-ZYearBook",
"C:\Temp\Full2.xls", False

The memo field is exported in full


If I use

Dim strFileName As String

strFileName = Me![MemberType]

DoCmd.TransferSpreadsheet acExport, , "tblA-ZYearBook", "C:\Temp\"
& strFileName & ".xls", False

The Memo field is truncated as before.

2. If there are any carriage returns in that memo field, under Excel they
are shown as small squares, wheras using the Output to method did not
produce this problem

Any advice on how to correct my code in 1 above and how to overcome 2 would
be appreciated

Tom

 

[Ken Snell \(MVP\)]

RE: number 1:

In my experience there should be no difference in the output from the two
examples that you give -- in other words, I am surprised if you do indeed
see a difference in the memo field's string data. The output is determined
by the data source (tblA-ZYearBook) and the version of EXCEL that is defined
(in your case, you're using the default version, usually EXCEL 9.0+, because
you don't specify it in the second argument of the TransferSpreadsheet
method).

When you tried the second example, did the target EXCEL file already exist?
Or are you letting TransferSpreadsheet create it for you (a better approach
for this testing)?


RE: number 2:

In ACCESS, a "new line" is created by the combination of two characters:
the carriage return (Chr(13)) and the line feed (Chr(10)). In EXCEL, a new
line is created by just the line feed character.

So, the data in your table need to be changed either before or after the
export is done. One "easy" way to do this is to use a query for the data
source, not the table, and to have a calculated field in the query in place
of the field with the "new line" characters in it, and to use the Replace
function to make the change.

But, this is where it's tricky -- because you have a memo field, any use of
a calculated field in a query for exporting to EXCEL will truncate the memo
field, regardless of what method you use. So, if you want to change the data
before you export, you'll need to use an append query to put the data (with
the replacement of the "new line" characters) into an empty table, where the
table has a memo field to receive the memo field's data from the query, and
then you will need to export this "intermediate" table to EXCEL.

The alternative is to modify the data in the EXCEL spreadsheet after the
export. This can be done with some VBA code that loops through all the cells
and uses the Replace function to make the switch.

--

Ken Snell
<MS ACCESS MVP>

Thanks Ken

Have tried your suggestions and notice the following:

1. If I use:

DoCmd.TransferSpreadsheet acExport, , "tblA-ZYearBook",
"C:\Temp\Full2.xls", False

The memo field is exported in full


If I use

Dim strFileName As String

strFileName = Me![MemberType]

DoCmd.TransferSpreadsheet acExport, , "tblA-ZYearBook",
"C:\Temp\" & strFileName & ".xls", False

The Memo field is truncated as before.

2. If there are any carriage returns in that memo field, under Excel
they are shown as small squares, wheras using the Output to method did not
produce this problem

Any advice on how to correct my code in 1 above and how to overcome 2
would be appreciated

Tom

Use the TransferSpreadsheet action in either a macro or VBA code. It will
not truncate a memo field, whereas OutputTo (macro or VBA) and File |
Export do truncate.

 

[Tom]

Thanks Ken

1

Its working fine , I note that when the 2nd argument is not specified, the
Excel file created is nearly double in size compared to when the argument is
specified [161k compared to 85k], but it works fine

2.

Found it easier to produce an 'intermediate' table & run a update query to
replace the (Chr(13)) & (Chr(10)) with a ',' and then transfertext the
updated table

Many thanks for your advice

Tom

RE: number 1:

In my experience there should be no difference in the output from the two
examples that you give -- in other words, I am surprised if you do indeed
see a difference in the memo field's string data. The output is determined
by the data source (tblA-ZYearBook) and the version of EXCEL that is
defined (in your case, you're using the default version, usually EXCEL
9.0+, because you don't specify it in the second argument of the
TransferSpreadsheet method).

When you tried the second example, did the target EXCEL file already
exist? Or are you letting TransferSpreadsheet create it for you (a better
approach for this testing)?


RE: number 2:

In ACCESS, a "new line" is created by the combination of two characters:
the carriage return In EXCEL, a new line is created by just the line feed
character.

So, the data in your table need to be changed either before or after the
export is done. One "easy" way to do this is to use a query for the data
source, not the table, and to have a calculated field in the query in
place of the field with the "new line" characters in it, and to use the
Replace function to make the change.

But, this is where it's tricky -- because you have a memo field, any use
of a calculated field in a query for exporting to EXCEL will truncate the
memo field, regardless of what method you use. So, if you want to change
the data before you export, you'll need to use an append query to put the
data (with the replacement of the "new line" characters) into an empty
table, where the table has a memo field to receive the memo field's data
from the query, and then you will need to export this "intermediate" table
to EXCEL.

The alternative is to modify the data in the EXCEL spreadsheet after the
export. This can be done with some VBA code that loops through all the
cells and uses the Replace function to make the switch.

--

Ken Snell
<MS ACCESS MVP>

Thanks Ken

Have tried your suggestions and notice the following:

1. If I use:

DoCmd.TransferSpreadsheet acExport, , "tblA-ZYearBook",
"C:\Temp\Full2.xls", False

The memo field is exported in full


If I use

Dim strFileName As String

strFileName = Me![MemberType]

DoCmd.TransferSpreadsheet acExport, , "tblA-ZYearBook",
"C:\Temp\" & strFileName & ".xls", False

The Memo field is truncated as before.

2. If there are any carriage returns in that memo field, under Excel
they are shown as small squares, wheras using the Output to method did
not produce this problem

Any advice on how to correct my code in 1 above and how to overcome 2
would be appreciated

Tom

Use the TransferSpreadsheet action in either a macro or VBA code. It
will not truncate a memo field, whereas OutputTo (macro or VBA) and File
| Export do truncate.


--

Ken Snell
<MS ACCESS MVP>

The issue is that when a table, which contains a Memo field, to Excel
2002 the data in the Memo field has been truncated to 255 characters
when viewed in Excel.

Using Accessess 2002

Any advise/Suggestions how to overcome this problem would be
appreciated

TIA

Tom