Pasting an Excel table into a memo field

[Pat]

I have somewhat of an obscure question. I periodically need to paste small Excel tables into an Access memo field. When you simply copy and paste the table all formatting is lost (of course) and all cells in each row run together. I've set up a routine in Excel that concatenates the cells in each row and adds spaces based on the number of characters in the cell. I do this in an attempt to have all the columns line up once pasted into the memo field. My simple Excel routine looks at each cell in a column, finds the cell with the longest word, adds 2 spaces to the longest word and for the other cells in the column I add enough spaces so the word length + spaces = length of the longest word + the two spaces I added. Theoretically this would have all the columns lined up.

In the following example the longest word in column 1 is James at 5. I add 2 spaces after James. Since "Name" = 4 characters I add 3 spaces after "Name" (5-4+2). And so on.

Name Company Notes
Sam Dura Not active
James Ford Working the issues

However, because Excel is using true type fonts the width of each letter is slightly different and when I use this routine the columns are close, but not perfectly aligned. The table comes out something like this (this isn't EXACTLY how it looks, just presenting a rough representation)

Name Company Notes


Sam Dura Not active
James Ford Working the issues

Does anyone know how I can format an Excel table so when I paste it into a memo field all columns line up? Maybe I can import it into a temp Access table and then use that data to populate the memo field. Please don't point me to using a link to Excel or an OLE, I need to have the data in the memo field. Thanks in advance for any suggestions.

[June7]

Are you displaying this data on a form or report? Try setting the textbox to a non-proportional font. I like Consolas.

[Pat]

Both, my I use the form more than the report. Your suggestion, although not perfect, made the data look much better when I changed the font!

[June7]

Issue solved?

Storing the data in separate fields and records in a related table and displaying in separate textboxes in a subform would be the only alternative I can imagine

[Pat]

Thanks - this has been a big help. Because your answer got me much closer than I was before, how do I indicate this issue is solved?

[June7]

Thread Tools dropdown above first post.