Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2005
    Posts
    67

    Unanswered: Exporting from MS Access to MS Excel vba - memo field being truncated

    Hi.

    my colleague has tried exporting data from MS Access to Excel using vba. It is working, but the memo field is being truncated. Is there a way of specifying the size of the memo field in the code so that when exported into excel, all the data will show in Excel?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Below re cell limits in Excel
    Quote Originally Posted by Excel Help
    Length of cell contents (text)32,767 characters. Only 1,024 display in a cell; all 32,767 display in the formula bar.
    Arguably, Excel is not the appropriate choice to display data of the sort of scale of memo fields. Having said that - I have exceeded some of excel's cell limits too in my time - I split the data into n cells (where n was something like MyMemoLen\ 1024 +1).

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Dec 2005
    Posts
    67

    Truncating Memo Fields

    Thanks, but how do you write that in vba using the transferspreadsheet function? Or is there another way it can be written in vba?

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by tasha123
    Thanks, but how do you write that in vba using the transferspreadsheet function? Or is there another way it can be written in vba?
    I'm afraid you don't and you can't. I used a different method and I was not exporting sets of results - just one single result.

    Memo fields are useful for large, free text descriptions. They tend to really be used to add flesh to an individual record rather than in sets (which is what Excel really displays). I can't really see why you would want to have a column of memo datatype in Excel.

    However - another alternative (untested) might be to have your query chop the text into several columns using mid e.g.
    Code:
    SELECT ..... MID(MemoCol, 1, 1024) AS Memo1, MID(MemoCol, 1025, 1024) AS Memo2, .... etc
    It's ugly and I don't even know if mid can handle those sorts of numbers but... a memo field in an excel data set will be ugly too.

    Sorry I can't help more - maybe someone else has a bright idea.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •