====== 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|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:**_[[plugin:tablewidth|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.

<code vb 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
</code>
===== 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.
<code vb 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
</code>


===== 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.

<code vb 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
</code>

===== 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.
<code vb 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
</code>



===== 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.

<code vb 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
</code>

===== 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.
<code vb>

            thisCellText = thisCell.Text
</code>