Pasting from Excel to Access

[Gersh76]

When I paste from Excel to Access, it doesn't include the paragraph breaks
from Excel. Instead it just pastes a small box at the end of a line and
starts the next line right after it. Any suggestions?

 

[Douglas J. Steele]

Access uses a combination of Carriage Return and Line Feed for a new line, I
believe Excel only uses Line Feed.

You can use the Replace function to change the vbLF to vbCrLf.

Replace([TextField], vbLf, vbCrLf)

Note that if you're running this through a query, you'll need to use

Replace([TextField], Chr(10), Chr(13) & Chr(10))

because queries don't know anything about intrinsic constants such as vbLf
and vbCrLf

 

[Gersh76]

Could you please elaborate on that a little. I tried the replace function by
going to edit>replace, but I don't see how it will work this way.

Access uses a combination of Carriage Return and Line Feed for a new line, I
believe Excel only uses Line Feed.

You can use the Replace function to change the vbLF to vbCrLf.

Replace([TextField], vbLf, vbCrLf)

Note that if you're running this through a query, you'll need to use

Replace([TextField], Chr(10), Chr(13) & Chr(10))

because queries don't know anything about intrinsic constants such as vbLf
and vbCrLf

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

When I paste from Excel to Access, it doesn't include the paragraph breaks
from Excel. Instead it just pastes a small box at the end of a line and
starts the next line right after it. Any suggestions?

 

[Douglas J. Steele]

I was actually thinking in terms of using VBA code: add a button to your
form that uses the Replace function on the content of the text box.

There are some potential issues, though. If you run that Replace statement
multiple times, you'll end up with real problems (You'll change existing
CR/LF combinations to CR/LF/LF, which will lead to more issues).

To be thorough, you probably should change any instances of CR/LF to
something that won't occur naturally in your text (I normally use something
like aabbccbbaa), then change the LF to CR/LF, then change the original
CR/LF back:

Private Sub MyButton_Click()

Me!MyTextBox = Replace(Me!MyTextBox, vbCrLf, "aabbccbbaa")
Me!MyTextBox = Replace(Me!MyTextBox, vbLf, vbCrLf)
Me!MyTextBox = Replace(Me!MyTextBox, "aabbccbbaa", vbCrLf)

End Sub

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Could you please elaborate on that a little. I tried the replace function
by
going to edit>replace, but I don't see how it will work this way.

Access uses a combination of Carriage Return and Line Feed for a new
line, I
believe Excel only uses Line Feed.

You can use the Replace function to change the vbLF to vbCrLf.

Replace([TextField], vbLf, vbCrLf)

Note that if you're running this through a query, you'll need to use

Replace([TextField], Chr(10), Chr(13) & Chr(10))

because queries don't know anything about intrinsic constants such as
vbLf
and vbCrLf

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

When I paste from Excel to Access, it doesn't include the paragraph
breaks
from Excel. Instead it just pastes a small box at the end of a line
and
starts the next line right after it. Any suggestions?