-----Original Message-----
Umm. That shouldn't be happening. What version (and service pack) of
Office are you using, and about how many rows, columns and characters
are there in your data?
I suppose it's conceivable there's a problem with the Excel Selection
object if there's a really big selection, so you could try something
like this instead
Sub RemoveCRs()
Dim U As Excel.Range
Dim C As Excel.Range
Dim lngRow as Long
Set U = ActiveSheet.UsedRange
For lngRow = 1 to U.Rows.Count
For Each C In U.Rows(lngRow).Cells
If Left(C.Formula, 1) <> "=" Then
C.Formula = Replace(C.Formula, Chr(13), "")
End If
Next C
Next lngRow
Set U = Nothing
End Sub
Otherwise, you could try fixing it at the Access end, by using a
calculated field in a query to strip out the Chr(13)s. In Access 2002
and some Access 2000 installations you can use the Replace () function
directly in a query. Otherwise, you have to write a little custom VBA
function that calls the VBA Replace() function.
Hi John,
Thanks for taking the time to try and work this problem
out. Unfortuantly due to the size of the excel files i am
using, the code you gave me was crashing excel when i
tried to run it. I tried it with a single row and it
worked but once i tried to run it on several rows it just
hanged. Once again thanks for all your help, if i find a
solution i'll post it here.
Barry
-----Original Message-----
Hi Barry,
My last message was done entirely from memory but when
you said "none of
it worked" I tried for myself. Using File|Export instead
of
copy-and-paste doesn't help, and nor (to my surprise)
does exporting as
CSV. But the little VBA procedure does the job if you
correct the one
error it contains.
Here's the corrected version. The only difference is Chr
(13) in place of
Chr(10).
Sub RemoveCRs()
Dim C As Excel.Range
For Each C In Application.Selection.Cells
If Left(C.Formula, 1) <> "=" Then
C.Formula = Replace(C.Formula, Chr(13), "")
End If
Next
End Sub
On Tue, 9 Sep 2003 07:15:23 -0700, "barry"
Hi John,
Thanks for the help, unfortunatly none of it worked. I'd
figured it was to do with line feed as the fields that
are
giving me the squares have to enforce line feed.
I've also tired with some limited success to remove the
squares using:
Edit>Replace
Alt + 0010.
And then replacing all, however this would only work in
the first instance and would even then only remove some
of
the squares.
I have also tried numerous ways of exporting from access.
Thanks again and if u have any other ideas that would be
great.
Cheers,
Barry
-----Original Message-----
Hi Barry,
It sounds as if your Access data includes linebreaks
within the data.
Access uses Carriage Return + Line Feed for these: Chr
(13) & Chr(10),
but Excel just uses a linefeed Chr(10). The single
square
symbols are
thus the Chr(13)s that Excel doesn't need, and the ones
that appear when
you disable word wrapping are, I guess, the Chr(10)s
that
are useless in
that context.
Do you get better results if you export the data by
building a select
query that returns what you want, and then using
File|Export to export
it to Excel? Another possibility would be to export it
to
a CSV file
(comma-delimited, with " " marks qualifying the text
fields) and then
import that into Excel.
Otherwise, in recent versions of Excel you could use
the
VBA Replace()
function to remove the superfluous Chr(13)s, using
something like this
air code:
Sub RemoveCRs()
Dim C as Excel.Range
For Each C in Application.Selection.Cells
If Left(C.Formula, 1) <> "=" Then
C.Formula = Replace(C.Formula, Chr(10),"")
End If
Next
End Sub
On Mon, 8 Sep 2003 06:41:06 -0700, "Barry"
I'm exporting data (text,numbers,date) from an access
table to excel using copy/paste. When i paste the data
into excel, it includes in some of the text fields, a
square symbol, wheather the cell is empty or not. This
only occurs in some of the columns.
Also if i manually delete the square symbols and then
format the cells to remove word wrap, the squares re-
appear or double if i hadn't removed them.
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
.
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
.
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
.
