If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
outputting memo fields to excel
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 |
#2
|
|||
|
|||
outputting memo fields to excel
"John Baker" wrote in message ... 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 |
#3
|
|||
|
|||
outputting memo fields to excel
that should be
or ADO: CurrentProject.Connection.Execute strSQL, dbFailOnError "Gary Walter" wrote: "John Baker" wrote in message ... 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 |
#4
|
|||
|
|||
outputting memo fields to excel
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. "Gary Walter" wrote in message ... "John Baker" wrote in message ... 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 |
#5
|
|||
|
|||
outputting memo fields to excel
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 "John Baker" wrote: 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. "Gary Walter" wrote in message ... "John Baker" wrote in message ... 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 |
#6
|
|||
|
|||
outputting memo fields to excel
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. -- Ken Snell MS ACCESS MVP "John Baker" wrote in message ... 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 |
#7
|
|||
|
|||
outputting memo fields to excel
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. "Gary Walter" wrote in message ... 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 "John Baker" wrote: 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. "Gary Walter" wrote in message ... "John Baker" wrote in message ... 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 |
#8
|
|||
|
|||
outputting memo fields to excel
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 "John Baker" wrote: 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. "Gary Walter" wrote in message ... 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 "John Baker" wrote: 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. "Gary Walter" wrote in message ... "John Baker" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Memo fields and ODBC tables | blogan | General Discussion | 0 | July 15th, 2004 07:03 PM |
Coverting Lotus 123 files to use with excel | Muffin1947 | General Discussion | 6 | June 20th, 2004 10:18 AM |
Export Inbox fields from Outlook 2000 to Excel | Eileen | General Discussion | 1 | June 11th, 2004 01:11 PM |
UNION Query truncating Memo fields | Matthew DeAngelis | Running & Setting Up Queries | 4 | June 10th, 2004 02:17 PM |
Memo fields in queries | L Wick | Running & Setting Up Queries | 0 | June 10th, 2004 02:28 AM |