Losing data from memo field when exporting to Excel

  • Thread starter Thread starter dbl
  • Start date Start date
D

dbl

Hi this question must have been asked many times, so I apologise now for
asking it again. But I cannot find the answer.

When I export a report to Excel from Access 2003 to Excel 2003 the memo
field only transfers the first 225 digits
how do I get round this problem.

Thanks

Bob
 
Do not use File | Export menu to do the transfer. It uses EXCEL 95 format
for outputting, which is limited to text strings no longer than 255
characters.

Use TransferSpreadsheet action (macro or VBA code) to do the export. It will
not truncate memo fields.
 
Hi Ken thanks for reply, could you have a look at the following and tell me
where I am going wrong please, it comes up with an error check the file name
or path
but both are correct or is it the code is all wrong anyway.

Thanks

Bob

Dim stDocName As String
Dim strWhere As String
strWhere = "[Date] between #" & Format(Me.txtStartDate, "dd-mmm-yy")
& "# AND #" & Format(Me.txtEndDate, "dd-mmm-yy") & "#"
stDocName = "ClaimFormReport"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"ClaimFormReport","C:\Documents and Settings\Bob\My
Documents\ClaimFormReport.xls", True
 
Let's try a simpler path for the file first. Try this and see if it works:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"ClaimFormReport","C:\ClaimFormReport.xls"


By the way, the other code lines that you posted do not do anything in this
code snippet -- you don't use the variables anywhere else in the code
snippet.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



dbl said:
Hi Ken thanks for reply, could you have a look at the following and tell
me where I am going wrong please, it comes up with an error check the file
name or path
but both are correct or is it the code is all wrong anyway.

Thanks

Bob

Dim stDocName As String
Dim strWhere As String
strWhere = "[Date] between #" & Format(Me.txtStartDate, "dd-mmm-yy")
& "# AND #" & Format(Me.txtEndDate, "dd-mmm-yy") & "#"
stDocName = "ClaimFormReport"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"ClaimFormReport","C:\Documents and Settings\Bob\My
Documents\ClaimFormReport.xls", True

Ken Snell said:
Do not use File | Export menu to do the transfer. It uses EXCEL 95 format
for outputting, which is limited to text strings no longer than 255
characters.

Use TransferSpreadsheet action (macro or VBA code) to do the export. It
will not truncate memo fields.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/
Click to expand...
Click to expand...
 
Ken that works but it brings over every record, it was set up to pick up the
dates from a form where I run all my daily reports so that you only have to
enter the date once for every report.

I can pick up the correct date by using Between [Start Date] And [Finish
Date] in the query.

Is it possible to bring that back into the coding so that I still only have
to enter the dates once for every report? This is how it was before the
transfer code was used.

Thanks Bob

Dim stDocName As String
Dim strWhere As String
strWhere = "[Date] between #" & Format(Me.txtStartDate, "dd-mmm-yy")
& "# AND #" & Format(Me.txtEndDate, "dd-mmm-yy") & "#"
stDocName = "ClaimFormReport"
DoCmd.OpenReport stDocName, acPreview, , strWhere


Ken Snell said:
Let's try a simpler path for the file first. Try this and see if it works:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"ClaimFormReport","C:\ClaimFormReport.xls"


By the way, the other code lines that you posted do not do anything in
this code snippet -- you don't use the variables anywhere else in the code
snippet.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



dbl said:
Hi Ken thanks for reply, could you have a look at the following and tell
me where I am going wrong please, it comes up with an error check the
file name or path
but both are correct or is it the code is all wrong anyway.

Thanks

Bob

Dim stDocName As String
Dim strWhere As String
strWhere = "[Date] between #" & Format(Me.txtStartDate,
"dd-mmm-yy") & "# AND #" & Format(Me.txtEndDate, "dd-mmm-yy") & "#"
stDocName = "ClaimFormReport"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"ClaimFormReport","C:\Documents and Settings\Bob\My
Documents\ClaimFormReport.xls", True

Ken Snell said:
Do not use File | Export menu to do the transfer. It uses EXCEL 95
format for outputting, which is limited to text strings no longer than
255 characters.

Use TransferSpreadsheet action (macro or VBA code) to do the export. It
will not truncate memo fields.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Hi this question must have been asked many times, so I apologise now
for asking it again. But I cannot find the answer.

When I export a report to Excel from Access 2003 to Excel 2003 the memo
field only transfers the first 225 digits
how do I get round this problem.

Thanks

Bob
Click to expand...
Click to expand...
Click to expand...
 
Not sure if I'm fully understanding. You want to export records that are
filtered based on values in the form's textboxes for dates?

If yes, the query that you export must already contain those parameters; you
cannot "add" the filtering as part of the TransferSpreadsheet action.

Create a copy of the current query, and add the parameters to it. Save it
with a different name. Then export the new query instead of the original
query that you were using in the TransferSpreadsheet action.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



dbl said:
Ken that works but it brings over every record, it was set up to pick up
the dates from a form where I run all my daily reports so that you only
have to enter the date once for every report.

I can pick up the correct date by using Between [Start Date] And [Finish
Date] in the query.

Is it possible to bring that back into the coding so that I still only
have to enter the dates once for every report? This is how it was before
the transfer code was used.

Thanks Bob

Dim stDocName As String
Dim strWhere As String
strWhere = "[Date] between #" & Format(Me.txtStartDate, "dd-mmm-yy")
& "# AND #" & Format(Me.txtEndDate, "dd-mmm-yy") & "#"
stDocName = "ClaimFormReport"
DoCmd.OpenReport stDocName, acPreview, , strWhere


Ken Snell said:
Let's try a simpler path for the file first. Try this and see if it
works:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"ClaimFormReport","C:\ClaimFormReport.xls"


By the way, the other code lines that you posted do not do anything in
this code snippet -- you don't use the variables anywhere else in the
code snippet.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



dbl said:
Hi Ken thanks for reply, could you have a look at the following and tell
me where I am going wrong please, it comes up with an error check the
file name or path
but both are correct or is it the code is all wrong anyway.

Thanks

Bob

Dim stDocName As String
Dim strWhere As String
strWhere = "[Date] between #" & Format(Me.txtStartDate,
"dd-mmm-yy") & "# AND #" & Format(Me.txtEndDate, "dd-mmm-yy") & "#"
stDocName = "ClaimFormReport"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"ClaimFormReport","C:\Documents and Settings\Bob\My
Documents\ClaimFormReport.xls", True

Do not use File | Export menu to do the transfer. It uses EXCEL 95
format for outputting, which is limited to text strings no longer than
255 characters.

Use TransferSpreadsheet action (macro or VBA code) to do the export. It
will not truncate memo fields.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Hi this question must have been asked many times, so I apologise now
for asking it again. But I cannot find the answer.

When I export a report to Excel from Access 2003 to Excel 2003 the
memo field only transfers the first 225 digits
how do I get round this problem.

Thanks

Bob
Click to expand...
Click to expand...
Click to expand...
 
Thanks Ken your help has been very much appreciated.

Regards

Bob
Ken Snell said:
Not sure if I'm fully understanding. You want to export records that are
filtered based on values in the form's textboxes for dates?

If yes, the query that you export must already contain those parameters;
you cannot "add" the filtering as part of the TransferSpreadsheet action.

Create a copy of the current query, and add the parameters to it. Save it
with a different name. Then export the new query instead of the original
query that you were using in the TransferSpreadsheet action.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



dbl said:
Ken that works but it brings over every record, it was set up to pick up
the dates from a form where I run all my daily reports so that you only
have to enter the date once for every report.

I can pick up the correct date by using Between [Start Date] And [Finish
Date] in the query.

Is it possible to bring that back into the coding so that I still only
have to enter the dates once for every report? This is how it was before
the transfer code was used.

Thanks Bob

Dim stDocName As String
Dim strWhere As String
strWhere = "[Date] between #" & Format(Me.txtStartDate,
"dd-mmm-yy") & "# AND #" & Format(Me.txtEndDate, "dd-mmm-yy") & "#"
stDocName = "ClaimFormReport"
DoCmd.OpenReport stDocName, acPreview, , strWhere


Ken Snell said:
Let's try a simpler path for the file first. Try this and see if it
works:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"ClaimFormReport","C:\ClaimFormReport.xls"


By the way, the other code lines that you posted do not do anything in
this code snippet -- you don't use the variables anywhere else in the
code snippet.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Hi Ken thanks for reply, could you have a look at the following and
tell me where I am going wrong please, it comes up with an error check
the file name or path
but both are correct or is it the code is all wrong anyway.

Thanks

Bob

Dim stDocName As String
Dim strWhere As String
strWhere = "[Date] between #" & Format(Me.txtStartDate,
"dd-mmm-yy") & "# AND #" & Format(Me.txtEndDate, "dd-mmm-yy") & "#"
stDocName = "ClaimFormReport"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"ClaimFormReport","C:\Documents and Settings\Bob\My
Documents\ClaimFormReport.xls", True

Do not use File | Export menu to do the transfer. It uses EXCEL 95
format for outputting, which is limited to text strings no longer than
255 characters.

Use TransferSpreadsheet action (macro or VBA code) to do the export.
It will not truncate memo fields.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Hi this question must have been asked many times, so I apologise now
for asking it again. But I cannot find the answer.

When I export a report to Excel from Access 2003 to Excel 2003 the
memo field only transfers the first 225 digits
how do I get round this problem.

Thanks

Bob
Click to expand...
Click to expand...
Click to expand...
 
Back
Top