Import text from Access results in squares at end of para

[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.

 

[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

 

[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.

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

 

[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