Import text from Access results in squares at end of para

L

Laura

I have an Access database that I query from Excel. The
fields that are defined as TeXt or Memo fields in Access
have squares at the end of each paragraph in Excel. I can
manually remove them from Excel, but the next time I
refresh the data from Access, then they are back of
course. Is there some way to prevent this or to remove the
squares via an Excel macro. I tried to record a macro that
would remove the squares, but it would not record my
keystrokes.
 
J

Joe Bloggs

Laura

You can create an query in access that does not include
the memo fields and import that.

Alternatively, I seem to remember that it can also be done
from Excel. It uses the Access wizard.

It is years since I tried it and I can't remember But I'm
sure you just follow the wizard.

regards
Peter Atherton
 
S

Stephen Bullen

Hi Laura,
I have an Access database that I query from Excel. The
fields that are defined as TeXt or Memo fields in Access
have squares at the end of each paragraph in Excel. I can
manually remove them from Excel, but the next time I
refresh the data from Access, then they are back of
course. Is there some way to prevent this or to remove the
squares via an Excel macro. I tried to record a macro that
would remove the squares, but it would not record my
keystrokes.
Click to expand...

I'd imagine those are carriage-return characters, char(13), so you
could strip those out within the Access query, instead of:

SELECT MyField,...

Use

SELECT Replace(MyField,Char(13),"") As MyField,...

Alternatively, you might be able to use find/replace after doing the
query:

Cells.Replace What:=Chr$(13), Replacement:="", LookAt:=xlPart


Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk
 
L

Laura

Hi Stephen,
Thanks. I tried the Access suggestion, but there is
no "Replace" function, so I'm going to try the Excel macro
suggestion next.

Laura
 
Top