Excel spreadsheet table to Tiddlywiki export macro

[todd.c...@gmail.com]

Hi all,

I wrote a simple export macro for creating a table in Excel and
exporting the selection in Tiddlywiki syntax (a modification of this
script: http://www.lacher.com/examples/a960521b.htm). It could be
improved by copying data to the clipboard rather than writing to a
file.

Todd


Rem ***** BASIC *****

Sub TiddlyWikiExport()

' Dimension all variables
Dim DestFile As String
Dim FileNum As Integer
Dim ColumnCount As Integer
Dim RowCount As Integer
Dim Clipboard As String

' Prompt user for destination filename

DestFile = InputBox("Enter the destination filename" _
& Chr(10) & "(with complete path):", "Quote-Comma Exporter")

' Obtain next free file handle number
FileNum = FreeFile()

' Turn error checking off
On Error Resume Next

' Attempt to open destination file for output
Open DestFile For Output As #FileNum

' If an error occurs report it and end
If Err <> 0 Then

MsgBox "Cannot open filename " & DestFile
End

End If

' Turn error checking on
On Error GoTo 0

' Loop for each row in selection
For RowCount = 1 To Selection.Rows.Count

' Write the initial table tag
Print #FileNum, "|";

' Loop for each column in selection
For ColumnCount = 1 To Selection.Columns.Count

' Write the background color tag
If Selection.Cells(RowCount, ColumnCount).Interior.Color <>
vbWhite Then
ColorToRGB CStr(Selection.Cells(RowCount,
ColumnCount).Interior.Color), r, g, b
Print #FileNum, "bgcolor(#" & r & g & b & "): ";
End If

' Write the initial bold tag
If Selection.Cells(RowCount, ColumnCount).Font.Bold = True
Then
Print #FileNum, "''";
End If

' Write the initial italics tag
If Selection.Cells(RowCount, ColumnCount).Font.Italic = True
Then
Print #FileNum, "//";
End If

' Write the initial strikethrough tag
If Selection.Cells(RowCount, ColumnCount).Font.Strikethrough =
True Then
Print #FileNum, "---";
End If

' Set right alignment
If Selection.Cells(RowCount, ColumnCount).HorizontalAlignment
= xlRight Or _
Selection.Cells(RowCount, ColumnCount).HorizontalAlignment
= xlCenter Then
Print #FileNum, " ";
End If

' Write the initial font color tag
If Selection.Cells(RowCount, ColumnCount).Font.Color <>
vbBlack Then
ColorToRGB CStr(Selection.Cells(RowCount,
ColumnCount).Font.Color), r, g, b
Print #FileNum, "@@color(#" & r & g & b & "):";
End If

' Write the initial hyperlink tag
If Selection.Cells(RowCount, ColumnCount).Hyperlinks.Count > 0
Then
Print #FileNum, "[[";
End If

' Write current cell's text
Print #FileNum, Selection.Cells(RowCount, ColumnCount).Text;

' Write the initial hyperlink tag
If Selection.Cells(RowCount, ColumnCount).Hyperlinks.Count > 0
Then
Print #FileNum, "|" & Selection.Cells(RowCount,
ColumnCount).Hyperlinks(1).Address & "]]";
End If

' Write the ending font color tag
If Selection.Cells(RowCount, ColumnCount).Font.Color <>
vbBlack Then
Print #FileNum, "@@";
End If

' Set left alignment
If Selection.Cells(RowCount, ColumnCount).HorizontalAlignment
= xlLeft Or _
Selection.Cells(RowCount, ColumnCount).HorizontalAlignment
= xlCenter Then
Print #FileNum, " ";
End If

' Write the ending strikethrough tag
If Selection.Cells(RowCount, ColumnCount).Font.Strikethrough =
True Then
Print #FileNum, "---";
End If

' Write the ending italic tag
If Selection.Cells(RowCount, ColumnCount).Font.Italic = True
Then
Print #FileNum, "//";
End If

' Write the ending bold tag
If Selection.Cells(RowCount, ColumnCount).Font.Bold = True
Then
Print #FileNum, "''";
End If

' Write the ending table separator
Print #FileNum, "|";

' Check if cell is in last column
If ColumnCount = Selection.Columns.Count Then
' If so then write a blank line
Print #FileNum,
End If

' Start next iteration of ColumnCount loop
Next ColumnCount

' Start next iteration of RowCount loop
Next RowCount

'Close destination file
Close #FileNum

End Sub

Sub ColorToRGB(ByVal Color As String, ByRef r, ByRef g, ByRef b)

On Error GoTo Solution
Dim SStr As String
SStr = "000000" & Hex(Color)
SStr = Right(SStr, 6)
b = Mid(SStr, 1, 2)
g = Mid(SStr, 3, 2)
r = Mid(SStr, 5, 2)

If Len(r) < 2 Then r = "0" & r
If Len(g) < 2 Then g = "0" & g
If Len(b) < 2 Then b = "0" & b

Solution:
If Err.Number <> 0 Then
r = -1
g = -1
b = -1
End If
End Sub

[Eric Shulman]

> I wrote a simple export macro for creating a table in Excel and
> exporting the selection in Tiddlywiki syntax (a modification of this
> script:http://www.lacher.com/examples/a960521b.htm). It could be
> improved by copying data to the clipboard rather than writing to a
> file.

Consider this alternative approach:

Save your Excel spreadsheet using CSV (comma-separated value) format.

Then, edit the resulting file, and change each line from:
field,field,field
to
|field|field|field|
(i.e., replace commas with "|" and put "|" at the beginning and end of
the line). This turns the CSV output into a TW-formatted table that
you can then copy/paste into a tiddler... or, you could use
http://www.TiddlyTools.com/#FileDropPlugin
http://www.TiddlyTools.com/#FileDropPluginConfig
to drag-and-drop the edited CSV file onto your TW document to
automatically create and fill a new tiddler for you!

HTH,
-e
Eric Shulman
TiddlyTools / ELS Design Studios

[wolfgang]

Hi everyone,

Due to the kindness of Xavier Verges in responding to my request, I
nowadays use the following html textbox in my TWs, into which I paste
excel tables. And after pushing 'translate' am presented with
TiddlyWiki's table formatting. Ready to be pasted into a tiddler
(beware of GoogleGroups new lines in pasted code here):

<html><p align="center"><textarea id="xclToTw" rows="10" cols="60"></
textarea><button onclick="(function(){var
ta=document.getElementById('xclToTw'); displayMessage('original: ' +
ta.value); ta.value=ta.value.replace(/^|$|\t/gm, '|');})();" >
Translate</button></p></html>

http://vipa.3host.biz/TiddlyDesktop.html#ExcelToTW

Regards,

W.

[todd.c...@gmail.com]

Small update to allow newlines (linefeeds) within a cell:

replace:
Print #FileNum, Selection.Cells(RowCount,
ColumnCount).Text;

with:
content = Replace(Selection.Cells(RowCount,
ColumnCount).Text, Chr$(10), "<<br>>")
Print #FileNum, content;

[Eric Shulman]

> content = Replace(Selection.Cells(RowCount,
> ColumnCount).Text, Chr$(10), "<<br>>")

note: the <<br>> syntax is deprecated, as is no longer directly
supported in the TW2.3 core. Use the simpler form: "<br>" instead.

[Xavier Vergés]

Todd,
using the clipboard from excel seems simple (but I haven't tried):
http://www.cpearson.com/excel/clipboard.htm

To show code,
http://tiddlywiki.pastebin.com/
is very handy for everyone. It would be cool if you also pasted a
tiddler with some sample results.

-Xv

On Jan 3, 7:17 pm, "todd.chamb...@gmail.com" <todd.chamb...@gmail.com>
wrote:

[todd.c...@gmail.com]

Thanks Eric and Xavier,

I posted the revised code to http://tiddlywiki.pastebin.com/f7848a6a7.
This is my first crack at VB, but you have to add a "Reference" to the
Microsoft Forms 2.0 library: Tools->References..., Browse for
FM20.dll
to make the clipboard work (heck of a lot easier than writing to the
file though ;) ).

Todd

On Jan 3, 1:59 pm, "Xavier Vergés" <xver...@gmail.com> wrote:
> Todd,
> using the clipboard from excel seems simple (but I haven't tried):http://www.cpearson.com/excel/clipboard.htm
>

> To show code,http://tiddlywiki.pastebin.com/