Excelの表からMarkdown形式の表を作成するマクロ

ソースコード

Excelの選択した範囲のセルを、Markdown形式でクリップボードにコピーする。

※利用にはMicrosoft Forms 2.0 Object Libraryへの参照設定が必要

http://www.relief.jp/itnote/archives/017881.php
http://answers.microsoft.com/ja-jp/office/forum/office_2010-excel/excel2010%E3%81%AEvba%E3%81%AE%E5%8F%82%E7%85%A7/360f7066-27d3-4c91-b8b3-79131ab4ba28

Option Explicit

Const MARKDOWN_TYPE As String = "Normal"

' Markdownの表形式に変換
Function convertMarkdownTableFormat(element As String)
    convertMarkdownTableFormat = "|" & element
End Function

' 各Markdownの種類に基いて、表形式フォーマットを作成
Function makeMarkdownTableFormatFromType(markdownType As String, number As Long)
    Dim count As Long
    Dim buffer As String

    buffer = ""
    If markdownType <> "" Then
        For count = 1 To number Step 1
            buffer = buffer & markdownType
        Next
        buffer = buffer & "|" & vbCrLf
    End If
    
    makeMarkdownTableFormatFromType = buffer
End Function

' Clipboardに値を設定
Sub setClipBoard(data As String)
    Dim ClipBoard As New DataObject
    
    With ClipBoard
        .SetText data       ''変数のデータをDataObjectに格納する
        .PutInClipboard     ''DataObjectのデータをクリップボードに格納する
    End With
    
End Sub

Sub main()
    Dim markdownType As Object
    Set markdownType = CreateObject("Scripting.Dictionary")
    markdownType.Add "Normal", "|:-:"
    markdownType.Add "Redmine", ""

    Dim selectionRange As Range
    Dim selectionCell As Range
    
    Dim leftEdgeColumn As Long
    Dim leftEdgeRow As Long
    Dim rightEdgeColumn As Long
    Dim rightEdgeRow As Long
    Dim currentColumn As Long
    Dim currentRow As Long
    
    Dim element As String
    Dim buffer As String
    Dim markdownTableFormat As String
    
    leftEdgeColumn = Selection.Column
    leftEdgeRow = Selection.Row
    rightEdgeColumn = Selection(Selection.count).Column
    rightEdgeRow = Selection(Selection.count).Row
    
    ' 表の1行目を読みこむ
    buffer = ""
    For currentColumn = leftEdgeColumn To rightEdgeColumn Step 1
        element = Cells(leftEdgeRow, currentColumn)
        buffer = buffer & convertMarkdownTableFormat(element)
    Next
    buffer = buffer & "|" & vbCrLf
    
    ' 各Markdownの種類に基いて、表形式フォーマットを作成
    markdownTableFormat = markdownType.Item(MARKDOWN_TYPE)
    buffer = buffer & makeMarkdownTableFormatFromType(markdownTableFormat, rightEdgeColumn - leftEdgeColumn + 1)
    
    ' 表の2行目以降を読み込む
    For currentRow = leftEdgeRow + 1 To rightEdgeRow Step 1
        For currentColumn = leftEdgeColumn To rightEdgeColumn Step 1
            element = Cells(currentRow, currentColumn)
            buffer = buffer & convertMarkdownTableFormat(element)
        Next
        buffer = buffer & "|" & vbCrLf
    Next
    
    ' Clipboardに値をコピー
    Call setClipBoard(buffer)
    
End Sub

下記アドインにより洗練された機能が付いている模様↓

■ 参考

• http://qiita.com/taruhachi/items/5bd18d7de4c3fa40dd23
• http://qiita.com/QUENCH/items/d77d0983d4a6d9d7de8e
• http://officetanaka.net/excel/vba/tips/tips20.htm