User talk:DBP

Excel Macro for making wiki tables

Here is an excel VBA macro that prints out a table to a plain text file that is formatted for cutting and pasting into the wiki editor.

Over time I may try to enhance it to preserve excel formatting. For now it does not. It arbitrarily makes the first row bold and centered and the rest of the table is left justified plain text. I think it should be possible to enhance this to preserve cell shading, justification and other font properties. Maybe someone already has this?

--begin cut-n-paste--

Option Explicit
Public Sub wikitable()
'This subroutine assumes your active sheet has a table with the top left corner in cell A1.
'It writes out a new file in the same directory as the excel file, with the same file name
'as the excel file, but a file type or extension of ".wiki"
'The format of this output file is designed to be cut-n-paste into a wiki page
'
Dim Source, Dest As Worksheet
Dim Dest_File, Source_Path, caption, res As String
Dim dest_bk As Workbook
Dim S_rows, S_cols, D_row As Long
Dim scr_upd, first As Boolean
Dim r, r2 As Range
Dim fid As Integer
'
scr_upd = Application.ScreenUpdating
Application.ScreenUpdating = False
Set Source = ActiveSheet
Source_Path = ActiveWorkbook.path
Dest_File = Source_Path & "\wikitable_" & mid(ActiveWorkbook.name, 1, Len (ActiveWorkbook.name) - 4) & ".wiki"
fid = FreeFile
Open Dest_File For Output As fid
Print #fid,
Print #fid, "{| cellpadding=""3"" cellspacing="; 0; " border=""1"""
Print #fid, "|+align=""bottom"" style=""color:#e76700;""|" & caption & ""
S_rows = Source.Range("A65536").End(xlUp).Row
S_cols = Source.Range("A1").End(xlToRight).Column
For Each r In Source.Range(Cells(1, 1), Cells(1, S_cols))
    Print #fid, "!" & r.Value
Next r
Print #fid, "|-"
For Each r In Source.Range(Cells(2, 1), Cells(S_rows, 1))
    For Each r2 In Source.Range(Cells(r.Row, 1), Cells(r.Row, S_cols))
        If Application.IsNumber(r2.Value) Then
            res = Format(r2.Value, r2.NumberFormat)
        Else
            res = r2.Value
        End If
        Print #fid, "|" & res
    Next r2
    Print #fid, "|-"
Next r
Print #fid, "|}"
Close #fid
Application.ScreenUpdating = scr_upd
End Sub

--end cut-n-paste

--Don 17:33, 9 December 2006 (UTC)

Interesting! Note that it seems that "|" should be "| ", otherwise it does not work if a negative numbers follows.--Patrick 22:03, 9 December 2006 (UTC)

Right on. I suppose the separator for the first row should also be "! " rather than "!". Thanks --Don 22:01, 10 December 2006 (UTC)

Corrected a slight typo in your "description" Don. Somehow the word smae just didn't seem right Other than that, looks good, dood. 69.182.165.163 18:22, 24 September 2010 (UTC)