Wikipedia:Helferlein/VBA-Macro for EXCEL tableconversion
aus Wikipedia, der freien Enzyklopädie
Inhaltsverzeichnis |
[Bearbeiten] 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 a wiki-text, that are copied via the clipboard into the wiki-editor.
[Bearbeiten] Functional reference
- Converts an arbitrary selected cellrange into wiki-tableformat with all essential formatting information
- fontsize, -color, -style (italic, bold, underlined)
- background color of the cell
- textaligment in the cells (horizontal und vertical)
- columnwidth and lineheight
- further on the wikioutput is volumeoptimized, that means the cellformatting code is only repeated if necessary, e.g. when another formatting option has been active an the linecell 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.
[Bearbeiten] VBA-Source
Please copy the VBA-Source from here
[Bearbeiten] Tips for usage
[Bearbeiten] 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.
[Bearbeiten] 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:
- Frameformating: In EXCEL each cell cann have its individuel frame information on all four sides. This is not possble in wiki-table, where the framing is defined on a tablewide standard formatting.
[Bearbeiten] Avoid formats in EXCEL that lead to unwanted results
A EXCEL-table that is parsed with VBA-code returns the cellcontent under certain circumstances programmatically not the same, as it is visually rendered.
The following known problems can be avoided by used explicit diffrent EXCEL-Formatting options:
- Locale Non-US Dateformats are not correctly exported by EXCEL-Macro interface of microsoft. There is no known solution to this.
- Numberformat 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)".
[Bearbeiten] Avoid Renderingconflict 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 cellrendering. 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
[Bearbeiten] 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:
-
- With Excel use: File > Open and open the .xls file of the spreadsheet table you want to convert.
- Open the macro-editor (menu: Special/Macro/Visual-Basic-editor) or 'Alt-F11'
- 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.
- There you right-click on 'VBA-Project' with the cursor placed at the name of your actual document
- In the context menu use Insert/Module
- 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)
- Select the entire vba-Code above and insert it into that empty white area. Later when saving this you will actually create a new module.
- Go back to your worksheet, do the final formatting of your table, then select the cellrange you want to convert into a wiki-table.
- Execute the macro format_as_wikitable
- A new worksheet with name "Wikioutput" is created, where the wikitext is written
- Copy this text via clipboard into the wiki-editor
- Use the preview function of the wiki-editor
- 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 Discussionpage, which is on my watchlist. --ollio 13:21, 10. Apr 2006 (CEST)
[Bearbeiten] 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.
Howto 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)
[Bearbeiten] Samples of created output: Link to the gallerie
69.159.107.152 has added an sample of created output. It has been transferred to the gallerie. Please trim your samples not be too big. --ollio 09:36, 5. Nov. 2006 (CET)
