Import Simple Excel Tables Into Wikidot

W e l c o m e ! » Help » List of community-edited How-To's » Page Content » 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

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

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&"|| "

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.

After having adjusted the size of column A, which is absolutely not necessary… ;-) you would have

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:

+ show me the code

- hide the 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,"~")

Select the cell below (B10) and enter function =multicat(range)

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	³⁴⁷	320	500	1658
4	RIGG	Gordon	1	MoyesLitespeed	129	466	₂₉₅	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

+ show me the code

- hide the 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

+ show me the generated code

- hide the code

[[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

Example taken from http://bhgc.wikidot.com/bos2007 by Phil Chett
McGimpsey and Associates: http://www.mcgimpsey.com/excel/udfs/multicat.html
Ozgrid.com: Tutorial on Excel User Defined Functions
ExcelTip.com: Writing Your First VBA Function in Excel
David McRitchie: Excel colors to html
John Walkenbach: http://j-walk.com/ss/excel/tips/tip93.htm
Chip Pearson: http://www.cpearson.com/excel/CFColors.htm

Backlinks

Author

gerdami. Please visit his/her userPage.

Show Comments

Comments

Hide All Comments Unfold All Fold All

Fold

Problems with the excel function which gives ... #NAME?

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

gerdami - Visit Handbook en Français - Rate this howto:import-simple-excel-tables-into-wikidot up!

Reply Options

Unfold Problems with the excel function which gives ... #NAME? by

gerdami, 07 Sep 2007 11:20

Fold

Problems with the excel function which gives ... #NAME?

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.

Reply Options

Unfold Problems with the excel function which gives ... #NAME? by

Phil Chett, 07 Sep 2007 11:34

Fold

Automatize Excel import of Tables to create new wiki site for each....

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

Reply Options

Unfold Automatize Excel import of Tables to create new wiki site for each.... by

EVotingCC, 19 Sep 2007 09:43

Fold

Re: Automatize Excel import of Tables to create new wiki site for each....

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 Show more

Reply Options

Unfold Re: Automatize Excel import of Tables to create new wiki site for each.... by

Helmut_pdorf, 19 Sep 2007 14:34

Fold

Latest vba version

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##

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

Pilot

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

Pilot

Merging the first two cells gives

Pilot

FF0000|A##

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 Show more

Reply Options

Unfold Latest vba version by

Phil Chett, 04 Nov 2007 16:52

Fold

Re: Latest vba version

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!

gerdami - Visit Handbook en Français - Rate this howto:import-simple-excel-tables-into-wikidot up!

Last edited on 08 Nov 2007 12:23 by gerdami Show more

Reply Options

Unfold Re: Latest vba version by

gerdami, 05 Nov 2007 21:34

Fold

Re: Latest vba version

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 ??}

Options

Unfold Re: Latest vba version by

Phil Chett, 05 Nov 2007 23:38

Fold

Re: Latest vba version

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,} …

gerdami - Visit Handbook en Français - Rate this howto:import-simple-excel-tables-into-wikidot up!

Last edited on 06 Nov 2007 07:13 by gerdami Show more

Options

Unfold Re: Latest vba version by

gerdami, 06 Nov 2007 06:57

Fold

Re: Latest vba version

Phil Chett 06 Nov 2007 07:38

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

Options

Unfold Re: Latest vba version by

Phil Chett, 06 Nov 2007 07:38

Fold

Re: Latest vba version

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 ?

Options

Unfold Re: Latest vba version by

Helmut_pdorf, 06 Nov 2007 14:50

Fold

Re: Latest vba version

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 :-)

Options

Unfold Re: Latest vba version by

Phil Chett, 06 Nov 2007 16:45

Fold

Re: Latest vba version

gerdami 08 Nov 2007 12:19

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

gerdami - Visit Handbook en Français - Rate this howto:import-simple-excel-tables-into-wikidot up!

Reply Options

Unfold Re: Latest vba version by

gerdami, 08 Nov 2007 12:19

Fold

ROX

Gabrys 07 Jul 2008 17:21

Hey, this one really rocks.

Good job! Thanks!

Piotr Gabryjeluk
visit my blog

Reply Options

Unfold ROX by

Gabrys, 07 Jul 2008 17:21

Fold

Re: ROX

gerdami 12 Jul 2008 08:34

Thanks Gabrys, but this i still a unfinished work…

gerdami - Visit Handbook en Français - Rate this howto:import-simple-excel-tables-into-wikidot up!

Reply Options

Unfold Re: ROX by

gerdami, 12 Jul 2008 08:34

Fold

Re: ROX

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

Reply Options

Unfold Re: ROX by

erdraug, 15 Oct 2008 11:08

Fold

Re: Excel to wikidot

gerdami 08 May 2009 18:09

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

gerdami - Visit Handbook en Français - Rate this howto:import-simple-excel-tables-into-wikidot up!

Reply Options

Unfold Re: Excel to wikidot by

gerdami, 08 May 2009 18:09

Fold

Re: Excel to wikidot

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.

Reply Options

Unfold Re: Excel to wikidot by

RobElliott, 08 May 2009 19:06

Fold

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

Community Admin

Reply Options

Unfold Re: Excel to wikidot by

(account deleted), 08 May 2009 20:33

Fold

Excel to wikidot *****

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

Reply Options

Unfold Excel to wikidot ***** by

rch, 15 May 2009 10:00

Fold

Yubei Obe 14 Oct 2014 18:23

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

my talking tom cheats

Last edited on 30 Aug 2015 13:25 by Yubei Obe Show more

Reply Options

Unfold by

Yubei Obe, 14 Oct 2014 18:23

Fold

gerdami 10 Mar 2015 12:16

You're welcome.
;-)

gerdami - Visit Handbook en Français - Rate this howto:import-simple-excel-tables-into-wikidot up!

Reply Options

Unfold by

gerdami, 10 Mar 2015 12:16

Fold

rasberrykit 18 Jul 2020 04:57

Thanks bro. This really helped

Phần mềm giải pháp

Reply Options

Unfold by

rasberrykit, 18 Jul 2020 04:57

Add a New Comment

_content _l5 cell excel import row table vba

	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

	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

hints / tips / help / discussion

Support Forum (Recent)

Time in Toruń, Poland

The Spreadsheet

A. Simple and stupid solution

1. Prepare Excel

2. From Excel to Wikidot

B. VBA solution

1. Copy the VBA code into Excel

2. Insert the function MultiCat into the spreadsheet

3. From Excel to Wikidot

Versions

What you get

XLA

C. VBA version 2

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

Code

Versions

What you get

XLA

Credits

Backlinks

Related

Author

Related articles

Comments

Identified problems.

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

2 : Conditional formatting

Problems

Problem 1

Problem 2

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