I am at my wits end here. I have code from Access that dumps data to an Excel
spreadsheet. Several values in one of my columns are pieces of text with
Chr(10) between them so that each piece of text falls on a separate line
within the cell.

The text gets dumped, but the separate lines do not show on the sheet in the
cell ( Chr(10) shows in the cell as a little box). However, they do show in
the formula bar as separate lines and if I click in the formula bar or
double-click the cell everything falls into place. I just can’t get this to
happen automatically from code.

Here is the code that sets the value that gets dumped to my sheet. I’m
cycling through a record set in Access, doing a replacement of ";" by Chr(10)
and then setting the cells formula value to the new value. I use a semicolon
as a placeholder to tell my code where I want new lines to start. The
separated parts of text are long strings and separating them to different
lines makes it much easier for my users to read.

NewValue = rs.Fields(iFieldNum - 1)
NewValue = Replace(NewValue, ";", Chr(10))
.Cells(I, iFieldNum).Formula = NewValue

I’ve tried many options to get this to work. Any suggestions would be
greatly appreciated.

Clint

NewValue = rs.Fields(iFieldNum - 1)
NewValue = Replace(NewValue, ";", Chr(10))
With .Cells(I, iFieldNum)
.Formula = NewValue
.WrapText = True
end with

--
Regards,
Tom Ogilvy

"cherman" <(E-Mail Removed)> wrote in message
news:975B039D-FF37-4E89-A3D8-(E-Mail Removed)...
>I am at my wits end here. I have code from Access that dumps data to an
>Excel
> spreadsheet. Several values in one of my columns are pieces of text with
> Chr(10) between them so that each piece of text falls on a separate line
> within the cell.
>
> The text gets dumped, but the separate lines do not show on the sheet in
> the
> cell ( Chr(10) shows in the cell as a little box). However, they do show
> in
> the formula bar as separate lines and if I click in the formula bar or
> double-click the cell everything falls into place. I just can't get this
> to
> happen automatically from code.
>
> Here is the code that sets the value that gets dumped to my sheet. I'm
> cycling through a record set in Access, doing a replacement of ";" by
> Chr(10)
> and then setting the cells formula value to the new value. I use a
> semicolon
> as a placeholder to tell my code where I want new lines to start. The
> separated parts of text are long strings and separating them to different
> lines makes it much easier for my users to read.
>
> NewValue = rs.Fields(iFieldNum - 1)
> NewValue = Replace(NewValue, ";", Chr(10))
> .Cells(I, iFieldNum).Formula = NewValue
>
> I've tried many options to get this to work. Any suggestions would be
> greatly appreciated.
>
> Clint
>

Thank you very much. That was exactly what I needed.

Clint


"Tom Ogilvy" wrote:

> NewValue = rs.Fields(iFieldNum - 1)
> NewValue = Replace(NewValue, ";", Chr(10))
> With .Cells(I, iFieldNum)
> .Formula = NewValue
> .WrapText = True
> end with
>
> --
> Regards,
> Tom Ogilvy
>
> "cherman" <(E-Mail Removed)> wrote in message
> news:975B039D-FF37-4E89-A3D8-(E-Mail Removed)...
> >I am at my wits end here. I have code from Access that dumps data to an
> >Excel
> > spreadsheet. Several values in one of my columns are pieces of text with
> > Chr(10) between them so that each piece of text falls on a separate line
> > within the cell.
> >
> > The text gets dumped, but the separate lines do not show on the sheet in
> > the
> > cell ( Chr(10) shows in the cell as a little box). However, they do show
> > in
> > the formula bar as separate lines and if I click in the formula bar or
> > double-click the cell everything falls into place. I just can't get this
> > to
> > happen automatically from code.
> >
> > Here is the code that sets the value that gets dumped to my sheet. I'm
> > cycling through a record set in Access, doing a replacement of ";" by
> > Chr(10)
> > and then setting the cells formula value to the new value. I use a
> > semicolon
> > as a placeholder to tell my code where I want new lines to start. The
> > separated parts of text are long strings and separating them to different
> > lines makes it much easier for my users to read.
> >
> > NewValue = rs.Fields(iFieldNum - 1)
> > NewValue = Replace(NewValue, ";", Chr(10))
> > .Cells(I, iFieldNum).Formula = NewValue
> >
> > I've tried many options to get this to work. Any suggestions would be
> > greatly appreciated.
> >
> > Clint
> >
>
>
>