?
Solved

Unwanted CrLf squares in Excel file from Access

Posted on 2009-12-17
18
Medium Priority
?
726 Views
Last Modified: 2012-05-08
I have Author Bio and book description data in memo fields in Access.  I am regularly exporting lengthy memo data from Access to Excel.  Excel doesn't like the Cr\Lfs and shows up as squares.

I want to maintain the line breaks in the Excel file, not just replace with spaces.  I could replace them in the Excel file (alt+Enter), but I need to fix this in the source data(Access) for recurring exports.  Maybe a replace function to run on the memo fields in a temp output table prior to running the transferspreadsheet routine?  Help!

0
Comment
Question by:shaun37
  • 8
  • 8
  • 2
18 Comments
 
LVL 16

Assisted Solution

by:Calvin Brine
Calvin Brine earned 200 total points
ID: 26075505
You should be able to just turn on the cell word wrap in the format options.
 
HTH
Cal
0
 
LVL 18

Expert Comment

by:Simon
ID: 26075531
Use this replace function in your query. When you export the query results to Excel, choose the 'export as formated' option, or use the 'analyze it with Excel' option.

mem1:replace([myMemoField],chr(13) & chr(10),chr(13))

do that with any memo fields in your export query.
0
 

Author Comment

by:shaun37
ID: 26075967
No, because the characters are already part of the data.  Data is entered into an Access Db, then exported to Excel.  It's going to require some sort of clever Find\Replace, I'm afraid. But I'd rather do this to the data inside of Access, if possible, so that users won't have to "clean it up" everytime they pull data.

Shaun
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:shaun37
ID: 26076028
Simon, this looks a bit more like it.  Remember, I'm using the transferspreadsheet process to do this through code. Eager to check this out!

Shaun
0
 

Author Comment

by:shaun37
ID: 26076522
Tried the Analyze with Excel and that's promising, except it truncates field data to 255 characters.

Shaun
0
 
LVL 18

Expert Comment

by:Simon
ID: 26076668
I've got an alternative for you....

Paste the attached code into a new module and add a reference to "Microsoft Excel (version whatever)" in the VB editor Tools Menu /References.

I've tested that this will export to Excel without truncation or boxes for linebreaks. the TransferSpreadsheet macro method still includes the boxes, even with my replace function.

You could modify the attached code to pass the name of the query to export as a string parameter.
Option Compare Database

Function ExportToExcel()
Dim rsData As DAO.Recordset

Dim objWB As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim objRange As Excel.Range

Dim x As Integer

Set rsData = CurrentDb.QueryDefs("qryExportMemo").OpenRecordset
If Not rsData.EOF Then

    Set objexcel = CreateObject("excel.Application")

    objexcel.Visible = True
    Set objWB = objexcel.Workbooks.Add
    Set objSht = objWB.ActiveSheet
    
    'Write the field names
    For x = 0 To rsData.Fields.Count - 1
    objSht.Cells(1, x + 1).Value = rsData.Fields(x).Name
    Next x
    Set objRange = objSht.Range("A2:A2")
    Debug.Print objRange.Address
    objRange.CopyFromRecordset rsData
    rsData.Close
End If

Set rsData = Nothing
Set objRange = Nothing
Set objSht = Nothing
Set objWB = Nothing
Set objexcel = Nothing
End Function

Open in new window

0
 
LVL 18

Expert Comment

by:Simon
ID: 26076713
I should add...

For that code to work without editing, you must have a saved query called 'qryExportMemo' that includes the replace([myMemoField],chr(13) & chr(10),chr(13)) function.

In the code snippet below I've added a parameter for the queryname and a small subroutine to demonstrate the function.
Option Compare Database


sub TestExport
dim resultString as string
    resultString = ExportToExcel("qryExportMemo")
    msgbox resultstring
end sub


Function ExportToExcel(queryName as string)
Dim rsData As DAO.Recordset

Dim objWB As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim objRange As Excel.Range

Dim x As Integer

Set rsData = CurrentDb.QueryDefs(queryName).OpenRecordset
If Not rsData.EOF Then

    Set objexcel = CreateObject("excel.Application")

    objexcel.Visible = True
    Set objWB = objexcel.Workbooks.Add
    Set objSht = objWB.ActiveSheet
    
    'Write the field names
    For x = 0 To rsData.Fields.Count - 1
    objSht.Cells(1, x + 1).Value = rsData.Fields(x).Name
    Next x
    Set objRange = objSht.Range("A2:A2")
    Debug.Print objRange.Address
    objRange.CopyFromRecordset rsData
    rsData.Close
    ExportToExcel=rsdata.rowcount & " records exported"
else
msgbox "no data to export. Zero rows in query results"
ExportToExcel= "Zero records exported"
End If

Set rsData = Nothing
Set objRange = Nothing
Set objSht = Nothing
Set objWB = Nothing
Set objexcel = Nothing
End Function

Open in new window

0
 

Author Comment

by:shaun37
ID: 26077517
Simon-
  I notice the amount of effort you're putting into this and it's appreciated.  This code DOES open an Excel file, but it's truncating the field data after 255 characters.  "Analyze with Excel" is very promising but it has the same limitation, apparently.  Perhaps I'll post a sample of my Db tomorrow and you [all] can see what I'm trying to accomplish.

Shaun
0
 
LVL 18

Expert Comment

by:Simon
ID: 26078605

Workarounds (all worth trying, none guaranteed):
1.Export to Excel 97-2003 format (when using transferspreadsheet, try acSpreadsheetTypeExcel8 or acSpreadsheetTypeExcel9 as the spreadsheet type)

2.Change the export query to an append one and apppend the data-to-be-exported to an empty table first (but not a maketable query). Then export directly from the table to Excel 8 or 9

3.Use TransferText to export to a .csv or tab-separated file then import that to Excel

4.The MS suggested workaround for this, (which I have also seen posted elsewhere) is not great (split then concatenate the memo fields):
http://support.microsoft.com/kb/208801
0
 

Author Comment

by:shaun37
ID: 26098941
This question is NOT abandoned.  I have not received a suitable solution.  The suggestions so far would only aggravate the problem - which I'm getting more convinced, is just a[nother] MSOffice limitation I must live with.  I'm not ready to close this yet.

Shaun
0
 
LVL 16

Expert Comment

by:Calvin Brine
ID: 26099044
Shaun,
So you actually want to actually remove the crlf from the string?  
If you turn the word wrap on for the cell, it will use the CRLF.  You see the square because the word wrap is turned off.
If you want to remove once in the excel sheet, try this code.  You will need to update range to match where you data exists, but it's fairly easy to read code.  Let me know if you need help with it.  It replaces both the LF and CR characters.  If you data doesn't use those two, then I will need a sample to determine exactly what characters are used.
HTH
Cal

Dim wb As Workbook, ws As Worksheet
Dim cell As Range

Set wb = ActiveWorkbook
Set ws = ActiveSheet

For Each cell In ws.Range("A1", ws.Range("A" & ws.Rows.Count).End(xlUp))
    cell = Application.WorksheetFunction.Substitute(Application.WorksheetFunction.Substitute(cell, vbLf, " "), vbCr, "")
Next cell

Open in new window

0
 
LVL 18

Expert Comment

by:Simon
ID: 26099742
Hi. I thought we had got past the problem with the CRLFs and the followup issue is that memo fields are truncated

 "The suggestions so far would only aggravate the problem"

Please be more specific about this.

In my last post I offered several workarounds for the truncation problem. 1 and 2 are definitely worth trying. There is good info here (Allen Browne) on truncated memo fields. One of the reasons i suggested using a temp table was so that you could establish whether the memo field was being truncated in your export query due to Aggregation, Uniqueness,Format property, UNION query or Concatenated fields.
Were you able to try exporting to a higher version of Excel (97-2003)?
0
 

Author Comment

by:shaun37
ID: 26100058
Simon, your help is greatly appreciated.  Please know I only meant that thus far, the solutions are trading a funky character problem for a truncation problem.  I want to go back to the original issue.  You may be on the right track, but I'm attaching a sample Db file with three test records in it.  Notice that the data in the Access table is breaking into paragraphs as desired.  Once you export it to an Excel file (transferspreadsheet9), there appear to be two different kinds of cr\lf boxes in the Excel file.  clear boxes, and black boxes.  Because of the word wrap feature, I think I can can live with the clear boxes because they disappear with word wrap.  I need help removing the appropriate black boxes.

What I REALLY want to do is maintain the paragraphs\line breaks withOUT the black boxes.  I will award partial points to those who have suggested the word wrap feature in Excel was part of the issue.  I get it now, but I didn't before.  This is where that find\replace might make more sense to me.

"Help me help you - Jerry McGuire"  :)

Shaun
BooksDbSample.mdb
0
 
LVL 18

Accepted Solution

by:
Simon earned 1800 total points
ID: 26101183
Shaun, I apologise. The first code I gave you was wrong. It should have been
mem1:replace([myMemoField],chr(13) & chr(10),chr(10)) in the export query.
...But that then causes the memo field truncation.

and

The code I gave you only works with short (<255) memo fields. The CopyFromRecordset method fails on longer memos, as I have since found out.



The solution to do it all in Access (without VBA in either Access or Excel) is:

1.Use this function in your export query where you have memo fields:
    mem1:replace([myMemoField],chr(13) & chr(10),chr(13))
2. Make your export query a 'Make table' query (temporarily) and run it once to generate a staging table for the export data (call it tblMemoExport for example).
3. Go to the design of your staging tabe and make sure the relevant field types are still memo. If they're not, convert them to memo.
3. Change your export query to an append query that appends to staging table [tblMemoExport]

Set up your Access Macro
1. RunSQL "Delete * from tblMemoExport
2. open query [name of your export query]
3. Transferspreadsheet [tblMemoExport]
4. In Excel, change the cell format to wrap.

Why it works? The replace() function doesn't truncate the memo field because the query output is being written to an explicitly typed corresponding memo field in the staging table. The Transferspreadsheet doesn't truncate because it is copying from the explicitly typed table fields (to version 8 or higher spreadsheet).

I've tested this in Access/Excel 2002 which is the closest I have to your Office 2000. If you still get truncation, please let me know whether they're also truncated in the staging table.
0
 
LVL 18

Assisted Solution

by:Simon
Simon earned 1800 total points
ID: 26101200
Holy crap, I did the typo again:

Set up your Access Macro
1.Use this function in your export query where you have memo fields:
    mem1:replace([myMemoField],chr(13) & chr(10),chr(10))  <------------chr(10) NOT chr(13)
0
 

Author Comment

by:shaun37
ID: 26114290
Simon,
  This is getting close to a reality.  The idea of a "staging table" is looking do-able, because that is the only way to keep the data from getting truncated.  So...
One question on that:  From a good Db coding\efficiency point of view, would it be better to use the same table and just append to it and clear it as needed OR would it be better to recreate the table on the fly and delete it (over and over again) as needed?

Shaun
0
 
LVL 18

Expert Comment

by:Simon
ID: 26115427
To keep it simple and reliable, best to repeatedly empty and fill the same table, because you have explicitly defined datatypes and field/index options (as opposed to maketable query).

From .mdb/.accdb file growth perspective I'm not sure.

Your could build the table each time in VBA or a DDL query, but I'm not sure how creating and destroying tables in the database compares to zapping (delete * from) the table each time. If you ever wanted to query the export table from Excel using MSQuery, it would be better to have a persistent table rather than an object that is sometimes there and sometimes not, and persistent table is easier for de-bugging purposes too.

Choice is yours at the end of the day. I've used the zap and repopulate method since DB2/Foxpro days and it's never been an issue, but other experts here will know more of the theory on this than me, and I think that really is another question ;)
0
 

Author Closing Comment

by:shaun37
ID: 31667508
I have split the points a little because the wordwrap thing from CBrine was a very important part of the solution.

Simon got the rest of the points because of the patience and effort demonstrated.  I didn't have any interest in working with the data in Excel because once it leaves Access, I'm through with it.  His approach with the staging table, clearing it, populating it on the fly and then transferring it out to Excel is precisely how I resolved this issue.  Much obliged!  -Shaun
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question