forked from apfernandes/excelToMarkdown
-
Notifications
You must be signed in to change notification settings - Fork 0
/
excelTableToMarkDown.vb
140 lines (88 loc) · 3.96 KB
/
excelTableToMarkDown.vb
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
Sub rangeToMarkDown()
Dim cell As Range
Dim selectedRange As Range
Set selectedRange = Application.Selection
Dim rowCounter As Integer
Dim columnCounter As Integer
Dim totalColumns As Integer
Dim currentColumnWidth As Integer
Dim FullMarkDown As String
totalColumns = selectedRange.Columns.Count
Dim columnWidth(50) As String 'maximum of 50 columns
'///
'/// init lengths of columns
'///
For i = 0 To totalColumns
columnWidth(i) = 0
Next i
'///
'/// go through range to calculate maximum lengths of each column
'///
For Each Row In selectedRange.Rows
columnCounter = 0
For Each cell In Row.Cells
currentColumnWidth = Len(cell.Value)
If (currentColumnWidth > columnWidth(columnCounter)) Then
columnWidth(columnCounter) = currentColumnWidth
End If
columnCounter = columnCounter + 1
'/// Debug.Print cell.Address, " ", cell.Value, "->", Len(cell.Value)
Next cell
Next Row
'///
'/// go through range to calculate maximum lengths of each column
'///
Dim currentLine As String
rowCounter = 0
For Each Row In selectedRange.Rows
columnCounter = 0
currentLine = "|"
For Each cell In Row.Cells
currentColumnWidth = columnWidth(columnCounter)
Dim extraSpaces As Integer
currentLine = currentLine & " "
currentLine = currentLine & cell.Value
extraSpaces = currentColumnWidth - Len(cell.Value)
For j = 0 To extraSpaces
currentLine = currentLine & " "
Next j
currentLine = currentLine & " |"
columnCounter = columnCounter + 1
'/// Debug.Print cell.Address, " ", cell.Value, "->", Len(cell.Value)
Next cell
' Debug.Print "Currentline is: " & currentLine
' Debug.Print "Without CRLF : " & Replace(currentLine, vbCrLf, "")
' Replace(str, vbCRLf, "")
' remove CRLFs from current line, they will break Markdown
FullMarkDown = FullMarkDown & vbCrLf & Replace(currentLine, vbLf, " ")
'Debug.Print "Full Markdown so far: " & vbCrLf & "------------------" & vbCrLf & vbCrLf & FullMarkDown & vbCrLf & vbCrLf & "------------------"
If (rowCounter = 0) Then
currentLine = "|"
columnCounter = 0
For j = 0 To (totalColumns - 1)
currentLine = currentLine
currentColumnWidth = columnWidth(columnCounter)
currentLine = currentLine & "-"
For k = 0 To currentColumnWidth
currentLine = currentLine & "-"
Next k
currentLine = currentLine & "-|"
columnCounter = columnCounter + 1
Next j
' Debug.Print "Currentline is: " & currentLine
' Debug.Print "Without CRLF : " & Replace(currentLine, vbCrLf, "")
' Replace(str, vbCRLf, "")
' remove CRLFs from current line, they will break Markdown
FullMarkDown = FullMarkDown & vbCrLf & Replace(currentLine, vbLf, " ")
'Debug.Print "Full Markdown so far: " & vbCrLf & "------------------" & vbCrLf & vbCrLf & FullMarkDown & vbCrLf & vbCrLf & "------------------"
End If
rowCounter = rowCounter + 1
Next Row
' Copy Markdown to Clipboard immediately
' needs Microsoft Forms 2.0 Library enabled
Dim clip As New DataObject
clip.SetText FullMarkDown
clip.PutInClipboard
'MsgBox (FullMarkDown)
' Clipboard FullMarkDown
End Sub