Results 1 to 3 of 3
  1. #1

    Exporting to txt/xls with >255 chars (2002)

    When I run an export:
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "somequery", somefile, False
    DoCmd.TransferText acExportDelim, , "somequery", somefile, False
    text > 255 is truncated in the export.
    MS has this link:
    <A target="_blank" HREF=></A>
    a solution which doesn't work

    I tried opening a file:
    Open strFile For Output Access Write As #1
    and writing each field, but get garbage in the output when it gets to the 255th character.

    Any ideas?

    Edited to actiate link--Charlotte

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Sacramento, California, USA
    Thanked 1 Time in 1 Post

    Re: Exporting to txt/xls with >255 chars (2002)

    You're doing two exports. Which one doesn't work as expected?

    If I use TransferSpreadsheet from Access 2000 and specify an Excel9 format, the memo fields export to Excel 2000 without being truncated. Unfortunately, they contain non-printing characters for carriage returns, etc., but they don't truncate.

    I'm not surprised the TransferText doesn't work because unless you use an import/export spec, you never know exactly how it's going to be exported and you have very little control. Using acExportMerge will give you a tab-delimited text file, which is a bit better; but it just doesn't handle memo fields well at all.

    Memo fields are not actually true text fields, so you wouldn't be able to write them directly to a text file except possibly by defining a user-defined type, using that to determine the record length and then writing the udt to the file instead of trying to pass it as text.

  3. #3

    Re: Exporting to txt/xls with >255 chars (2002)

    Neither one worked. But, I did solve the problem. I create a table that matches the structure of the query, using Memo field(s) in place of the query (calculated) text field(s). I run an append query (after deleting the records) and run the export against the table with the memo field, rather than directly off the query.

Posting Permissions

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