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

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)