Excel macro for exporting a selected cell range to Dokuwiki

File with Makro

Herewith a further version of this nice macro directly within the file. The tool contains parameters on first worksheet to
allow the user some customization. Paste your table into another worksheet, select the cells as usual and run the makro.

Also check this Google Sheets Macro

Features/Configuration

Configuration Parameter	Options	Effect in DokuWiki
text formatting	yes/no	defines if Bold, Italic, Undeline will be considered
horizontal Alignment	yes/no	use same horizontal cell alignment (left, center, right)
Detect and use text color	yes/no	text will be colored as in Excel
Detect and use cell color	yes/no	cell color will be used as text background color (do not mixed up with cell color at the wiki)
Use first Line as Table Header	yes/no	colors the background first row light blue (or whatever your template defined as header background color)
Use first Column as Table Header	yes/no	colors the background first column light blue (or whatever your template defined as header background color)
Hyperlinks only as address	yes/no	display the hyperlink only by its real link address (no: use cell text as caption and put the link behind)
turn comments into footnotes	yes/no	cell comments will be inserted as footnotes
defined table width	yes/no	table and columns will be defined in their width (Note:_tablewidth plugin needed at DW)

Download

A German excel version is throwing errors caused by “ThisWorkbook.Sel2Wiki” due to a string is handed over and no function is used.
But my Excel understands following:

myButton.OnAction = “DieseArbeitsmappe.Sel2Wiki”

Therfore I changed the AddIn Button providing now two buttons to overcome the language dependency.
If you have a German Excel then use the (German) button or the other if your Excel is an English version.

For downloading the file please visit following Forum post:
http://forum.dokuwiki.org/post/24915;nocount
or from our git: https://github.com/Taggic/Excel2DokuWiki

Getting it working

For the clipboard code to work, you need at least Excel 2000. If you get an error message for DataObject, try adding an empty form to your project in the VBA editor. Alternatively, add a reference to the MS Forms Library: Tools → References → Make sure Microsoft Forms 2.0 Object Library is checked.

If you can't find the reference, go to the VB Editor, Insert Form and check again.

If MSForms is not listed, and you can't find it in the References list, go to add a reference and then hit the “Browse…” button to look for the file yourself. MSForms is in a file called “FM20.dll” (at least for version 2.0; other versions would be numbered accordingly) which is probably in your Windows\System32 folder. You should be able to add it that way (even without adding a UserForm). If you can't find the file, it may have somehow gotten erased and you may need to reinstall Excel. (from http://www.excelforum.com/excel-programming-vba-macros/353942-how-do-i-reference-dataobject.html)

If it's still not there, try the instructions here: http://msdn.microsoft.com/en-us/library/aa159923%28office.11%29.aspx

Complex Tables

A modified version of the simple one above. As above, for the clipboard code to work, you need at least Excel 2000 and add a reference to the MS Forms Library: Tools → References → Make sure Microsoft Forms 2.0 Object Library is checked.

excel2dokuwiki_complex.vbs

Option Explicit
 
Sub SelectionToWiki()
    ' Macro to convert/export the selected cells into a DokuWiki table
    ' Tested with DokuWiki 2007-06-26
    ' Limitations:
    '   won't format individual characters within a cell
 
    Dim currentSelection As Range, thisCell As Range
    Dim wikiText As String, thisCellText As String
    Dim rows As Integer, cols As Integer, thisRow As Integer, thisCol As Integer
    Dim inMerge As Boolean
    Dim oData As DataObject
 
    Set oData = New DataObject
    Set currentSelection = ActiveWindow.RangeSelection
    rows = currentSelection.rows.Count
    cols = currentSelection.Columns.Count
    wikiText = ""
 
    For thisRow = 1 To rows
        If thisRow = 1 Then
            'heading row
            wikiText = wikiText & "^"
        Else
            wikiText = wikiText & "|"
        End If
 
        inMerge = False
        For thisCol = 1 To cols
            Set thisCell = currentSelection.Cells(thisRow, thisCol)
 
            'value
            thisCellText = thisCell.Value
            'if it's an empty cell then make it a space (to avoid merging cells)
            If Not inMerge And thisCellText = "" Then thisCellText = " "
 
            If Not inMerge Then
                'don't apply formatting and alignment for cells within a merged area (only the first cell gets that)
                'formatting
                With thisCell.Font
                    If .Bold = True Then thisCellText = "**" & thisCellText & "**"
                    If .Italic = True Then thisCellText = "//" & thisCellText & "//"
                    If .Underline <> xlUnderlineStyleNone Then thisCellText = "__" & thisCellText & "__"
                End With
 
                'alignment
                Select Case thisCell.HorizontalAlignment
                    Case xlLeft
                        thisCellText = thisCellText & "  "
                    Case xlRight
                        thisCellText = "  " & thisCellText
                    Case xlCenter
                        thisCellText = "  " & thisCellText & "  "
                End Select
            End If
 
            'check for merged cells
            If thisCell.MergeCells Then
                inMerge = True
            End If
 
            'add this cell to wiki output string
            If thisRow = 1 Then
                'heading row
                wikiText = wikiText & thisCellText & "^"
            Else
                wikiText = wikiText & thisCellText & "|"
            End If
 
        Next thisCol
        'end this row
        wikiText = wikiText + Chr(13)
    Next thisRow
 
    'now copy to clipboard
    oData.SetText (wikiText)
    oData.PutInClipboard
    MsgBox "Selected cells were copied into clipboard", vbInformation
 
End Sub

Management of Background Color and embedded Newlines

The following modification copes with spreadsheets where some cells have interior colors set, or contain embedded linebreaks within a cell.

excel2dokuwiki_color.vbs

Public Sub SelectionToWiki()
    ' Macro to convert/export the selected cells into a DokuWiki table
    ' Tested with DokuWiki 2008-05-05
    ' Limitations:
    '   won't format individual characters within a cell
 
    Dim currentSelection As Range, thisCell As Range
    Dim wikiText As String, thisCellText As String
    Dim rows As Integer, cols As Integer, thisRow As Integer, thisCol As Integer
    Dim inMerge As Boolean
    Dim oData As DataObject
 
    Set oData = New DataObject
    Set currentSelection = ActiveWindow.RangeSelection
    rows = currentSelection.rows.Count
    cols = currentSelection.Columns.Count
    wikiText = ""
 
    For thisRow = 1 To rows
        inMerge = False
        For thisCol = 1 To cols
            Set thisCell = currentSelection.Cells(thisRow, thisCol)
 
            'value
            thisCellText = thisCell.Value
            'if it's an empty cell then make it a space (to avoid merging cells)
            If Not inMerge And thisCellText = "" Then thisCellText = " "
 
            If Not inMerge Then
                'don't apply formatting and alignment for cells within a merged area (only the first cell gets that)
                'formatting
                With thisCell.Font
                    If .Bold = True Then thisCellText = "**" & thisCellText & "**"
                    If .Italic = True Then thisCellText = "//" & thisCellText & "//"
                    If .Underline <> xlUnderlineStyleNone Then thisCellText = "__" & thisCellText & "__"
                End With
 
                'alignment
                Select Case thisCell.HorizontalAlignment
                    Case xlLeft
                        thisCellText = thisCellText & "  "
                    Case xlRight
                        thisCellText = "  " & thisCellText
                    Case xlCenter
                        thisCellText = "  " & thisCellText & "  "
                End Select
            End If
 
            'check for merged cells
            If thisCell.MergeCells Then
                inMerge = True
            End If
 
            'replace embedded newlines with backslashes
            thisCellText = Replace(thisCellText, Chr(10), "\\ ")
 
            'add this cell to wiki output string
            If thisRow = 1 Or thisCell.Interior.ColorIndex <> xlNone Then
                'heading row or colored cell
                wikiText = wikiText & "^" & thisCellText
            Else
                wikiText = wikiText & "|" & thisCellText
            End If
 
        Next thisCol
        'end this row
        wikiText = wikiText & "|" & Chr(13)
    Next thisRow
 
    'now copy to clipboard
    oData.SetText (wikiText)
    oData.PutInClipboard
    MsgBox "Selected cells were copied into clipboard", vbInformation
 
End Sub

Add this macro to Excel menu

If you want add this macro to Excel Tools menu. Create a new XLA file with the sub SelectionToWiki in VBA module and add this macro to ThisWorkBook module.

excel2dokuwiki_this_workbook.vbs

Private Sub Workbook_Open()
 
    Const strExceltoWiki As String = "Copy Selection to Wiki"
 
    Dim myButton As CommandBarButton
    Dim Wb As Workbook
    Dim I As Integer
 
    'Check in current Tools menu if item already exist
    For I = 1 To Application.CommandBars("Tools").Controls.Count
        If Application.CommandBars("Tools").Controls(I).Caption = strExceltoWiki Then
            Exit Sub
        End If
    Next I
 
    Set myButton = Application.CommandBars("Tools").Controls.Add(Type:=msoControlButton, temporary:=True)
    myButton.Caption = strExceltoWiki
    myButton.Style = msoButtonCaption
    myButton.BeginGroup = True
    myButton.OnAction = "SelectionToWiki"
 
End Sub

How to retrieve Hyperlinks

The following lines may be included into the upper mentioned code to get the hyperlink and its namespace out of the cells to (insert after “If Not inMerge Then”).

 
 If Not inMerge Then
    'Hyperlinks & namespace
     If thisCell.Hyperlinks.Count > 0 Then
        thisCellText = "  [[" & thisCell.Hyperlinks(1).Address & "|" & thisCellText & "]]  "
     End If
     ...

or if you want the hyperlink only then use

    'Hyperlinks only
    If thisCell.Hyperlinks.Count > 0 Then
       thisCellText = "  [[" & thisCell.Hyperlinks(1).Address & "]]  "
    End If
 ...

Please take care to replace DokuWiki syntax characters like visualized by following example to be inserted before “If Not inMerge Then”:

 'replace Dokuwiki syntax signs
 thisCellText = Replace(thisCellText, "*", "x")
 
 If Not inMerge Then
 ...

Complex Tables & Escaping & Hyperlinks & Background colors

The following code puts all the above macros into one single macro.

excel2dokuwiki_color.vbs

Public Sub SelectionToWiki()
    ' Macro to convert/export the selected cells into a DokuWiki table
    ' Tested with DokuWiki 2008-05-05
    ' Limitations:
    '   won't format individual characters within a cell
 
    Dim currentSelection As Range, thisCell As Range
    Dim wikiText As String, thisCellText As String
    Dim rows As Integer, cols As Integer, thisRow As Integer, thisCol As Integer
    Dim inMerge As Boolean
    Dim oData As DataObject
 
    Set oData = New DataObject
    Set currentSelection = ActiveWindow.RangeSelection
    rows = currentSelection.rows.Count
    cols = currentSelection.Columns.Count
    wikiText = ""
 
    For thisRow = 1 To rows
        inMerge = False
        For thisCol = 1 To cols
            Set thisCell = currentSelection.Cells(thisRow, thisCol)
 
            'value
            thisCellText = thisCell.Value
            'if it's an empty cell then make it a space (to avoid merging cells)
            If Not inMerge And thisCellText = "" Then thisCellText = " "
 
            If Not inMerge Then
                'replace Dokuwiki syntax signs
                thisCellText = Replace(thisCellText, "*", "x")
				thisCellText = Replace(thisCellText, "|", "/")
				thisCellText = Replace(thisCellText, "^", "/")
 
                'Convert the FIRST hyperlink of the cell & add the cell content as the hyperlink's name
                If thisCell.Hyperlinks.Count > 0 Then
                    thisCellText = "  [[" & thisCell.Hyperlinks(1).Address & "|" & thisCellText & "]]  "
                End If
 
                'don't apply formatting and alignment for cells within a merged area (only the first cell gets that)
                'formatting
                With thisCell.Font
                    If .Bold = True Then thisCellText = "**" & thisCellText & "**"
                    If .Italic = True Then thisCellText = "//" & thisCellText & "//"
                    If .Underline <> xlUnderlineStyleNone Then thisCellText = "__" & thisCellText & "__"
                End With
 
                'alignment
                Select Case thisCell.HorizontalAlignment
                    Case xlLeft
                        thisCellText = thisCellText & "  "
                    Case xlRight
                        thisCellText = "  " & thisCellText
                    Case xlCenter
                        thisCellText = "  " & thisCellText & "  "
                End Select
            End If
 
            'check for merged cells
            If thisCell.MergeCells Then
                inMerge = True
            End If
 
            'replace embedded newlines with backslashes
            thisCellText = Replace(thisCellText, Chr(10), "\\ ")
 
            'add this cell to wiki output string
            If thisRow = 1 Or thisCell.Interior.ColorIndex <> xlNone Then
                'heading row or colored cell
                wikiText = wikiText & "^" & thisCellText
            Else
                wikiText = wikiText & "|" & thisCellText
            End If
 
        Next thisCol
 
        'end this row
        wikiText = wikiText & "|" & Chr(13)
    Next thisRow
 
 
    'now copy to clipboard
    oData.SetText (wikiText)
    oData.PutInClipboard
    MsgBox "Selected cells were copied into clipboard", vbInformation
 
End Sub

Office 2007 Complex Tables & Escaping & Hyperlinks & Background colors with Toolbar Addin

The following code puts all the above macros into one single macro. and includes the addin code that has been modified to work with office 2007. Save it as type Excel Addin (*.xlam) and put it in %appdata%\Microsoft\AddIns Close and reopen excel, go to the addins section of the menu, click on the button to manage excel addins and enable this addin. Close Excel. Open a table or new document and you are good to go.

excel2dokuwiki_color and menu .vbs

Private Sub Workbook_Open()
 
    Const strExceltoWiki As String = "Copy Selection to Wiki"
 
    Dim myButton As CommandBarButton
    Dim Wb As Workbook
    Dim I As Integer
 
    'Check in current Tools menu if item already exist
    For I = 1 To Application.CommandBars("Tools").Controls.Count
        If Application.CommandBars("Tools").Controls(I).Caption = strExceltoWiki Then
            Exit Sub
        End If
    Next I
 
    Set myButton = Application.CommandBars("Tools").Controls.Add(Type:=msoControlButton, temporary:=True)
    myButton.Caption = strExceltoWiki
    myButton.Style = msoButtonCaption
    myButton.BeginGroup = True
    myButton.OnAction = "ThisWorkbook.SelectionToWiki" '"DieseArbeitsmappe.SelectionToWiki" for german users.
 
End Sub
Public Sub SelectionToWiki()
    ' Macro to convert/export the selected cells into a DokuWiki table
    ' Tested with DokuWiki 2010-11-07
    ' Limitations:
    '   won't format individual characters within a cell
 
    Dim currentSelection As Range, thisCell As Range
    Dim wikiText As String, thisCellText As String
    Dim rows As Integer, cols As Integer, thisRow As Integer, thisCol As Integer
    Dim inMerge As Boolean
    Dim oData As Object
 
    Set oData = CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") 'Instantiate DataObject without FM 2.0.
    Set currentSelection = ActiveWindow.RangeSelection
    rows = currentSelection.rows.Count
    cols = currentSelection.Columns.Count
    wikiText = ""
 
    For thisRow = 1 To rows
        inMerge = False
        For thisCol = 1 To cols
            Set thisCell = currentSelection.Cells(thisRow, thisCol)
 
            'value
            thisCellText = thisCell.Value
            'if it's an empty cell then make it a space (to avoid merging cells)
            If Not inMerge And thisCellText = "" Then thisCellText = " "
 
            If Not inMerge Then
                'replace Dokuwiki syntax signs
                thisCellText = Replace(thisCellText, "*", "%%*%%")
                thisCellText = Replace(thisCellText, "|", "%%|%%")
                thisCellText = Replace(thisCellText, "^", "%%^%%")
 
                'Convert the FIRST hyperlink of the cell & add the cell content as the hyperlink's name
                If thisCell.Hyperlinks.Count > 0 Then
                    thisCellText = "  [[" & thisCell.Hyperlinks(1).Address & "|" & thisCellText & "]]  "
                End If
 
                'don't apply formatting and alignment for cells within a merged area (only the first cell gets that)
                'formatting
                With thisCell.Font
                    If .Bold = True Then thisCellText = "**" & thisCellText & "**"
                    If .Italic = True Then thisCellText = "//" & thisCellText & "//"
                    If .Underline <> xlUnderlineStyleNone Then thisCellText = "__" & thisCellText & "__"
                End With
 
                'alignment
                Select Case thisCell.HorizontalAlignment
                    Case xlLeft
                        thisCellText = thisCellText & "  "
                    Case xlRight
                        thisCellText = "  " & thisCellText
                    Case xlCenter
                        thisCellText = "  " & thisCellText & "  "
                End Select
            End If
 
            'check for merged cells
            If thisCell.MergeCells Then
                inMerge = True
            End If
 
            'replace embedded newlines with backslashes
            thisCellText = Replace(thisCellText, Chr(10), "\\ ")
 
            'add this cell to wiki output string
            If thisRow = 1 Or thisCell.Interior.ColorIndex <> xlNone Then
                'heading row or colored cell
                wikiText = wikiText & "^" & thisCellText
            Else
                wikiText = wikiText & "|" & thisCellText
            End If
 
        Next thisCol
        'end this row
            If thisRow = 1 Or thisCell.Interior.ColorIndex <> xlNone Then
                'heading row or colored cell
                wikiText = wikiText & "^" & Chr(13)
            Else
                wikiText = wikiText & "|" & Chr(13)
            End If
 
    Next thisRow
 
 
    'now copy to clipboard
    oData.SetText (wikiText)
    oData.PutInClipboard
    MsgBox "Selected cells were copied into clipboard", vbInformation
 
End Sub

Retrieving the formatted value

It is possible to retrieve the formatted value instead of the raw value by replacing thisCell.Value with thisCell.Text. This means that the code will keep the numbers formatted as e.g. a percentage with the correct number of decimals.

            thisCellText = thisCell.Text

Table of Contents