Wikipedia Diskussion:Textverarbeitung/EXCEL-Tabellenumwandlung/VBA-Macro for EXCEL tableconversion

Please enter new topics at the end.

Version V10

10.4.2006: Version V10 eingestellt --ollio 13:36, 10. Apr 2006 (CEST)

Tried to run macro in Apple Enviroment

running a apple version of excel and this bug appeared,

Private Sub writecell(colnr As Long)
oline = oline + 1: orange.Cells(oline, 1) = formatstring_for_a_cellcontent(False, colnr = 1) & " | " & _
                                           process_cellcontent(selrange.Cells(iline, icolumn))
End Sub

there's a little yellow arrow in the margin, and hmmm??? --70.52.24.207 02:44, 23. Apr 2006 (CEST)

• Is this a compilertime error or a runtime error? What is the text of the error message? What version of EXCEL and what OS-Version do you use? --ollio 19:22, 24. Apr 2006 (CEST)

tried V10 "run error -91" appears and this is highlighted in yellow "With selrange.Cells(iline, 1) 'take first column as reference" --70.52.24.207 02:48, 23. Apr 2006 (CEST)

• The program has been developed an tested on Windows-XP with EXCEl-2003. I haven't been provided with a MAC for testing yet. Though think, I will not be able to reproduce your error. As long as you can not provide an acurate verbose error-text that explains the possible problem, I can't give you a clue what the problem might be of my macro in your environment. Operating systems and appplications programms normally provide natural language explanations for any errors, if installed correctly. Sometime these message are hidden in statusline or behind a popup that has to be activated by pressing some HELp-Button or the like. Try to be a bit more inquisitive by gather helpful context information.
• I there is somebody else with MAC-OS that has gather experience with this macro? Please report any positive of negative feedback. --ollio 19:22, 24. Apr 2006 (CEST)
• I tried using this marco on a Mac and I got the same error. I had to change a line of code to get it to work. It was a quick fix and I am sure there is a better way to do it. In the process_cellcontent function I changed

  process_cellcontent = Replace(cellcontent, vbLf, "<BR>")

  to

  process_cellcontent = cellcontent 

  I hope this helps. Maya
• Hello Maya, thanks for your feedback, which will certainly help the macusers and encourage them to use the macro. There is one thing MAC-users should consider: The codechange of Maja will also change the functionality a little bit. The line

 ' V13: replace linebreaks in cellcentent with a Wiku-<BR> to avoid havoc in wiki-rendering thanks feedback of ManWing2, 26. Sep 2006
  process_cellcontent = Replace(cellcontent, vbLf, "<BR>")

is intended to provide correct rendering of multiline-text in one cell. With Maja's change that won't work nomore that way. So its as Maja stated: A quick-fix - but not entirely proper. Maybe some with a MAC and some programming skills may provide a better fix. Still unclear is the proper errormessage and the statement about errortype (compiletime error or a runtime error). That might help. It will also help to make more clear if two errorreports consider the same error-occurance. Kind regard, --ollio 20:15, 23. Okt. 2006 (CEST)

Executing your manual for excel -> wiki

Hello,

Thank you very much for your macro! I have however some problems with it:

In the manual you write:

• In the left window you see the open VBA-Projects
• There you right-click on 'VBA-Project' with the cursor placed at the name of your actual dokument

I cannot see this, I just have an empty white page, divided in tow parts (main page above and a smaller division 'execution' below), but I continue:

• In the contextmenü use Insert/Modul
• In the right window you will get a empty white area

There is no right window, but a new one opens

• Select the entire vba-Code above and insert it into that new modul

done

• Go back to your worksheet, do the final formatting of your table, then select the range you want to convert into a wiki-table

done

• Execute the macro format_as_wikitable

done! Now I have a nice table in the wiki, however without the cell borders. Is there any way to include these?

Just a remark: I get an error, if I select the range you want to convert into a wiki-table before inserting the macro: "ineattribut_fondcolor = selrange.Cells(iline, 1).Font.Color" It works only if I do not choose anything before starting to execute what you describe. --ChristianeT 14:13, 8 June 2006 (EDT)

• Hello Christiane, I did some changes to manual, hope they help clarifing. Keep in mind that EXCEL 1.) I do not work on english EXCEL, nor do I ~2.) have an english installation to prove all the details of the manual. Though that's not professional, I would neither be paid for such expenses or any work invested into this volunteering project. Anyway it seems you got the VBA-code to do its work. Congratulations.

Concerning the borders: I don't embeed wiki-Code for individual borders on the cell level. I thougth about it, but dissmissed such plans. It would e.g. blow up the volume of generated wikicode, besides the extra programming work :-) Anywhay there ist a {{prettytable}}-directive on top of the table. This directive formattes a nice tableborder. You can see a sample table of mine at sample table. Of course that directive must be definied on your home wiki-project. What wiki-project are you native from? Maybe you should check if that prerequiste is meet there. Write here if you need furter assistance. kind regards, --ollio 22:21, 8. Jun 2006 (CEST)

Version 12

English text: support in quality assurance

I'm use the English Wikipedia and have uploaded and run your macro with success. Although the wiki version of the table does not include merged cells or center formating it does substantually reduce the size of the table.

I have edited the english text of the article to improve the English and will check back from time to time to see if there is anything else I can improve. Thanks for this macro. It will make it possible now for me to use tables on the Wikipedia all of the time. 209.216.92.232 17:06, 3. Jul 2006 (CEST)

General Options in the VBE: Break on unhandled errors is mandatory

Well done, Ollio. I've done a large amount of coding in VBA for Excel, and I know good work when I see it.

On the first run, I got a runtime error at:

Worksheets(tabname).Select

in Function WorksheetExits. I see what you're doing with On Error, but in my English language version I needed to set the General Options in the VBE to Break on Unhandled Errors. (Also, I think you intended WorksheetExists, not WorksheetExits, which I found a little confusing at first.) 4.241.216.47 19:17, 17. Jul 2006 (CEST)

Hallo, in german excel version I've got 3 options under "General Options in the VBE", whereof one has to be selected:

• Break at each error (Unterbrechen bei jedem Fehler)
• Break at error in class moduls (Unterbrechen bei Fehler in Klassenmodul)
• Break on Unhandled Errors (Unterbrechen bei nicht verarbeiteten Fehlern)

I guess you had the first option active and the error trapping in the program was so ignored. Is it that? Strange is, that at my installation the trapping works in all optioncases, that is, even if the first option is selected. Though maybe there is another setting (e.g. Registery-setting) that overrides this option setting in my environment. Maybe I will try to check this condition and modify it accordingly in the code in some future release.

Of course I meant the meaning of the function as Function WorksheetExists(tabname As String) As Boolean. I will fix that semantic typo in the next functional release.

thanks for your feedback. --ollio 22:05, 25. Jul 2006 (CEST)

The Options dialog box in the VBE-editor contains the General tab (EXCEL-2000 and later), where is an Error Trapping section. Here you can specify what happens when an error occurs in VBA-code. There are the three different options in the Error Trapping section, mentioned above. According to microsoft, the default setting is Break on Unhandled Errors. When you change the option to Break on every error, all VBA-Code using coded error trapping routines ( on error goto ... ) won't work. As the macro Format_as_wikitable uses code with diffrent trapping routines, you must make sure that error trapping settings corresponds to the microsoft default of Break on Unhandled Errors. Kind regards, --ollio 23:15, 25. Jul 2006 (CEST)

dynamically creating wikioutput tab as last tab

   ' create output worksheet
   removed:Worksheets.Add 'always added at first place (?)
   removed:Worksheets(1).Name = outtabName
   Call Utility.AddSheetIfNotExists("wikioutput")
   Worksheets("wikioutput").Select

Hi <unsigned autor>, for the discussion here I use the languages english or german, but not VBA. It would be nice if you comment your opignion and purpose instead of providing nonfunctional modifications of statements. Your code obviously calls a local class of your own environment and does not work for the public. I personally don't bother that much the position of the newly added tab. --ollio 22:19, 25. Jul 2006 (CEST)

Thank you and a challenge

On a whim, I did a Google Search to see if anyone had come up with a way to convert tables from Excel to Wikitable. Thanks so much for creating this, and I look forward to seeing it evolve.

I do have a challenge, however. I wonder if it is possible to create a set of macros that would take copied wikitable source code, and drop the data onto an excel sheet. I know that you can copy the html table source, but that's a lot of extra code that shouldn't be necessary. I know it's possible to do it from XML source code as well. Anyway, thanks! Chadlupkes 03:48, 30. Dez. 2006 (CET)

Hi, no need for doing any coding here. Just select the table in the HTML-Window, copy it to the clipboard, paste it into EXCEL. You will get all content, formatting, coloring etc. If you have problems doing this, make sure that your really select the HTML-Tableheader and -tail. You can achieve this by selecting one more character outside from the outside of the table, both a the tablehead and tail . --ollio 21:55, 19. Jan. 2007 (CET)

Wish-list for colspan and rowspan

Thanks a lot for the great macro you provide to convert xls spreadsheets to mediawiki.

As it is written in the tool presentation, the result is made of individual cells : "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."

You say it is not possible, but to my knowledge under Mediawiki you can use colspan and rowspan : so if the xls cell is a merged cell would it be possible to have the same rendering in the output via colspan and rowspan ? Jean Cognard 12:02, 19. Jan. 2007 (CET)

Hello, I agree that this might be possible. I will think about that, but to get coding it I've first to get in the mood of doing again such volontary coding work in this area. Right now I have other priorities, though in any case you have to be patient, sorry. --ollio 22:01, 19. Jan. 2007 (CET)

I've added support for colspan and rowspan. --82.110.244.146 14:49, 13. Apr. 2007 (CEST)

Adding a & nbsp; in empty cells

To start with: a very nice macro! Wonderful! It saves me a lot of time converting Excel-sheets with colors/bold etc into the right format!

One question though: Is it possible to add something in the code (I am not a whizzkid) that will insert a "nobreakspace" (& nbsp;) into an empty cell the macro will process? For this moment if you have an empty cell it is not displayed properly as a real 'empty' cell in the wikitable. If you add a "nobreakspace" (& nbsp;) it shows at least a cell with nothing in it.

Thanks in advance!
Bart -- 16:08, 15. Feb. 2007 194.237.142.21

Thanks Bart for your valuable a precisely feedback. Please download and tryout V14, it should work the way you mean. Otherwise please report it here. :It's changed and tested.

It has been an pleasure,

Othmar
ollio 23:20, 15. Feb. 2007 (CET)

nbsp does not appear

The "nbsp" in the source code is not appearing on the wiki page. This seems to be a bug of MediaWiki. If a user copies the text from the wiki page they will have a version with a whitespace, like this: cellcontent = " " instead of having an nbsp. mw:User:Fernando.correia

If you have this problem and your solution ok for you. In my test I tested "nbsp" in this wiki with a sample table and it worked. --ollio 22:47, 2. Mär. 2007 (CET)

Take a look, the wiki page is not showing the "nbsp". Now somebody even edited the "nbsp" out of the source code. I wonder if this will not insert the bug back. --mw:User:Fernando.correia 15:45, 5. Mär. 2007 (CET)

Ok, first I have to say that I'm not happy with people messing up with the sourcecode. It's protected by a GNU-License. I'm even a little upset if someone does this, using a anonymous IP-Account, please don't do this. No to to subject:

Sample of correct table rendering

Below you see the table rendered with code cellcontent = " ". A simple whitespace is not displayed in a wikitable, it is simply absorbed. It's the same effect if simply coded cellcontent = " ".

c

Below you see the table rendered with code cellcontent = " ". This changes the cellrendering though that parameter like cellheight are respected in the wiki rendering. This is the purpose of the V14 changes.

Test

For convenience a added a binary variable to switch between these two possiblities. Please switch the this variable your local copy of the code. Please do not edit the sourcecode. I Hope this will cover both of the differgent wishes. --ollio 23:52, 5. Mär. 2007 (CET)

Alternate method to add Macros to Excel

Al Lowenstein, Venaca, Inc., alowenstein@venaca.com

Microsoft Excel has a function similar to Microsoft Word's Normal.dot. It is slightly hostile to setup, but here's the recipe. If you store the macro per the recipe (i.e., in Personal.xls), it will be available whenever you open Excel. Thanks Ollio for the macro.

Use the following procedure to create a Personal.xls file to store macros and settings, available for all workbooks when running Microsoft Excel.

1.	From Windows Explorer, go to:
	
	C:\Program Files\Microsoft Office\Office\Xlstart
	(The above path worked on some computers.)

	or alternatively, go to:

	C:\Documents and Settings\<user name>\Application Data\Microsoft\Excel\XLSTART
	(The above path worked on other computers, where <user name>)
	
2.	Right–click the right pane of Xlstart and choose 

	New > Text Document.
	
	Name your new document Personal.xls and press Enter. When prompted if you want to change the extension, click Yes.
	
3.	Double–click the newly created file to open it in Excel.

	Choose Window > Hide; then choose File > Exit. When prompted to save the Personal macro workbook, click Yes.

	The next time you run Excel, Personal.xls will run but remain hidden.

4.	To store your macros in Personal.xls, choose Window > Unhide to unhide the file, Personal.xls. 

	After you finish storing your macros, choose Window > Hide to once again hide the Personal.xls file.

prettytable depreciated

The Macro needs to be updated with to replace {{prettytable}} with class="wikitable" because {{prettytable}} has been depreciated.

Great macro btw.

Toastysoul - en.wikipedia.org

ok, see updated V15, Thanks for the hint ollio 23:59, 21. Apr. 2007 (CEST)

Additional support for hyperlinks

Hi Othmar

Really love this macro! Thanks. Have just one comment on hyperlinks - I have Excel spreadsheets with hyperlinks to network/shared drive documents (i.e. starting with \\) and email addresses (i.e. starting with mailto:). Maybe you have already added support for this? - but here is what i changed just quickly now (copying your code example):

    If Len(WorksheetFunction.Substitute(hyperlink, "http://", "")) <> Len(hyperlink) Then 'There may be a neater way to do this
        cellcontent = " [" & hyperlink & " " & cellcontent & "]" 'http link
    ElseIf Len(WorksheetFunction.Substitute(hyperlink, "mailto:", "")) <> Len(hyperlink) Then 'There may be a neater way to do this
        cellcontent = " [" & hyperlink & " " & cellcontent & "]" 'mailto: link
    ElseIf Len(WorksheetFunction.Substitute(hyperlink, "\\", "")) <> Len(hyperlink) Then
        'Look for shared drive links, with \\, add file:/// at the beginning, and replace all spaces with %20
        hyperlink = WorksheetFunction.Substitute(hyperlink, "\\", "file:///\\")
        hyperlink = WorksheetFunction.Substitute(hyperlink, " ", "%20")
        cellcontent = " [" & hyperlink & " " & cellcontent & "]" 'link to file on shared drive
    Else
        cellcontent = " [[" & hyperlink & "|" & cellcontent & "]]" 'assume that anything without http or \\ is a local wiki link
    End If

Cheers, Will

Version V17

No borders appearing on wikipage

Super macro Othmar

Quick question - have version 17 in use and after I generate the wikiouput and copy to the wiki (MedaiWiki) edit page, then save, my border lines are not visible even though the alignment is preserved correctly. Have not added fancy formatting to the borders and in fact removed them to see would the default border lines appear. Using Excel 2000

Update: tried it here in preview and it works - must be MediaWiki