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

: cell excel import row table vba

rating: +41

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:

'*****************************************
'MultiCat version 1.3.001 of 22.10.2007
'Purpose: Concatenate all cells in a range for table upload to wikidot
'Inputs: rRng - range to be concatenated
' sHeadDelim - optional heading delimiter
' to insert between cell Texts
'Usage: in call A1: =Multicat(B1:L1,"~") for the wikidot heading
' in call A2: =Multicat(B2:L2) for future wikidot line split
' in call A2: =Multicat(B2:L2," "," _") for future wikidot line split
' in call A3: =Multicat(B2:L2," ","") to suppress LF character "Alt-Enter"
'Returns: concatenated string
'Now supports: bold, italic, underline, strikethrough, subscript, superscript,
'cell font color, left, center, right alignment
'
'Adapted by gerdami on 05/09/2007 for Excel tables upload to Wikidot
'From http://www.mcgimpsey.com/excel/udfs/multicat.html. McGimpsey and Associates.
'Except where noted, all code on this site may be distributed under the Gnu GPL.
'Acknowledgement is appreciated.
'Colors: http://www.mvps.org/dmcritchie/excel/colors.htm#hexconv
'*****************************************
Public Function MultiCat( _
 ByRef rRng As Excel.Range _
 , Optional ByVal sHeadDelim As String = "" _
 , Optional ByVal sChar10Rep As String = " _" _
 ) As String
 Dim rCell As Range
 Dim sDelim, sBlank, sTemp, sChar10, sChar34, sNull, sAlign, _
 sBold As String
 sDelim = "||"
 sBlank = " "
 ' sChar10 = Chr(10) 'vbLf = vb Line Feed constant equivalent to Chr(10)
 sChar34 = Chr(34)
 sNull = ""
 sChar10Rep = sChar10Rep ' & vbLf
 For Each rCell In rRng
 ' sTemp = sTemp & sDelim & sHeadDelim & sBlank & rCell.Text & sBlank
 If (sHeadDelim = " " Or sHeadDelim = "") Then
 sAlign = ""
 Select Case rCell.HorizontalAlignment
 Case Is = xlGeneral
 sAlign = ""
 Case Is = xlCenter
 sAlign = "="
 Case Is = xlLeft
 sAlign = "<"
 Case Is = xlRight
 sAlign = ">"
 End Select
 Else
 sAlign = sHeadDelim
 End If
 If rCell.Font.Bold = True Then
 sBold = "**"
 Else
 sBold = ""
 End If
 If rCell.Font.Italic = True Then
 sItalic = "//"
 Else
 sItalic = ""
 End If
 If rCell.Font.Strikethrough = True Then
 sStrikethrough = "--"
 Else
 sStrikethrough = ""
 End If
 If rCell.Font.Superscript = True Then
 sSuperscript = "^^"
 Else
 sSuperscript = ""
 End If
 If rCell.Font.Subscript = True Then
 sSubscript = ",,"
 Else
 sSubscript = ""
 End If
 ' Underline is not simply True or False
 If rCell.Font.Underline = xlUnderlineStyleNone Then
 sUnderline = ""
 Else
 sUnderline = "__"
 End If
 ' font colors
 ' http://www.mvps.org/dmcritchie/excel/colors.htm#hexconv
 xColor = Right("000000" & Hex(rCell.Font.Color), 6)
 xColor = "#" & Right(xColor, 2) & Mid(xColor, 3, 2) & Left(xColor, 2)
 If xColor <> "#000000" Then
 sColorBegin = "#" & xColor & "|"
 sColorEnd = "##"
 Else
 sColorBegin = ""
 sColorEnd = ""
 End If
 xColor = Right("000000" & Hex(rCell.Interior.Color), 6)
 xColor = "#" & Right(xColor, 2) & Mid(xColor, 3, 2) & Left(xColor, 2)
 If xColor <> "#FFFFFF" Then
 sSpanBegin = " [[span style=""background-color: " & xColor & """]] "
 sSpanEnd = " [[/span]] "
 Else
 sSpanBegin = ""
 sSpanEnd = ""
 End If
 ' added Trim(string) to supress spaces before and after the cell text
 sTemp = sTemp & sDelim & sAlign & sBlank & sSpanBegin & sColorBegin & sUnderline & sBold & sItalic & sStrikethrough & sSuperscript & sSubscript & Trim(rCell.Text) & sSubscript & sSuperscript & sStrikethrough & sItalic & sBold & sUnderline & sColorEnd & sSpanEnd & sBlank
 Next rCell
 sTemp = sTemp & sDelim
 sTemp = Application.WorksheetFunction.Substitute(sTemp, vbLf, sChar10Rep)
 MultiCat = sTemp
 End Function

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

Option Explicit
Public Sub ExcelToWikidot()
'
'ExcelToWikidot
'Version 2.1.007 du 04/11/2007
'Author gerdami
'Credits:
' McGimpsey and Associates, for the idea: http://www.mcgimpsey.com/excel/udfs/multicat.html
' David McRitchie, for the colors: http://www.mvps.org/dmcritchie/excel/colors.htm#hexconv
' John Walkenbach, for the split function: http://j-walk.com/ss/excel/tips/tip93.htm
' Chip Pearson, for the Conditional Formatting Colors: http://www.cpearson.com/excel/CFColors.htm
 Dim aRange, rCell As Range
 Dim nRows, nCols, i, j, x As Long
 Dim sAlign, sBackgroundColor, sCell, sWdLf, xColor As String
 Dim xString As Variant
 sWdLf = " _"
 'sWdLf = " _" & vbLf
 Set aRange = Selection.CurrentRegion
 nRows = aRange.Rows.Count
 nCols = aRange.Columns.Count
 'Sheets.Add.Name = "Wikidot" ... test whether Wikidot sheet exists and delete TODO !
 Sheets.Add
 ActiveCell.Value = "[[table style=""width: 100%; border-collapse: collapse; border:2px solid""]]"
 'ActiveCell.Value = "[[table]]"
 ActiveCell.Offset(1, 0).Activate
 For i = 1 To nRows
 ActiveCell.Value = "[[row]]"
 ActiveCell.Offset(1, 0).Activate
 For j = 1 To nCols
 Set rCell = aRange.Cells(i, j)
 sCell = Trim(aRange.Cells(i, j).Text) 'remove spaces before and after text
 sCell = Application.WorksheetFunction.Substitute(sCell, vbLf, sWdLf) 'replace char(010) by " _"
 'ActiveCell.Value = ActiveCell.Value & "[[cell]]"
 '[[cell style is build piece by piece
 ActiveCell.Value = ActiveCell.Value & "[[cell style=""border:1px solid silver; "
 Select Case rCell.HorizontalAlignment
 Case Is = xlGeneral
 sAlign = ""
 Case Is = xlCenter
 sAlign = "text-align: center; "
 Case Is = xlLeft
 sAlign = "text-align: left; "
 Case Is = xlRight
 sAlign = "text-align: right; "
 End Select
 ActiveCell.Value = ActiveCell.Value & sAlign
 ' background-color
 ' xColor = Right("000000" & Hex(rCell.Interior.Color), 6) 'David Mc
 ' xColor = Right("000000" & Hex(ColorOfCF(rCell, 0)), 6) 'Chip Pearson
 xColor = Right("000000" & Hex(ConditionalColor(rCell, "interior")), 6) '
 xColor = "#" & Right(xColor, 2) & Mid(xColor, 3, 2) & Left(xColor, 2)
 If xColor <> "#FFFFFF" Then
 sBackgroundColor = " background-color: " & xColor & ";"
 Else
 sBackgroundColor = ""
 End If
 ActiveCell.Value = ActiveCell.Value & sBackgroundColor
 ActiveCell.Value = ActiveCell.Value & """]]"
 If Len(sCell) > 0 Then 'Wikidot does not like empty cells: v2.1.007
 'cell emphasis is applied piece by piece
 If rCell.Font.Bold = True Then sCell = "**" & sCell & "**"
 If rCell.Font.Italic = True Then sCell = "//" & sCell & "//"
 If rCell.Font.Strikethrough = True Then sCell = "--" & sCell & "--"
 If rCell.Font.Superscript = True Then sCell = "^^" & sCell & "^^"
 If rCell.Font.Subscript = True Then sCell = ",," & sCell & ",,"
 If rCell.Font.Underline = xlUnderlineStyleNone Then Else sCell = "__" & sCell & "__"
 ' font colors
 ' http://www.mvps.org/dmcritchie/excel/colors.htm#hexconv
 ' xColor = Right("000000" & Hex(rCell.Font.Color), 6)
 ' http://www.cpearson.com/excel/CFColors.htm
 ' xColor = Right("000000" & Hex(ColorOfCF(rCell, 1)), 6)
 xColor = Right("000000" & Hex(ConditionalColor(rCell, "font")), 6)
 xColor = "#" & Right(xColor, 2) & Mid(xColor, 3, 2) & Left(xColor, 2)
 If xColor <> "#000000" Then sCell = "#" & xColor & "|" & sCell & "##"
 ActiveCell.Value = ActiveCell.Value & sCell
 Else ' Len(sCell) = zero then nothing
 End If
 ActiveCell.Value = ActiveCell.Value & "[[/cell]]"
 ' With the above I have all cells in one row, with the split function,
 ' I will split the cells containing the Wikidot linefeed " _"
 ' http://j-walk.com/ss/excel/tips/tip93.htm
 xString = Split(ActiveCell.Text, sWdLf)
 For x = 0 To UBound(xString)
 ActiveCell.Value = xString(x)
 If x <> UBound(xString) Then ActiveCell.Value = ActiveCell.Value & sWdLf
 ActiveCell.Offset(1, 0).Activate
 ' I have currently a side effect: one cell per line
 ' but I will keep it for readibility
 Next x
 Next j
 'ActiveCell.Offset(1, 0).Activate
 ActiveCell.Value = "[[/row]]"
 ActiveCell.Offset(1, 0).Activate
 Next i
 ActiveCell.Value = "[[/table]]"
 Selection.CurrentRegion.Select
End Sub
'''''''''''''''''''''''''''''''''
Function ConditionalColor(rg As Range, FormatType As String) As Long
'http://www.vbaexpress.com/kb/getarticle.php?kb_id=190
'Returns the color (either font or interior) of the first cell in range rg. If no _
 conditional format conditions apply, then returns the regular color of the cell. _
 FormatType is either "Font" or "Interior"
Dim cel As Range
Dim tmp As Variant
Dim boo As Boolean
Dim frmla As String, frmlaR1C1 As String, frmlaA1 As String
Dim i As Long
' Application.Volatile 'This statement required if Conditional Formatting for rg is determined by the _
 value of other cells
Set cel = rg.Cells(1, 1)
Select Case Left(LCase(FormatType), 1)
Case "f" 'Font color
 ConditionalColor = cel.Font.Color
Case Else 'Interior or highlight color
 ConditionalColor = cel.Interior.Color
End Select
If cel.FormatConditions.Count > 0 Then
 'On Error Resume Next
 With cel.FormatConditions
 For i = 1 To .Count 'Loop through the three possible format conditions for each cell
 frmla = .Item(i).Formula1
 If Left(frmla, 1) = "=" Then 'If "Formula Is", then evaluate if it is True
 'Conditional Formatting is interpreted relative to the active cell. _
 This cause the wrong results if the formula isn't restated relative to the cell containing the _
 Conditional Formatting--hence the workaround using ConvertFormula twice in a row. _
 If the function were not called using a worksheet formula, you could just activate the cell instead.
 frmlaR1C1 = Application.ConvertFormula(frmla, xlA1, xlR1C1, , ActiveCell)
 frmlaA1 = Application.ConvertFormula(frmlaR1C1, xlR1C1, xlA1, xlAbsolute, cel)
 boo = Application.Evaluate(frmlaA1)
 Else 'If "Value Is", then identify the type of comparison operator and build comparison formula
 Select Case .Item(i).Operator
 Case xlEqual ' = x
 frmla = cel & "=" & .Item(i).Formula1
 Case xlNotEqual ' <> x
 frmla = cel & "<>" & .Item(i).Formula1
 Case xlBetween 'x <= cel <= y
 frmla = "AND(" & .Item(i).Formula1 & "<=" & cel & "," & cel & "<=" & .Item(i).Formula2 & ")"
 Case xlNotBetween 'x > cel or cel > y
 frmla = "OR(" & .Item(i).Formula1 & ">" & cel & "," & cel & ">" & .Item(i).Formula2 & ")"
 Case xlLess ' < x
 frmla = cel & "<" & .Item(i).Formula1
 Case xlLessEqual ' <= x
 frmla = cel & "<=" & .Item(i).Formula1
 Case xlGreater ' > x
 frmla = cel & ">" & .Item(i).Formula1
 Case xlGreaterEqual ' >= x
 frmla = cel & ">=" & .Item(i).Formula1
 End Select
 boo = Application.Evaluate(frmla) 'Evaluate the "Value Is" comparison formula
 End If
 If boo Then 'If this Format Condition is satisfied
 On Error Resume Next
 Select Case Left(LCase(FormatType), 1)
 Case "f" 'Font color
 tmp = .Item(i).Font.Color
 Case Else 'Interior or highlight color
 tmp = .Item(i).Interior.Color
 End Select
 If Err = 0 Then ConditionalColor = tmp
 Err.Clear
 On Error GoTo 0
 Exit For 'Since Format Condition is satisfied, exit the inner loop
 End If
 Next i
 End With
End If
End Function

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 
[[table style="width: 100%; border-collapse: collapse; border:2px solid"]]
[[row]]
[[cell style="border:1px solid silver; background-color: #FFFF00;"]]##FF0000|**Places**##[[/cell]]
[[cell style="border:1px solid silver; background-color: #FFFF00;"]]##FF0000|**Name**##[[/cell]]
[[cell style="border:1px solid silver; background-color: #FFFF00;"]]##FF0000|**First name**##[[/cell]]
[[cell style="border:1px solid silver; background-color: #FFFF00;"]]##FF0000|**No**##[[/cell]]
[[cell style="border:1px solid silver; text-align: center; background-color: #FFFF00;"]]##FF0000|**Glider**##[[/cell]]
[[cell style="border:1px solid silver; background-color: #FFFF00;"]]##FF0000|**T1**##[[/cell]]
[[cell style="border:1px solid silver; background-color: #FFFF00;"]]##FF0000|**T2**##[[/cell]]
[[cell style="border:1px solid silver; background-color: #FFFF00;"]]##FF0000|**T3**##[[/cell]]
[[cell style="border:1px solid silver; background-color: #FFFF00;"]]##FF0000|**T4**##[[/cell]]
[[cell style="border:1px solid silver; background-color: #FFFF00;"]]##FF0000|**T5**##[[/cell]]
[[cell style="border:1px solid silver; background-color: #FFFF00;"]]##FF0000|**Total general**##[[/cell]]
[[/row]]
[[row]]
[[cell style="border:1px solid silver; text-align: left; background-color: #FF0000;"]]**1**[[/cell]]
[[cell style="border:1px solid silver; background-color: #CCFFFF;"]]##0000FF|MATTHEWS _
The King _
Kong##[[/cell]]
[[cell style="border:1px solid silver; text-align: center; background-color: #CCFFFF;"]]##0000FF|//Dave//##[[/cell]]
[[cell style="border:1px solid silver; background-color: #CCFFFF;"]]##0000FF|6##[[/cell]]
[[cell style="border:1px solid silver; background-color: #CCFFFF;"]]##0000FF|Moyes _
Litespeed _
S##[[/cell]]
[[cell style="border:1px solid silver; text-align: center; background-color: #CCFFFF;"]]135[[/cell]]
[[cell style="border:1px solid silver; text-align: center; background-color: #808080;"]]##00FFFF|468##[[/cell]]
[[cell style="border:1px solid silver; text-align: center; background-color: #808080;"]]##00FFFF|500##[[/cell]]
[[cell style="border:1px solid silver; text-align: center; background-color: #808080;"]]##00FFFF|468##[[/cell]]
[[cell style="border:1px solid silver; text-align: center; background-color: #C0C0C0;"]]##800080|289##[[/cell]]
[[cell style="border:1px solid silver; text-align: right; background-color: #CCFFFF;"]]##333399|**1860**##[[/cell]]
[[/row]]
[[row]]
[[cell style="border:1px solid silver; text-align: left; background-color: #FFFF00;"]]##0000FF|**2**##[[/cell]]
[[cell style="border:1px solid silver; background-color: #CCFFFF;"]]PHIPPS[[/cell]]
[[cell style="border:1px solid silver; text-align: center; background-color: #CCFFFF;"]]//Graham//[[/cell]]
[[cell style="border:1px solid silver; background-color: #CCFFFF;"]]4[[/cell]]
[[cell style="border:1px solid silver; background-color: #CCFFFF;"]]WillsWingT2[[/cell]]
[[cell style="border:1px solid silver; text-align: center; background-color: #C0C0C0;"]]##800080|287##[[/cell]]
[[cell style="border:1px solid silver; text-align: center; background-color: #969696;"]]##00FF00|402##[[/cell]]
[[cell style="border:1px solid silver; text-align: center; background-color: #969696;"]]##00FF00|337##[[/cell]]
[[cell style="border:1px solid silver; text-align: center; background-color: #808080;"]]##00FFFF|500##[[/cell]]
[[cell style="border:1px solid silver; text-align: center; background-color: #C0C0C0;"]]##800080|256##[[/cell]]
[[cell style="border:1px solid silver; text-align: right; background-color: #CCFFFF;"]]##333399|**1782**##[[/cell]]
[[/row]]
[[row]]
[[cell style="border:1px solid silver; text-align: left; background-color: #CC99FF;"]]##800080|**3**##[[/cell]]
[[cell style="border:1px solid silver; background-color: #CCFFFF;"]]**NEEDHAM**[[/cell]]
[[cell style="border:1px solid silver; text-align: center; background-color: #CCFFFF;"]]__//**Justin**//__[[/cell]]
[[cell style="border:1px solid silver; background-color: #CCFFFF;"]]**28**[[/cell]]
[[cell style="border:1px solid silver; background-color: #CCFFFF;"]]**Moyes _
Litespeed**[[/cell]]
[[cell style="border:1px solid silver; text-align: center; background-color: #C0C0C0;"]]##800080|150##[[/cell]]
[[cell style="border:1px solid silver; text-align: center; background-color: #969696;"]]##00FF00|341##[[/cell]]
[[cell style="border:1px solid silver; text-align: center; background-color: #969696;"]]##00FF00|347##[[/cell]]
[[cell style="border:1px solid silver; text-align: center; background-color: #969696;"]]##00FF00|320##[[/cell]]
[[cell style="border:1px solid silver; text-align: center; background-color: #808080;"]]##00FFFF|500##[[/cell]]
[[cell style="border:1px solid silver; text-align: left; background-color: #CCFFFF;"]]##333399|**1658**##[[/cell]]
[[/row]]
[[row]]
[[cell style="border:1px solid silver; text-align: left; background-color: #CCFFFF;"]]**4**[[/cell]]
[[cell style="border:1px solid silver; background-color: #CCFFFF;"]]RIGG[[/cell]]
[[cell style="border:1px solid silver; text-align: center; background-color: #CCFFFF;"]]//Gordon//[[/cell]]
[[cell style="border:1px solid silver; background-color: #CCFFFF;"]]1[[/cell]]
[[cell style="border:1px solid silver; background-color: #CCFFFF;"]]Moyes _
Litespeed[[/cell]]
[[cell style="border:1px solid silver; text-align: center; background-color: #CCFFFF;"]]129[[/cell]]
[[cell style="border:1px solid silver; text-align: center; background-color: #808080;"]]##00FFFF|466##[[/cell]]
[[cell style="border:1px solid silver; text-align: center; background-color: #C0C0C0;"]]##800080|295##[[/cell]]
[[cell style="border:1px solid silver; text-align: center; background-color: #969696;"]]##00FF00|326##[[/cell]]
[[cell style="border:1px solid silver; text-align: center; background-color: #969696;"]]##00FF00|438##[[/cell]]
[[cell style="border:1px solid silver; text-align: center; background-color: #CCFFFF;"]]##333399|**1654**##[[/cell]]
[[/row]]
[[row]]
[[cell style="border:1px solid silver; text-align: left; background-color: #CCFFFF;"]]**5**[[/cell]]
[[cell style="border:1px solid silver; background-color: #CCFFFF;"]]KAVANAGH[[/cell]]
[[cell style="border:1px solid silver; text-align: center; background-color: #CCFFFF;"]]//Bruce//[[/cell]]
[[cell style="border:1px solid silver; background-color: #CCFFFF;"]]5[[/cell]]
[[cell style="border:1px solid silver; background-color: #CCFFFF;"]]##FF0000|WillsWingT2##[[/cell]]
[[cell style="border:1px solid silver; text-align: center; background-color: #C0C0C0;"]]##800080|210##[[/cell]]
[[cell style="border:1px solid silver; text-align: center; background-color: #808080;"]]##00FFFF|457##[[/cell]]
[[cell style="border:1px solid silver; text-align: center; background-color: #C0C0C0;"]]##800080|281##[[/cell]]
[[cell style="border:1px solid silver; text-align: center; background-color: #969696;"]]##00FF00|378##[[/cell]]
[[cell style="border:1px solid silver; text-align: center; background-color: #C0C0C0;"]]##800080|218##[[/cell]]
[[cell style="border:1px solid silver; text-align: center; background-color: #CCFFFF;"]]##333399|**1544**##[[/cell]]
[[/row]]
[[/table]]

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

gerdami gerdami . Please visit his/her userPage.

rating: +41

Related articles

Comments

Problems with the excel function which gives ... #NAME?
gerdami gerdami 07 Sep 2007 11:20

1. I tested this on Excel 2002/2003, not 2007.
2. PERSONAL.XLS : if you store the function in PERSONAL.XLS, which you have forced to be opened at startup , you have to specify the full name of the function "=personal.xls!MultiCat(B13:E13)"
3. You might also create an Excel Add-In
* create a new workbook multicat.xls (and put the code in the VBA project as described above)
* save the file multicat.xls
* save the file again as multicat.xla (choose type Msft Office Excel Add-In)
* with Tools, Add-Ins…, Browse and locate your multicat.xla
* at next Excel start-up the function will be available

by gerdami gerdami , 07 Sep 2007 11:20
Problems with the excel function which gives ... #NAME?
Phil Chett Phil Chett 07 Sep 2007 11:34

problem proved to sticking the code in the wrong place.
Text in how:to slightly changed to make it more obvious.

by Phil Chett Phil Chett , 07 Sep 2007 11:34
Automatize Excel import of Tables to create new wiki site for each....
EVotingCC EVotingCC 19 Sep 2007 09:43

Hi Phil!
Thank you once more for being so helpful.

I still have a question though: Is it possible to somehow automatize the import of my 200+ Tables into WikiDot but not as you describe it here in one large table.
What I want to do is to create a new page for each table. The whole thing should be included in a site structure where you can pick the different countries and then the various tables for the selected country as you can see here.

Looking forward to your reply!
Daniel

by EVotingCC EVotingCC , 19 Sep 2007 09:43
Re: Automatize Excel import of Tables to create new wiki site for each....
Helmut_pdorf Helmut_pdorf 19 Sep 2007 14:34

Hi !
First I think the title is wrong!
You do not want an extra site for every table…

What I do not understand:
is it not easier to upload the excel files itself , WITH .xls as file extensions , for documentation purposes?
Even if some visitor has no MS-Excel in use, they can always open the files with (open source) Open Office.

But, sorry, I believe, you need to create the pages, page after page because of parenting (to the regions a.s.o.) and tagging too and to insert the table over the source- edit window.
I think it is easier and faster, to create for every table a second sheet with in the first columns copied the content from the first sheet in the manner Gerdami and Phill has described.
Than it is more a copy and paste drama.

Best regards and good luck
Helmut

Service is my success. My webtips:www.blender.org (Open source), Wikidot-Handbook.

Sie können fragen und mitwirken in der deutschsprachigen » User-Gemeinschaft für WikidotNutzer oder
im deutschen » Wikidot Handbuch ?

Last edited on 19 Sep 2007 14:43 by Helmut_pdorf
by Helmut_pdorf Helmut_pdorf , 19 Sep 2007 14:34
Latest vba version
Phil Chett Phil Chett 04 Nov 2007 16:52

By heck, lad. You do be a clever bugger.

Theres a couple of quirks in that i have as yet to identify, but generally speaking, just as the multicat version does, this is an excellent way to save hours trying to code tables.
Just kick up your excell table the way you want it. Run the code.
Job done

Comp ID Dates Comp Detail Venue
A 3-10 Jan Forbes Flatlands (& Oz Nationals) Forbes, NSW
B 13 - 19 Jan Bogong Cup Mt Beauty, Victoria
C TBA Bassano Monte Grappa, Italy
D TBA Flytec Championship Groveland, Florida - but dates & venue t.b.c.
E 3 - 5 May British Open Series (Class 1 & 5) Dates & Venue t.b.c.
F 23 - 27 May British Open Series Dates & Venue t.b.c.
G 7 - 21 June European Championships Greifenburg, Austria
H 24 June - 5 July Chabre Open & 2009 Pre-Worlds Laragne-Monteglin, France
I TBA British Open Series Reserve Meet Dates & Venue t.b.c.
J TBA Bleriot Cup UK - dates & venue t.b.c.
K TBA UK Class 1 (& 5?) Nationals Dates & Venue t.b.c.
L 22 - 26 July Open Series Final Dates & Venue t.b.c.

Identified problems.

1: The first row of your table must not have blank cells in it.

Pilot ## FF0000|A## B C D R F G H I J K L

If i but a dot in the blank cell then i get

Pilot . A B C D R F G H I J K L

of course, i can always hide the dot by changing the text color

Pilot . A B C D R F G H I J K L

Merging the first two cells gives

Pilot ## FF0000|A## B C D R F G H I J K L

As long as the first row is right, then all following rows with blank spaces will work ok.

2 : Conditional formatting

Problem to be identified as yet, but it seems that as soon as it looks at a cell with conditional formating, the sode stops in its tracks with a runtime error (13) type mismatch. But then it does say

2.1.006 : now, also converts some conditional formatting

Last edited on 04 Nov 2007 20:08 by Phil Chett
by Phil Chett Phil Chett , 04 Nov 2007 16:52
Re: Latest vba version
gerdami gerdami 05 Nov 2007 21:34

Problems

Problem 1

It is fixed in version 007

Problem 2

  • From several Excel forum posts, it seems that there are some new features related to conditional formatting in Excel 12 (2007) and also some incompatibilities with VBA code from earlier versions (2002/2003).
  • I used a code from Chip Pearson for Color Formatting … which led to a run-time error when no font.color or interior.color was set for a met condition. [added on 8.11.2007].

Phil Chess Board

a b c d e f g h
8 XX YY XX YY XX YY XX YY 8
7 YY XX YY XX YY XX YY XX 7
6 XX YY XX YY XX YY XX YY 6
5 YY XX YY XX YY XX YY XX 5
4 XX YY XX YY XX YY XX YY 4
3 YY XX YY XX YY XX YY XX 3
2 XX YY XX YY XX YY XX YY 2
1 YY XX YY XX YY XX YY XX 1
a b c d e f g h

Done with Excel, some conditional formatting … and ExcelToWikidot!

Last edited on 08 Nov 2007 12:23 by gerdami
by gerdami gerdami , 05 Nov 2007 21:34
Re: Latest vba version
Phil Chett Phil Chett 05 Nov 2007 23:38

(wisper very softly, so not as to disturb … cos we don't want to upset this guy gerdarmi, who has saved me hours and hours of potential going madness by writing this code….. )

where the hell is version 0.00000000000007 ??

by Phil Chett Phil Chett , 05 Nov 2007 23:38
Re: Latest vba version
gerdami gerdami 06 Nov 2007 06:57

Please spell gerdami correctly! ;-)
007 is still an unpublished Fleming's.

And do not forget to click on Rating +.
If rating stucks to zero no interest from the Community, …

Last edited on 06 Nov 2007 07:13 by gerdami
by gerdami gerdami , 06 Nov 2007 06:57
Re: Latest vba version
Phil Chett Phil Chett 06 Nov 2007 07:38

page ratings have been disabled. (and i dont know why, or how to fix that)

by Phil Chett Phil Chett , 06 Nov 2007 07:38
Re: Latest vba version
Helmut_pdorf Helmut_pdorf 06 Nov 2007 14:50

Hi Phil!
I rated it now on my IE 7 - I cannot detect any error with the ratings module? -Helmut

Service is my success. My webtips:www.blender.org (Open source), Wikidot-Handbook.

Sie können fragen und mitwirken in der deutschsprachigen » User-Gemeinschaft für WikidotNutzer oder
im deutschen » Wikidot Handbuch ?

by Helmut_pdorf Helmut_pdorf , 06 Nov 2007 14:50
Re: Latest vba version
Phil Chett Phil Chett 06 Nov 2007 16:45

i fink sumwon has fixed it, cos i have bin trying to rate this wonderous piece of work for ages, now i can :-)

by Phil Chett Phil Chett , 06 Nov 2007 16:45
Re: Latest vba version
gerdami gerdami 08 Nov 2007 12:19

sumwon was gerdami using sitemanager, page_ratings.
somedays aie learn moore slang[uage] than wikidot features.

Footnotes
. demi, roger
by gerdami gerdami , 08 Nov 2007 12:19
ROX
Gabrys Gabrys 07 Jul 2008 17:21

Hey, this one really rocks.

Good job! Thanks!

Piotr Gabryjeluk
visit my blog

by Gabrys Gabrys , 07 Jul 2008 17:21
Re: ROX
gerdami gerdami 12 Jul 2008 08:34

Thanks Gabrys, but this i still a unfinished work…

by gerdami gerdami , 12 Jul 2008 08:34
Re: ROX
erdraug erdraug 15 Oct 2008 11:08

Finished or not, THANK YOU!

Up until now i've imported the excel into word, mass replaced tabs with "space || space" and "space || break ||" and i don't even remember what else, what a tiring process and how silly i feel now that i just have to copy some code in to cell A1 !!

/me blushes

by erdraug erdraug , 15 Oct 2008 11:08
Re: Excel to wikidot
gerdami gerdami 08 May 2009 18:09

Can anyone rate this up since the forum-for-dummies is again before my howto ?

by gerdami gerdami , 08 May 2009 18:09
Re: Excel to wikidot
RobElliott RobElliott 08 May 2009 19:06

the forum-for-dummies is again before my howto

Shock horror. I've corrected that dreadful situation. In return I would like….

Rob

Rob Elliott - Strathpeffer, Scotland - Wikidot first line support & community admin team.

by RobElliott RobElliott , 08 May 2009 19:06
Re: Excel to wikidot
(account deleted) 08 May 2009 20:33

This is why we need a multiple point scale. I'd give you as many "stars" as available!

-Ed

by (account deleted), 08 May 2009 20:33
Excel to wikidot *****
rch rch 15 May 2009 10:00

I am very happy that this is available, I have some minor problems that I can solve manually,
but it deserves the "as many stars" isn't normally 5+

Thanks

Rocio

by rch rch , 15 May 2009 10:00
Yubei Obe Yubei Obe 14 Oct 2014 18:23

rch - I'm also really happy that this is available! ;)
Best Regards!

Last edited on 30 Aug 2015 13:25 by Yubei Obe
by Yubei Obe Yubei Obe , 14 Oct 2014 18:23
gerdami gerdami 10 Mar 2015 12:16

You're welcome.
;-)

by gerdami gerdami , 10 Mar 2015 12:16
rasberrykit rasberrykit 18 Jul 2020 04:57

Thanks bro. This really helped

by rasberrykit rasberrykit , 18 Jul 2020 04:57
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-Share Alike 2.5 License.
Click here to edit contents of this page.
Click here to toggle editing of individual sections of the page (if possible). Watch headings for an "edit" link when available.
Append content without editing the whole page source.
Check out how this page has evolved in the past.
If you want to discuss contents of this page - this is the easiest way to do it.
View and manage file attachments for this page.
A few useful tools to manage this Site.
Change the name (also URL address, possibly the category) of the page.
View wiki source for this page without editing.
View/set parent page (used for creating breadcrumbs and structured layout).
Notify administrators if there is objectionable content in this page.
Something does not work as expected? Find out what you can do.
General Wikidot.com documentation and help section.
Wikidot.com Terms of Service - what you can, what you should not etc.
Wikidot.com Privacy Policy.

AltStyle によって変換されたページ (->オリジナル) /