Unanswered: Exporting from MS Access to MS Excel vba - memo field being truncated
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?
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).
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.
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.