Export to excel truncates to 255 chars...

[Shakss2]

Hi...

I am exporting some data from a table with a memo field to excel but the excel report truncates the memo field data to 255 chars.

Dim strPath As String
    ' Current path
    strPath = CurrentProject.Path & "\Adhoc_Report.xls"
    'New workbook object
    Dim sh As Workbook
    DoCmd.OutputTo acOutputTable, "test2", acSpreadsheetTypeExcel9, strPath, 1
    'Setting obj
    Set sh = CreateObject(strPath)
    'sh.ActiveSheet.Cells.EntireColumn.AutoFit
    sh.ActiveSheet.Cells.ColumnWidth = 23
    sh.ActiveSheet.Cells.EntireRow.AutoFit

Please help...

Thanks
Shaq

[Stewart Ross]

...I am exporting some data from a table with a memo field to excel but the excel report truncates the memo field data to 255 chars.

Hi. Excel text is a maximum of 255 chars (like an Access string). What is in your memo field, and is it frequently more than 255 characters long? You could split the memo data into multiple text fields in your export query, each no more than 255 characters long, but this will look messy in the Excel sheet. All depends on what you use that memo field for...

-Stewart

[Shakss2]

Hi...

Thanks for ur reply.
The memo field has all text data in it & it is often more than 255 chars.

If u could help me with some code which U have suggested than it will be of great help.

As a beginner im not sure if I answered ur Q's.

Thanks again.

Shaq

[Stewart Ross]

...The memo field has all text data in it & it is often more than 255 chars.

Hi again Shaq. In the circumstances (and against my better judgment!) the simplest solution I can think of is a brute-force one of adding additional fields that split up your memo field into a series of sub-fields, each of no more than 255 characters. It's not elegant at all, but it would allow you to retain the simple Excel export you currently use. Perhaps other contributors will be able to suggest better ways...

Although you have mentioned that the memo field is more than 255 characters you haven't said how long it is in typical use. You can use the Len() string function to find out, if you are at all familiar with using functions in queries.

In the DoCmd line of your code the exported table is called test2. If this is a query you can add new columns beside your memo field to split up the contents of your memo. If test2 is a table then you need to start by creating a query based on test2 so that you can add the additional memo fields. Include all the fields that are currently in test2.

Assuming that your memo is no more than 4x255 characters long (1020 chars), open the query in design view and add three blank columns beside your memo field. Add the following field names in the three blank columns, in the rows marked field: substituting the name of your current memo field in each case:

Memo Pt 2: Mid$(Nz([name of memo field]), 256, 255)
Memo Pt 3: Mid$(Nz([name of memo field]), 511, 255)
Memo Pt 4: Mid$(Nz([name of memo field]), 766, 255)

The mid$ (mid-string) function in each of these will extract 255 characters from your memo field at positions 256, 511, 766 respectively. Nz() ensures that if the field is null Mid$ is still fed by a string (although zero-length). Mid$ does not fail if there are no characters to return - it just returns a zero-length string.

If you added the three columns to a new query based on test2 save it under a suitable name (such as qryTest2) then change your Docmd statement to refer to qryTest2 instead of test2.

These are the only changes you will need to make to at least be able to export more of your memo field in this brute-force way. If however you need more characters still I think a very different solution will be required - because it is not really appropriate to go on taking 255 character chunks out of your memo field just because that is Excel's text limit.

-Stewart

ps if you are working in SQL view you can add the following to your select statement to do the same job as adding the three columns above:

 
select ..., [name of memo field], Mid$(Nz([name of memo field]), 256, 255) as [Memo Pt 2], Mid$(Nz([name of memo field]), 511, 255) as [Memo Pt 3], Mid$(Nz([name of memo field]), 766, 255) as [Memo Pt 4], ...

[missinglinq]

What kind of information does this memo field hold and why are you exporting it to Excel? Excel is pretty much used exclusively to manipulate data (read "crunch numbers") and the one thing that is absolutely verboden is in Access is the storing of data in memo fields that will ever, in any way, shape or form, need to be manipulated.

Maybe we can come up with a better solution.

Linq ;0)>

[Shakss2]

Hi...

Thanks a lot for helping and apologies on replying so late.

Your logic worked... Thank U all for the help.

This is what i did:
lsttest_criteri a = lsttest_criteri a & "," & "Forecast_Train ing AS Training_Inform ation" & ""
lsttest_criteri a = lsttest_criteri a & "," & " Mid(Nz(Forecast _Training),256, 255) as T1 " & ""
lsttest_criteri a = lsttest_criteri a & "," & " Mid(Nz(Forecast _Training),512, 255) as T2 " & ""
lsttest_criteri a = lsttest_criteri a & "," & " Mid(Nz(Forecast _Training),768, 255) as T3 " & ""

The used the below code to concatinate the data in 4 cells to 1 and then delete the other 3 cells

lrows = sh.ActiveSheet. UsedRange.Rows. COUNT


For i = 1 To 256
If Trim(sh.ActiveS heet.Cells(1, i)) = "Training_Infor mation" Then
For J = 2 To lrows
sh.ActiveSheet. Cells(J, i) = Trim(sh.ActiveS heet.Cells(J, i)) & Trim(sh.ActiveS heet.Cells(J, i + 1)) & Trim(sh.ActiveS heet.Cells(J, i + 2)) & Trim(sh.ActiveS heet.Cells(J, i + 3))

Next J

sh.ActiveSheet. Columns(i + 1).Delete
sh.ActiveSheet. Columns(i + 1).Delete
sh.ActiveSheet. Columns(i + 1).Delete

Exit For
End If
Next

Shaq