Import Simple Excel Tables Into Wikidot

Posted by gerdami on 06 Sep 2007 20:10, last edited by Helmut_pdorf on 12 Sep 2011 16:51

Tags: cell excel import row table vba

rating: +40+x

This HowTo describes how to import simple tables from Excel to Wikidot with a simple concatenation or with a VBA code.
In other words, it is sometimes easier to maintain a table outside Wikidot, let's say Excel, apply sorting, apply formatting and export the table to wikidot. Here is how:

The Spreadsheet

excel-A1-K6.jpg

A. Simple and stupid solution

1. Prepare Excel

I suppose that you have an Excel sheet of 12 columns and n rows starting from cell A1.
If it is not the case, copy and paste your data in a new sheet in cell A1.

Insert a column before column A. Data are moved right and table now starts from cell B1.

Copy and paste the following code in cell A1, otherwise the stupid code below won't work :(

="||~ "&B1&"||~ "&C1&"||~ "&D1&"||~ "&E1&"||~ "&F1&"||~ "&G1&"||~ "&H1&"||~ "&I1&"||~ "&J1&"||~ "&K1&"||~ "&L1&"||~ "&M1&"|| "
="|| "&B2&"|| "&C2&"|| "&D2&"|| "&E2&"|| "&F2&"|| "&G2&"|| "&H2&"|| "&I2&"|| "&J2&"|| "&K2&"|| "&L2&"|| "&M2&"|| "

excel-B1-L6.jpg
Copy down cell A2 to match the number of rows of your table.
Hint: double-click on the thick dot at the bottom right of cell A2.
excel-B2.jpg
After having adjusted the size of column A, which is absolutely not necessary… ;-) you would haveexcel-A1-A6.jpg

2. From Excel to Wikidot

Copy the range content "A1:An" to your wiki page. That's it.

Place Name No Glider T1 T2 T3 T4 T5 Total
1 MATTHEWS Dave 6 Moyes Litespeed S 135 468 500 468 289 1860
2 PHIPPS Graham 4 Wills Wing T2 287 402 337 500 256 1781
3 NEEDHAM Justin 28 Moyes Litespeed 150 340 347 320 500 1681
4 RIGG Gordon 1 Moyes Litespeed 129 466 295 326 438 1654
5 KAVANAGH Bruce 5 Wills Wing T2 210 457 281 378 218 1544

Some post-editing might be needed to suppress the extra columns to the right, i.d. supress " ||"
You might also merge some cells (e.g. the Name heading).
The excel formulas are to be extended if the number of columns is insufficient…
Please note that excel dates are numbers which have to converted to text by some excel formulas:

=TEXT(B2,"MM-YYYY") … if the date is in B2.
=TEXT(B3, "hh:mm:ss") … if time is in B3.

B. VBA solution

1. Copy the VBA code into Excel

From Excel, open the VBA interface by pressing
Alt-F11.
Insert
module
into the current workbook and paste the following code:


Press Alt-F4 to quit VBA.

2. Insert the function MultiCat into the spreadsheet

So as you type "= multicat" into a cell it should give dropdown list with the functions available to you. If Multicat is not available, or it the cell displays "#name" then you have not added the code to the module. Go back a step :)

With this VBA solution, the table can be anywhere in your spreadsheet
… and dates do not need to be converted into text !

Select a free cell (e.g. B9) in your spreadsheet and enter the function =multicat(range,"~")
vba-B2-L2.jpg
Select the cell below (B10) and enter function =multicat(range)
vba-B3-L3.jpg
Copy down cell B10 to match the number of rows, i.d. from B11 to B14 in our example.

3. From Excel to Wikidot

Copy the range content "B9:B14" to your wiki page. That's it.
Place Name No Glider T1 T2 T3 T4 T5 Total
1 MATTHEWS Dave 6 Moyes Litespeed S 135 468 500 468 289 1860
2 PHIPPS Graham 4 Wills Wing T2 287 402 337 500 256 1781
3 NEEDHAM Justin 28 Moyes Litespeed 150 340 347 320 500 1681
4 RIGG Gordon 1 Moyes Litespeed 129 466 295 326 438 1654
5 KAVANAGH Bruce 5 Wills Wing T2 210 457 281 378 218 1544

Versions

  • v 1.0 : first code
  • v 1.1 : line split (Alt-enter character) replaced by " _", the newline wikidot code
  • v 1.2.003 : basic alignment left, center, right and bold, italic and underline
  • v 1.3.002 : added font colors, strikethrough, superscript, subscript

What you get

Place Name First name No Glider T1 T2 T3 T4 T5 Total general
1 MATTHEWS Dave 6 MoyesLitespeedS 135 468 500 468 289 1860
2 PHIPPS Graham 4 WillsWingT2 287 402 337 500 256 1782
3 NEEDHAM Justin 28 MoyesLitespeed 150 341 347 320 500 1658
4 RIGG Gordon 1 MoyesLitespeed 129 466 295 326 438 1654
5 KAVANAGH Bruce 5 WillsWingT2 210 457 281 378 218 1544

XLA

If you trust me, this is the .xla: MultiCat v1.3.002.xla

C. VBA version 2

Version 2 generates [[table]], [[row]] and [[cell]] tags

BUT, unlike version 1, it is not a function anymore. it is just a sub procedure. Thats all.
You have to call this procedure with Alt-F8, when your active cell is within the table to convert to Wikidot's format. It will create a new sheet with the code to paste into a wikidot page. Yes, the code to be pasted is already selected ! .

Code

Versions

2.0.004 : first release of version 2. Now LineFeed (Alt-Enter -> " _Enter") WORKS, guys.
2.1.006 : now, also converts some conditional formatting (i.e. value condition, not formula condition), for Excel 2002/2003, buggy with Excel 2007, not tested with earlier versions.
2.1.007 : now allows empty cells… still working on conditional formatting improvements

What you get

Places Name First name No Glider T1 T2 T3 T4 T5 Total general
1 MATTHEWS
The King
Kong
Dave 6 Moyes
Litespeed
S
135 468 500 468 289 1860
2 PHIPPS Graham 4 WillsWingT2 287 402 337 500 256 1782
3 NEEDHAM Justin 28 Moyes
Litespeed
150 341 347 320 500 1658
4 RIGG Gordon 1 Moyes
Litespeed
129 466 295 326 438 1654
5 KAVANAGH Bruce 5 WillsWingT2 210 457 281 378 218 1544

XLA

If you trust me, here are the .xla:
MultiCat v2.0.004.xla
MultiCat v2.1.006.xla
MultiCat v2.1.007.xla
MultiCat v2.2.001.xla

Credits

Backlinks

Related

Author

gerdamigerdami. Please visit his/her userPage.

rating: +40+x

Related articles

Comments

Add a New Comment
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-Share Alike 2.5 License.