Simulate Alt+Enter in a table

[Guest]

I have a proc that updates a table with users who have missed training. Some
users miss more than one course, so my code concatenates the course strings
so I only have 1 record in my table per user, and it does so in a way that is
like using Alt+Enter to separate text while in a table field.

Works fine until I dump the data to Excel via another proc. The course info
comes out in one line instead of each course on a separate line (not a
separate cell, but a separate line within the same cell). These courses look
right in my table, with each course on a separate line within the same field.

I’ve tried different combinations of vbCrLf, vbCr, etc., but it still ends
up in Excel without the formatting. These characters are replaced with little
boxes. Of course, if I manually go into the cell and do the Alt+Enter, I can
separate the info, but I want this done automatically.

I don’t know if this is something I can do in Access when I send data to my
table or if it is something I have to do in my Excel code when dumping to a
sheet. I would prefer to take care of in Access.

Any suggestions?

 

[Granny Spitz via AccessMonster.com]

so my code concatenates the course strings
so I only have 1 record in my table per user

Change this so you have as many records as you have missed courses. If Sam
missed 3 courses, he should have 3 records, each one listing the course he
missed. You can put all the ingredients in the stew pot and stir, but if you
treat your data like a stew and toss all the data in one pot, you'll have a
hard time separating and arranging the data when you need to.

 

[Guest]

Hi and thanks for your suggestion.

However, this process is a temp process that is only used when running my
output to Excel so the real data is safe. Also, the users are used to seeing
the data with 1 line per user and do not want to see multiple lines.

The only part of the process I can't get right is the formatting.

 

[fredg]

I have a proc that updates a table with users who have missed training. Some
users miss more than one course, so my code concatenates the course strings
so I only have 1 record in my table per user, and it does so in a way that is
like using Alt+Enter to separate text while in a table field.

Works fine until I dump the data to Excel via another proc. The course info
comes out in one line instead of each course on a separate line (not a
separate cell, but a separate line within the same cell). These courses look
right in my table, with each course on a separate line within the same field.

I¢ve tried different combinations of vbCrLf, vbCr, etc., but it still ends
up in Excel without the formatting. These characters are replaced with little
boxes. Of course, if I manually go into the cell and do the Alt+Enter, I can
separate the info, but I want this done automatically.

I don¢t know if this is something I can do in Access when I send data to my
table or if it is something I have to do in my Excel code when dumping to a
sheet. I would prefer to take care of in Access.

Any suggestions?

Having more than one item of data in your field is poor database
design. You wouldn't have this problem with good design.

Usually, the problem is from the other direction.
Access uses the chr(13) & chr(10) combination as a new line
indicgtor. Excel uses just chr(10).

You can
1) Create an Update query to replace chr(13) & chr(10) with just
chr(10). Then export the changed data to Excel.

Update MyTable Set MyTable.[FieldName] = Replace([FieldName],chr13) &
chr(10),chr(10));

The new data however, will not appear correctly in Access (as you'll
have those little square boxes representing chr(10)).

or...
2) Write a macro in Excel to search for and replace Chr(13) & chr(10)
with just chr(10) after you have imported it.
That should be all you need do. Best to ask for help in an Excel
newsgroup for how to write it using Excel's VBA.

 

[Granny Spitz via AccessMonster.com]

However, this process is a temp process

Because it's just a temp process you might think you can take shortcuts to
save time. Not normalizing the data in this temp process is probably going
to take more time, not less. What you see in the tables in Access isn't
necessarily what you'll see after the data is exported to another format, so
your neat arrangement in Access falls to pieces in Excel. I believe you can
automate Excel and format the cells the way you want after the export, but
you should ask for the code to do that in the Excel newsgroups. I just had
another thought. Can you open Excel and link to this Access table? Maybe
that will show the cells the way you want (though I kinda doubt it).

 

[Marshall Barton]

I have a proc that updates a table with users who have missed training. Some
users miss more than one course, so my code concatenates the course strings
so I only have 1 record in my table per user, and it does so in a way that is
like using Alt+Enter to separate text while in a table field.

Works fine until I dump the data to Excel via another proc. The course info
comes out in one line instead of each course on a separate line (not a
separate cell, but a separate line within the same cell). These courses look
right in my table, with each course on a separate line within the same field.

I’ve tried different combinations of vbCrLf, vbCr, etc., but it still ends
up in Excel without the formatting. These characters are replaced with little
boxes. Of course, if I manually go into the cell and do the Alt+Enter, I can
separate the info, but I want this done automatically.

Excel uses Chr(10) (i.e. vbLF) as the new line sequence.

 

[Guest]

Thanks again! I'll check into your suggestion. I think we are getting our
wires crossed. The data that is dumped to the table is pulled by a query,
formatted in code, added to the table, dumped into Excel AND then deleted
from the temp table.

No shortcut needed or normalization for this. The data is in the table for a
half second, the data is formatted in Excel the way my customer wants and
everyone is happy.

Clint