Wikiup:Technik/Text/Basic/EXCEL-Tabellenumwandlung/en

Zur deutschsprachigen Version dieses Textes siehe Wikiup:Technik/Text/Basic/EXCEL-Tabellenumwandlung

Description: VBA-Macro for EXCEL tableconversion

This is version V1x of EXCEL-VBA-Macro format_as_wikitable. This Macro converts any selected range of cells into wiki-text, that are copied via the clipboard into the wiki-editor.

Functional reference

• Converts an arbitrary selected cellrange into wiki table format with all essential formatting information
  • fontsize, color, style (italic, bold, underlined)
  • background color of the cell
  • text aligment in the cells (horizontal and vertical)
  • column width and row height
  • hyperlinks
• further on the wiki output is size optimized; that means the cell formatting code is only repeated if necessary, e.g. when another formatting option has been active on the line cell before the actual cell
• because the frameformatting concepts of EXCEL and Wiki-table are not compatible, the frame-formatting is not converted - sorry for that.

Comments and possible errorreports should be posted under discussion.

VBA-Source

Please copy the VBA-Source from Wikipedia:Technik/Text/Basic/EXCEL-2003 Tabellenumwandlung VBA

Tips for usage

Optimize your formatting in EXCEL to get better results

Some problems and limitations in representation are listed below. An overview about representation power of this tool you get here.

Limits in rendering

This macro aims to convert a EXCEL-table as near to wikicode as possible. Ideally the table is rendered identically in both table-representation-clients. Differences in the rendering concepts of EXCEL and wiki-table limit this goal in certain areas:

• Cell formatting: In EXCEL each cell can have its individual frame information on all four sides. This is not possble in wiki-table, where the framing is defined on a tablewide standard formatting.

Avoid formats in EXCEL that lead to unwanted results

An EXCEL table that is parsed with VBA-code returns the cell content programmatically differently under certain circumstances, as it is visually rendered.

The following known problems can be avoided by used explicit different EXCEL formatting options:

• Locale Non-US date formats are not correctly exported by the EXCEL Macro interface of Microsoft. There is no known solution to this.
• Number format Standard should be avoided. Content of such cells is not exported correctly by EXCEL-Macro interface of microsoft; e.g. the number "1000" will be exported as "(1000)".

Avoid Rendering conflict due to differences in default formatting rules between EXCEL and wiki-table

There are some differences in default formatting rules between EXCEL and wiki-table, which will lead to implicit changes in the cell rendering. These conflicts can be avoided by using explicit formatting rules in EXCEL. You will have to override the EXCEL defaults by explicitly using attributing formats. You will see no difference in EXCEL, the difference will only emanate when rendering the converted wiki-output. This mainly concerns

• Horizontal alignment
• Vertical alignment

How to use this VBA-Macro

In short: Select the macrotext above, insert it into a EXCEL/VBA-Modul and start. If more details are needed, follow the instructions below, the commands may vary slightly depending on your excel version:

1. With Excel use: File > Open and open the .xls file of the spreadsheet table you want to convert.
2. Open the macro-editor (menu: Special/Macro/Visual-Basic-editor) or 'Alt-F11'
3. In the left window you should see the open VBA-Projects in the Project-Explorer. If not open that subwindow with CTR-R or View>ProjectExplorer.
4. There you right-click on 'VBA-Project’ with the cursor placed at the name of your actual document
5. In the context menu use Insert/Module
6. Somewhere eg. in the right you will get a popup window with a empty white area, with two dropdownboxes on the top (General and Declarations)
7. Select the entire vba-Code referenced above and insert it into that empty white area of the VBA editor. Later when saving this, you will actually create a new module.
8. Go back to your worksheet, do the final formatting of your table, then select the cellrange you want to convert into a wiki-table.
9. Execute the macro format_as_wikitable (On the menu select "Tools/Macro/Macros...". A dialog box with the title "Macro" will appear. Click the name "Format_as_wikitable" in the list, then click the "Run" button.)
10. A new worksheet with name "Wikioutput" is created, where the wikitext is written
11. Copy this text via clipboard into the wiki-editor
12. Use the preview function of the wiki-editor
13. When you are satisfied with the results save your wiki-work

There might be some slight differences in your default window-layouts in VBA-Editor. Please also keep in mind that I am working on a german EXCEL-Installation. Although the menu pathnames etc. are somewhat blindly translated into english it is possibile to improve on the english installation.

Comments or any problems may be posted to talk page, which is on my watchlist. --ollio 13:21, 10. Apr 2006 (CEST)

Using the macro as EXCEL-Add-in

You can also store an EXCEL-file with the macro-code as an XLA-addin-file. Thereafter you can use the macro as a addin-command, so you don’t have to copy it into each file where you want convert a table.

How to install a macro as EXCEL-Add-in:

First you install the macro in file, with an empty worksheet. You can then generate a XLA-Addin File:

• Save this file as XLA-Addin: File > SAVE as. In the SaveAs-Dialog you have to change the filetype to XLA.
• Give the XLA-File a meaingfull name (e.g.: Writewikitable.xla). The path of the file is automatically changed to C:\Own documents\myUsername\application data\Microsoft\AddIns. Save the file now.
• Close EXCEL and reopen it
• Under Extras > Addins you will now see your newly created XLA-Addin. You must activate it by its checkbox
• The macro Format_as_wikitable is now available - even it is not visible under Extras > Macro > Execute.

Of course it’s now troublesome to tiping blindly the macroname everytime you want to use it. To help yourself you can create a userdefined menuentry and assign this macro to it. --ollio 20:57, 16. Okt. 2006 (CEST)

Samples of created output: Link to the gallery

69.159.107.152 has added an sample of created output. It has been transferred to the gallery. Please trim your samples not be too big. --ollio 09:36, 5. Nov. 2006 (CET)