outputting memo fields to excel

[John Baker]

I have a query which contains some memo fields. I am using Office links to
transfer the data to Excel.
There are about 500 characters in some of the memo fields. All the data is
displayed as expected in the query, but when the data is transferred to
Excel the data in the memo field cells is cut off.

Is there a way to make sure all data from a memo field is transferred to
Excel? ( I would be happy with a limit of even 1000 characters)
Any help greatly appreciated

John Baker

 

[Gary Walter]

I have a query which contains some memo fields. I am using Office links to
transfer the data to Excel.
There are about 500 characters in some of the memo fields. All the data is
displayed as expected in the query, but when the data is transferred to
Excel the data in the memo field cells is cut off.

Is there a way to make sure all data from a memo field is transferred to
Excel? ( I would be happy with a limit of even 1000 characters)
Any help greatly appreciated

Hi John,

Did you try using SQL?

The following examples are from
http://support.microsoft.com/?kbid=295646

(replace "Customer" with name of your query,
and you could just enter path to xls, instead of using
"App.Path")

1) Copy to new sheet:
strSQL = "SELECT * INTO [Excel 8.0;Database=" & App.Path & _
"\book1.xls].[Sheet1] FROM Customers"
strSQL = "SELECT * INTO [Sheet1] IN '' [Excel 8.0;Database=" & App.Path & _
"\book1.xls] FROM Customers"
strSQL = "SELECT * INTO [Sheet1] IN ' " & App.Path & _
"\book1.xls' 'Excel 8.0;' FROM Customers"

2) Append to existing sheet:
strSQL = "INSERT INTO [Sheet1$] IN '' [Excel 8.0;Database=" & App.Path & _
"\book1.xls] SELECT * FROM Customers"
strSQL = "INSERT INTO [Sheet1$] IN '" & App.Path & _
"\book1.xls' 'Excel 8.0;' SELECT * FROM Customers"

After you have defined strSQL, use an Execute stmt.

DAO:

CurrentDb.Execute strSQL, dbFailOnError

or ADO:

CurrentProject.ActiveConnection.Execute strSQL, dbFailOnError

I *think* the truncation is a result of trying to *link*
to the Excel "table."

Of course, I could be wrong.

Good luck,

Gary Walter

 

[Gary Walter]

that should be

or ADO:
CurrentProject.Connection.Execute strSQL, dbFailOnError

I have a query which contains some memo fields. I am using Office links to
transfer the data to Excel.
There are about 500 characters in some of the memo fields. All the data is
displayed as expected in the query, but when the data is transferred to
Excel the data in the memo field cells is cut off.

Is there a way to make sure all data from a memo field is transferred to
Excel? ( I would be happy with a limit of even 1000 characters)
Any help greatly appreciated

Hi John,

Did you try using SQL?

The following examples are from
http://support.microsoft.com/?kbid=295646

(replace "Customer" with name of your query,
and you could just enter path to xls, instead of using
"App.Path")

1) Copy to new sheet:
strSQL = "SELECT * INTO [Excel 8.0;Database=" & App.Path & _
"\book1.xls].[Sheet1] FROM Customers"
strSQL = "SELECT * INTO [Sheet1] IN '' [Excel 8.0;Database=" & App.Path & _
"\book1.xls] FROM Customers"
strSQL = "SELECT * INTO [Sheet1] IN ' " & App.Path & _
"\book1.xls' 'Excel 8.0;' FROM Customers"

2) Append to existing sheet:
strSQL = "INSERT INTO [Sheet1$] IN '' [Excel 8.0;Database=" & App.Path & _
"\book1.xls] SELECT * FROM Customers"
strSQL = "INSERT INTO [Sheet1$] IN '" & App.Path & _
"\book1.xls' 'Excel 8.0;' SELECT * FROM Customers"

After you have defined strSQL, use an Execute stmt.

DAO:

CurrentDb.Execute strSQL, dbFailOnError

or ADO:

CurrentProject.ActiveConnection.Execute strSQL, dbFailOnError

I *think* the truncation is a result of trying to *link*
to the Excel "table."

Of course, I could be wrong.

Good luck,

Gary Walter

 

[John Baker]

Thanks very much for your help Gary.

I tried your method but am having trouble getting it running successfully.

The code keeps stopping on the line:
CurrentDb.Execute strSQL, dbFailOnError

It gives the message: "Could not find installable ISAM"
Not sure why?

The code I used was:

Private Sub cmdConvertQueryToExcelWithSQL_Click()
Dim strSQL As String
strSQL = "SELECT * INTO [Excel 9.0;Database= C:\Database By
Design\Amcor\FRC\Hazard Management\GMMReportADO.xls].[Sheet1] FROM
qryGGMReport"
CurrentDb.Execute strSQL, dbFailOnError
End Sub


Any help greatly appreciated.

John.

I have a query which contains some memo fields. I am using Office links to
transfer the data to Excel.
There are about 500 characters in some of the memo fields. All the data is
displayed as expected in the query, but when the data is transferred to
Excel the data in the memo field cells is cut off.

Is there a way to make sure all data from a memo field is transferred to
Excel? ( I would be happy with a limit of even 1000 characters)
Any help greatly appreciated

Hi John,

Did you try using SQL?

The following examples are from
http://support.microsoft.com/?kbid=295646

(replace "Customer" with name of your query,
and you could just enter path to xls, instead of using
"App.Path")

1) Copy to new sheet:
strSQL = "SELECT * INTO [Excel 8.0;Database=" & App.Path & _
"\book1.xls].[Sheet1] FROM Customers"
strSQL = "SELECT * INTO [Sheet1] IN '' [Excel 8.0;Database=" & App.Path & _
"\book1.xls] FROM Customers"
strSQL = "SELECT * INTO [Sheet1] IN ' " & App.Path & _
"\book1.xls' 'Excel 8.0;' FROM Customers"

2) Append to existing sheet:
strSQL = "INSERT INTO [Sheet1$] IN '' [Excel 8.0;Database=" & App.Path & _
"\book1.xls] SELECT * FROM Customers"
strSQL = "INSERT INTO [Sheet1$] IN '" & App.Path & _
"\book1.xls' 'Excel 8.0;' SELECT * FROM Customers"

After you have defined strSQL, use an Execute stmt.

DAO:

CurrentDb.Execute strSQL, dbFailOnError

or ADO:

CurrentProject.ActiveConnection.Execute strSQL, dbFailOnError

I *think* the truncation is a result of trying to *link*
to the Excel "table."

Of course, I could be wrong.

Good luck,

Gary Walter

 

[Gary Walter]

Hi John,

I am not an Excel expert....

I mostly work in Office 2000,
and although my Excel version is 9.0.6926,
when I go into the Registry, the "largest"
ISAM Format for Excel is 8.0:

HKLM\Software\Microsoft\Jet\4.0\ISAM Formats\
--Excel 3.0
--Excel 4.0
--Excel 5.0
--Excel 8.0
-----ExportFilter = "Microsoft Excel 97-2000 (*.xls)"

All the queries I have run using the methods outlined
previously have always succeeded using "Excel 8.0",
not "Excel 9.0"

Please respond back if I have misunderstood
or was not clear about something.

Good luck,

Gary Walter

I tried your method but am having trouble getting it running successfully.

The code keeps stopping on the line:
CurrentDb.Execute strSQL, dbFailOnError

It gives the message: "Could not find installable ISAM"
Not sure why?

The code I used was:

Private Sub cmdConvertQueryToExcelWithSQL_Click()
Dim strSQL As String
strSQL = "SELECT * INTO [Excel 9.0;Database= C:\Database By
Design\Amcor\FRC\Hazard Management\GMMReportADO.xls].[Sheet1] FROM
qryGGMReport"
CurrentDb.Execute strSQL, dbFailOnError
End Sub


Any help greatly appreciated.

John.

I have a query which contains some memo fields. I am using Office links to
transfer the data to Excel.
There are about 500 characters in some of the memo fields. All the data is
displayed as expected in the query, but when the data is transferred to
Excel the data in the memo field cells is cut off.

Is there a way to make sure all data from a memo field is transferred to
Excel? ( I would be happy with a limit of even 1000 characters)
Any help greatly appreciated

Hi John,

Did you try using SQL?

The following examples are from
http://support.microsoft.com/?kbid=295646

(replace "Customer" with name of your query,
and you could just enter path to xls, instead of using
"App.Path")

1) Copy to new sheet:
strSQL = "SELECT * INTO [Excel 8.0;Database=" & App.Path & _
"\book1.xls].[Sheet1] FROM Customers"
strSQL = "SELECT * INTO [Sheet1] IN '' [Excel 8.0;Database=" & App.Path & _
"\book1.xls] FROM Customers"
strSQL = "SELECT * INTO [Sheet1] IN ' " & App.Path & _
"\book1.xls' 'Excel 8.0;' FROM Customers"

2) Append to existing sheet:
strSQL = "INSERT INTO [Sheet1$] IN '' [Excel 8.0;Database=" & App.Path & _
"\book1.xls] SELECT * FROM Customers"
strSQL = "INSERT INTO [Sheet1$] IN '" & App.Path & _
"\book1.xls' 'Excel 8.0;' SELECT * FROM Customers"

After you have defined strSQL, use an Execute stmt.

DAO:

CurrentDb.Execute strSQL, dbFailOnError

or ADO:

CurrentProject.ActiveConnection.Execute strSQL, dbFailOnError

I *think* the truncation is a result of trying to *link*
to the Excel "table."

Of course, I could be wrong.

Good luck,

Gary Walter

 

[Ken Snell]

Use the TransferSpreadsheet action in a macro or in VBA code.

The Export option from File menu uses older format of EXCEL (95, I believe),
which will not export any text longer than 255 characters.

You can specify a newer version's format in TransferSpreadsheet, and the
memo field will be exported in its entirety that way.

 

[John Baker]

Thanks Gary.
I changed the Excel 9.0 to Excel 8.0 and it works!

The only problem is that it hasn't solved my issue. The memo fields are
still coming across as truncated.
I also tried the DoCmd.TransferSpreadsheet method but again the memo fields
come across as truncated.

Any ideas?

John.

Hi John,

I am not an Excel expert....

I mostly work in Office 2000,
and although my Excel version is 9.0.6926,
when I go into the Registry, the "largest"
ISAM Format for Excel is 8.0:

HKLM\Software\Microsoft\Jet\4.0\ISAM Formats\
--Excel 3.0
--Excel 4.0
--Excel 5.0
--Excel 8.0
-----ExportFilter = "Microsoft Excel 97-2000 (*.xls)"

All the queries I have run using the methods outlined
previously have always succeeded using "Excel 8.0",
not "Excel 9.0"

Please respond back if I have misunderstood
or was not clear about something.

Good luck,

Gary Walter

I tried your method but am having trouble getting it running successfully.

The code keeps stopping on the line:
CurrentDb.Execute strSQL, dbFailOnError

It gives the message: "Could not find installable ISAM"
Not sure why?

The code I used was:

Private Sub cmdConvertQueryToExcelWithSQL_Click()
Dim strSQL As String
strSQL = "SELECT * INTO [Excel 9.0;Database= C:\Database By
Design\Amcor\FRC\Hazard Management\GMMReportADO.xls].[Sheet1] FROM
qryGGMReport"
CurrentDb.Execute strSQL, dbFailOnError
End Sub


Any help greatly appreciated.

John.

I have a query which contains some memo fields. I am using Office

links
to

transfer the data to Excel.
There are about 500 characters in some of the memo fields. All the

data
is

displayed as expected in the query, but when the data is transferred to
Excel the data in the memo field cells is cut off.

Is there a way to make sure all data from a memo field is transferred to
Excel? ( I would be happy with a limit of even 1000 characters)
Any help greatly appreciated

Hi John,

Did you try using SQL?

The following examples are from
http://support.microsoft.com/?kbid=295646

(replace "Customer" with name of your query,
and you could just enter path to xls, instead of using
"App.Path")

1) Copy to new sheet:
strSQL = "SELECT * INTO [Excel 8.0;Database=" & App.Path & _
"\book1.xls].[Sheet1] FROM Customers"
strSQL = "SELECT * INTO [Sheet1] IN '' [Excel 8.0;Database=" &

App.Path &
_

"\book1.xls] FROM Customers"
strSQL = "SELECT * INTO [Sheet1] IN ' " & App.Path & _
"\book1.xls' 'Excel 8.0;' FROM Customers"

2) Append to existing sheet:
strSQL = "INSERT INTO [Sheet1$] IN '' [Excel 8.0;Database=" & App.Path & _
"\book1.xls] SELECT * FROM Customers"
strSQL = "INSERT INTO [Sheet1$] IN '" & App.Path & _
"\book1.xls' 'Excel 8.0;' SELECT * FROM Customers"

After you have defined strSQL, use an Execute stmt.

DAO:

CurrentDb.Execute strSQL, dbFailOnError

or ADO:

CurrentProject.ActiveConnection.Execute strSQL, dbFailOnError

I *think* the truncation is a result of trying to *link*
to the Excel "table."

Of course, I could be wrong.

Good luck,

Gary Walter

 

[Gary Walter]

Hi John,

Again, I am not an Excel expert, so here
is what I think I know.....

First, I have used this method to successfully
transfer *untruncated* memo fields from a
query to Excel. If the query "groups by" (or does
any kind of "sorting" on the memo field), in many
cases (not just Excel) this results in memo truncation,
but you have said that the field is not truncated in the
query.

In 97-2000 format, I believe max length for a text
column is 32,000 chars (Excel 5.0/95 format was 255
I believe).

In any "transfer" (to a text file, for example, as well as
to an Excel file), there is a registry setting that tells the
Jet engine how many rows to look at before guessing
what type of data a certain field is. The default is 25 rows.

It could be that it is looking at the first 25 rows in your
query and saying, "oh, this is just a 255 char text field."
Is it possible that the first 25 records returned from your
query will have memo field data < 256 chars?

It *might be* that sorting your query on the
length of the memo field DESC *may* be enough
to solve your problem.

Are you comfortable editing the Registry?

HKLM\Software\Microsoft\Jet\4.0\Engines\Excel
---TypeGuessRows 0x00000019 (25)

*I believe* that setting this value to 0 will force
it to look at *all* the records before determining
what type it is, but I don't want to be responsible
for you muffing up your registry....plus, you don't
want to have to do this on every computer your
app will be deployed on.

If this *is* the problem, another alternative might
be to UNION a "dummy record" with your
query where the dummy record would have
a string >255 chars in the field column corresponding
to your memo field.

If the above does not solve your problem, would
you mind posting the SQL for your query?

Thanks,

Gary Walter

I changed the Excel 9.0 to Excel 8.0 and it works!

The only problem is that it hasn't solved my issue. The memo fields are
still coming across as truncated.
I also tried the DoCmd.TransferSpreadsheet method but again the memo fields
come across as truncated.

Any ideas?

John.

Hi John,

I am not an Excel expert....

I mostly work in Office 2000,
and although my Excel version is 9.0.6926,
when I go into the Registry, the "largest"
ISAM Format for Excel is 8.0:

HKLM\Software\Microsoft\Jet\4.0\ISAM Formats\
--Excel 3.0
--Excel 4.0
--Excel 5.0
--Excel 8.0
-----ExportFilter = "Microsoft Excel 97-2000 (*.xls)"

All the queries I have run using the methods outlined
previously have always succeeded using "Excel 8.0",
not "Excel 9.0"

Please respond back if I have misunderstood
or was not clear about something.

Good luck,

Gary Walter

I tried your method but am having trouble getting it running successfully.

The code keeps stopping on the line:
CurrentDb.Execute strSQL, dbFailOnError

It gives the message: "Could not find installable ISAM"
Not sure why?

The code I used was:

Private Sub cmdConvertQueryToExcelWithSQL_Click()
Dim strSQL As String
strSQL = "SELECT * INTO [Excel 9.0;Database= C:\Database By
Design\Amcor\FRC\Hazard Management\GMMReportADO.xls].[Sheet1] FROM
qryGGMReport"
CurrentDb.Execute strSQL, dbFailOnError
End Sub


Any help greatly appreciated.

John.



I have a query which contains some memo fields. I am using Office links
to
transfer the data to Excel.
There are about 500 characters in some of the memo fields. All the data
is
displayed as expected in the query, but when the data is transferred to
Excel the data in the memo field cells is cut off.

Is there a way to make sure all data from a memo field is transferred to
Excel? ( I would be happy with a limit of even 1000 characters)
Any help greatly appreciated

Hi John,

Did you try using SQL?

The following examples are from
http://support.microsoft.com/?kbid=295646

(replace "Customer" with name of your query,
and you could just enter path to xls, instead of using
"App.Path")

1) Copy to new sheet:
strSQL = "SELECT * INTO [Excel 8.0;Database=" & App.Path & _
"\book1.xls].[Sheet1] FROM Customers"
strSQL = "SELECT * INTO [Sheet1] IN '' [Excel 8.0;Database=" & App.Path &
_
"\book1.xls] FROM Customers"
strSQL = "SELECT * INTO [Sheet1] IN ' " & App.Path & _
"\book1.xls' 'Excel 8.0;' FROM Customers"

2) Append to existing sheet:
strSQL = "INSERT INTO [Sheet1$] IN '' [Excel 8.0;Database=" & App.Path & _
"\book1.xls] SELECT * FROM Customers"
strSQL = "INSERT INTO [Sheet1$] IN '" & App.Path & _
"\book1.xls' 'Excel 8.0;' SELECT * FROM Customers"

After you have defined strSQL, use an Execute stmt.

DAO:

CurrentDb.Execute strSQL, dbFailOnError

or ADO:

CurrentProject.ActiveConnection.Execute strSQL, dbFailOnError

I *think* the truncation is a result of trying to *link*
to the Excel "table."

Of course, I could be wrong.

Good luck,

Gary Walter