An Excel macro for creating a table in a post

Getting the most out of Eileen's Lounge!
User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

An Excel macro for creating a table in a post

Post by HansV »

Here is an Excel macro similar to the one for Word in A Word macro for creating a table in a post.
TableMacro4Excel.txt
You can copy the macro Table2Lounge into a code module in Excel. If you copy it into a module in your personal macro workbook Personal.xls(b), it will be available in all workbooks.

To use it, select the cells that you want to copy, then run the macro (Alt+F8 will display the Macros dialog in all versions of Excel).
The macro will ask whether you want to include row and column headers, and whether you want to preserve vertical alignment (if you answer No, all rows will have Top vertical alignment).
As a result, the tags to create the table in a post will be placed on the Windows clipboard, ready to be pasted into your post.

Example:

Here is a screenshot of some cells in Excel:
x64.png
Running the macro with those cells selected and answering Yes to both prompts will place the following "code" on the clipboard:

[table=1][tr=middle][td=center][/td][td=center][b]A[/b][/td][td=center][b]B[/b][/td][td=center][b]C[/b][/td][/tr][tr=bottom][td=center][b]1[/b][/td][td=left]Mary
had[/td][td=left]a[/td][td=right]little[/td][/tr][tr=middle][td=center][b]2[/b][/td][td=right]lamb[/td][td=right]and
its[/td][td=center]fur[/td][/tr][/table]

When posted, this results in
ABC
1Mary
had
alittle
2lamband
its
fur
Note: to run the macro, you must have a reference to the Microsoft Forms 2.0 Object Library in the Visual Basic Editor. The easiest way to create the reference is to insert a userform (Insert | Userform). The userform can be kept or discarded, that doesn't matter.

Warning: the macro will ignore merged cells. The table tags in the Lounge don't support merged cells.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans