Apache OpenOffice (AOO) Bugzilla – Issue 33021
Excel VBA import filter
Last modified: 2017-05-20 11:29:53 UTC
Do you have any idea to make instructions of importing VBA a little bit easier? Current instructions: 1 Loads a specified MS Excel file. 2 Saves it as "OpenOffice.org 1.0 Spreadsheet (.sxc)." 3 Close it. 4 Starts a helper Perl script to modify the sxc file. a Creates a temporary working directory in the directory /tmp. b Copies the sxc file to 'xxx.sxc.zip'. c Extracts contents of the file into the working directory with unzip command. d Removes an element named <table:named-expressions> from a file content.xml e Replaces "=(xxx)" with "=xxx()" in the file content.xml f Eliminates "Rem ", lines for "Sub" and "End Sub" statement from Basic/xxx/yyy.xml g Packs contents back to 'xxx.sxc.zip' with zip command. h Renames the original file 'xxx.sxc' to 'xxx.bak.sxc'. i Renames the modified file 'xxx.sxc.zip' to 'xxx.sxc'. j Cleans up and removes the temporary working directory. 5 Waits until termination of the Perl script. 6 Loads the modified file.
Created attachment 17153 [details] A sample OOo Basic program that imports Excel VBA macros
Created attachment 17154 [details] A Perl script that helps the OOo Basic importer
To try the sample OOo Basic program that will import Excal VBA macros, - Prepare a Unix base machine. The program will not work on Windows. - Download Excel_VBA_Importer.sxc - Download Excel_VBA_Importer.pl - Open Excel_VBA_Importer.sxc and follow the instructions in it. - Choose an Excel file that you want to import. - Click on 'Load' button. That's it. You would see a properly imported Excel document in the Calc.
Created attachment 17155 [details] A sample Excel document that includes custom functions in VBA and uses them in a sheet
Created attachment 17157 [details] A snapshot that depicts an original Excel, a just loaded, and a converted document with their macros
In order for the import filter to work, I had to manually remove the word "Private" from all "Private Sub" statements in Excel_VBA_Importer.sxc (I would get a "BASIC syntax error. Symbol expected" message with each). After importing, the converted file's macros also give me the same error on every "Private Sub" statement. My experience programming macros is next to nil, so the solution to this problem is probably very simple.
Sorry, but could you please explain what you want us to do here? There is a reason that the VBA code is commented out: it does not work in OOo, and OOo is an end user application, so we will not import any macro code that does not run.
Tora, regarding the VBA, look at Issue 27344. but it use different solutions. maybe you can help the sheet name problems. thanks
Hmm, leave this for me for a while. One thing that i cannot understand is that the names of user defined functions prepared in Excel seem not to be used in OOo 1.1.x. They are already declared as another identifier when the Excel file is imported to OOo. Why?
*** Issue 79107 has been marked as a duplicate of this issue. ***
I add michael meek at CC, I think he will interest in this
Thanks utomo for the link; this looks 'amazing' ;-) hopefully the built-in import [ available for ages on Linux, or Novell/OO.o/Win32 ] will be rather better. Noel ?
@ Michael: your welcome michael. @Noel I hope Noel will consider this for OOo 2.3 as the patch is there. @Tora, I see the OS is ALL, but at the Aug 16 09:03:28 +0000 2004 you say The program will not work on Windows. I hope it is for all OS. Thanks all.
Reset assigne to the default "issues@openoffice.apache.org".