469,286 Members | 2,442 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,286 developers. It's quick & easy.

memo field get truncated while exporting into excel with docmd.transferspreadsheet

hi all,
I'm using Access 2007 and when I'm trying to export a query by using DoCmd.transferspreadsheet. I get my memo field truncated into 255 character.
I searched the web for answers and I did see many, but nothing helped me.
Is there any simple solution to get rid off this pain in the...

just pack of facts for your knowledge:
1. I can see full text(memo) in my query.
2. when I try to export query by GUI it works like a charm.
3. my export isn't simple, it exports a sheet by value.(so I can't really use GUI-export).
4.I've tried use DoCmd.OutputTo (and it worked fine) but it didn't worked out with the sheets separation (3).

thanks for any kind of help
Uriya.
Nov 9 '11 #1

✓ answered by ADezii

@Uriya:
Let's suppose that you have a fictitious Field named [Notes] in your Database, and that it is a MEMO Field. The following Query will split that Field into 5 Segments with Field Names Notes1...Notes5. This Query can now be Exported to Excel, where the Fields can then be reassembled via the CONCATENATE() Function which can be found in the Help Files.
Expand|Select|Wrap|Line Numbers
  1. SELECT Mid([Notes],1,250) AS Notes1,
  2.        Mid([Notes],251,501) AS Notes2, 
  3.        Mid([Notes],501,250) AS Notes3, 
  4.        Mid([Notes],751,250) AS Notes4, 
  5.        Mid([Notes],1001,250) AS Notes5
  6. FROM tblTest;
  7.  

11 7560
NeoPa
32,173 Expert Mod 16PB
It's a known limitation. What do you want? I would have thought this this would be understood after reading about it as you have.

I know little of your design but all I could suggest would be that you stopped using the field as a Memo.
Nov 11 '11 #2
ADezii
8,800 Expert 8TB
This is a well known problem with Exporting MEMO Fields in a Query to Excel. There are a couple of things you can try to circumvent this behavior:
  1. Export to Excel 2000 Format, namely:
    Expand|Select|Wrap|Line Numbers
    1. DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryNotes", CurrentProject.Path & "\Notes.xls"
  2. Break the Field into Segments, perform the Export, and then reassemble in Excel, namely:
    Expand|Select|Wrap|Line Numbers
    1. 'Divide Field into 250 Character 'Chunks'
    2. Notes1: Mid([MEMO Field],1,250)
    3. Notes2: Mid([MEMO Field],251,501)
    4. Notes3: Mid([MEMO Field],501,250)
    5. Notes4: Mid([MEMO Field],751,250)
    6. Notes5: Mid([MEMO Field],1001,250)
    7. 'etc...
    8.  
    Expand|Select|Wrap|Line Numbers
    1. In Excel, reassemble:
    2. =CONCATENATE(B2,C2,D2,E2,F2)
Nov 11 '11 #3
thanks for reply ADezii, NeoPa.
NeoPa- I have to use memo cuz it need to contain more then 255 chars.
ADezii- the first solution you gave much more possible for me (im quite a novice). but I do an export with
Expand|Select|Wrap|Line Numbers
  1. DoCmd.TransferSpreadsheet
and using acSpreadsheetTypeExcel9.
and it doesnt work...
Nov 11 '11 #4
NeoPa
32,173 Expert Mod 16PB
Have you considered ADezii's second option? Whereby you output multiple fields for the single Memo field in your table.

If this really is your requirement then it may be worth the extra complication of such a solution. Application Automation should enable you to merge these fields into a single column in the spreadsheet after the TransferSpreadsheet command has completed.

I admit I have no knowledge why Excel 2000 might handle it differently from other versions, but if ADezii says it's worth a try then it probably is.
Nov 11 '11 #5
well I don't mine to try ADezii's second options but I don't know how to integrate it with my code(cuz I don't no VBA so well)...I don't mine to share it so you could help me...
Nov 12 '11 #6
NeoPa
32,173 Expert Mod 16PB
You'll need to be clearer than that and ask for help properly. Expecting this to be done for you is not a good approach. There are threads already explaining this but you need to ask for specific help if you want assistance (Assistance <> doing it for you).
Nov 12 '11 #7
Neopa, I'm sorry that you think that Im expecting someone will do the work for me. I've never tried to even imply this. :)
the only thing Im trying to do is to find any solution here. I don't have a lot of experience with VB as I mentioned. so yes I would like to get some comprehension to help solve this issue
thanks for replying.
Uriya
Nov 13 '11 #8
ADezii
8,800 Expert 8TB
@Uriya:
Let's suppose that you have a fictitious Field named [Notes] in your Database, and that it is a MEMO Field. The following Query will split that Field into 5 Segments with Field Names Notes1...Notes5. This Query can now be Exported to Excel, where the Fields can then be reassembled via the CONCATENATE() Function which can be found in the Help Files.
Expand|Select|Wrap|Line Numbers
  1. SELECT Mid([Notes],1,250) AS Notes1,
  2.        Mid([Notes],251,501) AS Notes2, 
  3.        Mid([Notes],501,250) AS Notes3, 
  4.        Mid([Notes],751,250) AS Notes4, 
  5.        Mid([Notes],1001,250) AS Notes5
  6. FROM tblTest;
  7.  
Nov 13 '11 #9
NeoPa
32,173 Expert Mod 16PB
Uriya:
I've never tried to even imply this
Nevertheless, you managed to without trying.

As I said in an earlier post there are threads which explain how you should ask questions and if you'd read any of them (instead of simply denying you'd got anything wrong) you would have read how you should be posting the work you have already done in order to :
  1. Indicate you have already done or attempted some work on your own.
  2. Give the experts a context within which to work. There are often many ways to handle a problem and knowing which you are thinking about makes any answer more appropriate to the question.
  3. Give various details often missing from the explanation such as object names. A properly asked question wouldn't absolutely need this, but very few questions are asked well enough for that. This is no exception.

Uriya:
4.I've tried use DoCmd.OutputTo (and it worked fine) but it didn't worked out with the sheets separation (3).
If you show us this code I'm sure we could deduce where you're coming from and help you to formulate something more appropriate from it. We can also help you with instruction when we have a better feel for exactly what you do and don't understand.
Nov 13 '11 #10
ADezii, I've tried what you've suggested but I didn't know how to do it right (I added it in this post)...
I did a little research and I find out the transferspreadsheet does not export to excel 2007 (*.xlsx) and outputto method can't do multiple worksheets...

there is any workaround for exporting access 2007 queries into excel 2007?

my code :
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Sub cmdExport_Click()
  5.     Dim strEnts As String, strSQL As String, strFName As String, skelFN As String
  6.     Dim cdb As DAO.Database
  7.     Dim varEnt As Variant
  8.     Set cdb = CurrentDb
  9.  
  10.     On Error GoTo Error_cmdExport
  11.  
  12.     strFName = CurrentProject.Path & "\Hoveret_Milgot.xlsx"
  13.     skelFN = CurrentProject.Path & "\skel\skel.xlsx"
  14.     strEnts = ""
  15.     FileCopy skelFN, strFName
  16.  
  17.     strSQL = "SELECT DISTINCT [entitled] FROM [scholl]"
  18.     With cdb.OpenRecordset(strSQL, dbOpenSnapshot)
  19.         Call .MoveFirst
  20.         Do While Not .EOF
  21.             strEnts = strEnts & "," & !entitled
  22.             Call .MoveNext
  23.         Loop
  24.         Call .Close
  25.     End With
  26.     strEnts = Mid(strEnts, 2)
  27.  
  28.     For Each varEnt In Split(strEnts, ",")
  29.        If Len(CStr(varEnt)) = 0 Then
  30.        GoTo Continue1
  31.         End If
  32.         Call DoCmd.CopyObject(, varEnt, acQuery, "query1")
  33.  
  34.         Set cdb = CurrentDb
  35.         With cdb.QueryDefs(varEnt)
  36.             strSQL = Replace(.SQL, ";", "") & "WHERE [entitled] = '%E'"
  37.             .SQL = Replace(strSQL, "%E", varEnt)
  38.             Application.RefreshDatabaseWindow
  39.  
  40.         End With
  41.            Call DoCmd.TransferSpreadsheet(acExport, acSpreadsheetTypeExcel12Xml, varEnt, strFName, True)
  42.             'DoCmd.OutputTo acOutputQuery, varEnt, acFormatXLSX, strFName, False
  43.  
  44.        DoCmd.DeleteObject acQuery, varEnt
  45.  
  46.         Set cdb = Nothing
  47. Continue1:
  48.     Next varEnt
  49.  
  50.     Call MsgBox("finished", vbOKOnly, "Excel export")
  51.     Exit Sub
  52.  
  53. Error_cmdExport:
  54.     MsgBox "Error {" & Err & "}" & vbNewLine & Err.Description, vbOKOnly Or vbExclamation, "cmdExport"
  55.  
  56.                 Title:="cmdExport")
  57. End Sub
  58.  
what my code does is it get every entitled (distinct) in scholler table, and by it create a query (that contain some fields (including MEMO type)) and then export it (or trying to) into xlsx file in different sheet.
Attached Files
File Type: txt bytes_question.txt (1.8 KB, 254 views)
Nov 22 '11 #11
got it fixed!!!!
after I used ADezii's solution, I find that in my select I get a lot of HTML tags (such as <div>, <font> ,etc) so I went to my MEMO feild settings and change 'Text pattern' from HTML to SIMPLE
and it works. now I can export with TransferSpreadsheet more then 255 words.
thank all for the support,
Uriya
Nov 22 '11 #12

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.