Feature to add VBA functions to a workbook

[David Michael Pennington]

I am curious whether someone has done research on how difficult it
would be to write VBA code directly with openpyxl.

I have a challenging problem that is described in this post on
SuperUser:
http://superuser.com/questions/367201/how-can-i-count-unique-comma-seperated-values-in-excel-2010

The nature of the problem is difficult to solve with excel formulas,
but rather easy to solve if you can define your own user functions in
VBA (as suggested in Chris Neilsen's answer).

If someone has thoughts on how we could add VBA user defined formulas
into openpyxl, I would value your input on how we could make it
happen.

Cheers,

[Eric]

Hi David,
I guess you have good reasons not to make the calculation in python and just write the result in another cell :)

To be honest,I never tried to put vba in an openpyxl workbook,and I can't estimate the cost of adding such a feature for now,but I assume the effort would be quite big :/

If you need a quick solution for your problem,you'd better go with the insane Excel formula as answered on SU :) if you have more time in your hands,then you can file a ticket on the bugtracker and I can make some research on the topic,but I have many critical stuff to fix before :(

Cheers,
Eric

[David Michael Pennington]

On Dec 14, 1:54 am, Eric <eric.gaz...@gmail.com> wrote:
> Hi David,
> I guess you have good reasons not to make the calculation in python and
> just write the result in another cell :)
>

Correct, other people will consume this spreadsheet and want to modify
it themselves after it has been "compiled" from multiple sources. If
I must compile a new spreadsheet every time someone wants to modify a
cell, then that's not going to work in anything other than a small
office

> To be honest,I never tried to put vba in an openpyxl workbook,and I can't
> estimate the cost of adding such a feature for now,but I assume the effort
> would be quite big :/
>
> If you need a quick solution for your problem,you'd better go with the
> insane Excel formula as answered on SU :) if you have more time in your
> hands,then you can file a ticket on the bugtracker and I can make some
> research on the topic,but I have many critical stuff to fix before :(
>

Well I was hoping to contribute some code, even if in a private branch
assuming someone understood the scope of the problem. I could use the
formulas, but that solution is only marginally acceptable in the
situation that someone adds another value in the comma-separated list.

[Eric]

Hi David,

found this in the PHPExcel forums: http://phpexcel.codeplex.com/discussions/208399

Apparently, one thing you could do if you were really willing to take this path would be:

• start with an empty workbook with the VBA function already in
• write your workbook with formulas in openpyxl, callling the VBA function (which is not yet in there)
• still while in Python, take the vbaProject.bin file from your empty workbook, and copy it into your new workbook in the same location
• maybe fiddle a bit with the .ref files to add relations to your vbaProject.bin file

Apparently, VBA is compiled in Excel in a proprietary binary format, and is not part of the OOXML specification (assumptions based on http://techrights.org/2007/01/11/proprietary-open-xml-extensions-already/, have not checked the specs to confirm, but this seems highly probable).

Hope it will get you started, let me know if you need more help ;-)

Cheers,

Eric

2011/12/14 David Michael Pennington <penning...@gmail.com>

[filipeds...@gmail.com]

Hi David,

Sorry for the thread digging, but I am facing a similar problem.

I have written a python script which will update data in a xlsm file using openpyxl. This only changes cell contents and does nothing more.

Then I have created some vba code (in the sheet changed by the python script) to help the user filter data. When I re-run the python script, the vba code is all gone.

Did you managed to get the VBA to work while using the openpyxl?

Cheers,

Filipe

[John Bovey]

Hi Filipe,

I has responsible for the vba support in openpyxl. Did you call
load_workbook() with keep_vba=True?

John

[filipeds...@gmail.com]

Hi John,

Thank you for the quick reply.

Yes, see below a description of the actions I perform:

dst_wb=openpyxl.load_workbook(dest_filename, read_only=False, keep_vba=True)

dst_ws=dst_wb.get_sheet_by_name(dest_sheet)

    

    (Then I do some data searching in a origin file, different than the destination file)

    line = some data which will be written

    dst_ws.append(line)

 dst_wb.save(dest_filename)

I am somewhat new to python and because of it, might be missing something :/

Cheers,

Filipe

[filipeds...@gmail.com]

John,

I am not sure it is relevant, but I did not create the excel book via python. I just opened excel and saved as *.xlsm.

Cheers,

Filipe

[John Bovey]

On Thu, 27 Aug 2015 07:42:37 -0700 (PDT)
filipeds...@gmail.com wrote:

> I am not sure it is relevant, but I did not create the excel book via
> python. I just opened excel and saved as *.xlsm.

That is normally the case.

John

[John Bovey]

Hi Filipe (and Charlie),

Thanks for sending me the files, including the zipped archive of
your openpyxl. I am replying to you through openpyxl-users in case
Charlie can shed some light on the problem.

It looks as though you have an old version of openpyxl. When I run
your script with the version of openpyxl from branch 2.3 in the
repository on bitbucket, it works fine and the vba is still there in
the saved file. When I run it using the copy of openpyxl that you
sent it does not work. The vba code is actually still there in the
output file but excel does not recognise it. I don't have any way
to determine exactly what version of openpyxl you are using, though,
and I don't know what is the latest version available through pip.

Charlie,

Possibly the problem is in the workbook.xml.rels file. In Filipe's
version of openpyxl the type for target vbaProject is:
http://schemas.openxmlformats.org/officeDocument/2006/relationships/vbaProject
In the current branch 2.3 it is
http://schemas.microsoft.com/office/2006/relationships/vbaProject

John

[Charlie Clark]

Am .08.2015, 22:25 Uhr, schrieb John Bovey <J.D....@kent.ac.uk>:

> Possibly the problem is in the workbook.xml.rels file. In Filipe's
> version of openpyxl the type for target vbaProject is:
> http://schemas.openxmlformats.org/officeDocument/2006/relationships/vbaProject
> In the current branch 2.3 it is
> http://schemas.microsoft.com/office/2006/relationships/vbaProject

That's certainly possible. I did have to make some changes around that
when moving the code to the more declarative form. However, I can't debug
what I don't have.

Filipe - replying to an e-mail from 2011 without consulting the
documentation which explains how VBA is supported was not very helpful. If
you think there is a bug in the library then please submit a bug report
detailing which version you are using and providing a sample file.

Charlie
--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Kronenstr. 27a
Düsseldorf
D- 40217
Tel: +49-211-600-3657
Mobile: +49-178-782-6226

[John Bovey]

Filipe's code is ok, as is the latest version of the openpyxl code. He just needs to get the latest version. I just was not sure of the best way for him to do that.
John

On 29 August 2015 12:43:40 BST, Charlie Clark <charli...@clark-consulting.eu> wrote:

Am .08.2015, 22:25 Uhr, schrieb John Bovey <J.D....@kent.ac.uk>:

 Possibly the problem is in the workbook.xml.rels file. In Filipe's
 version of openpyxl the type for target vbaProject is:
 http://schemas.openxmlformats.org/officeDocument/2006/relationships/vbaProject
 In the current branch 2.3 it is
 http://schemas.microsoft.com/office/2006/relationships/vbaProject

That's certainly possible. I did have to make some changes around that  
when moving the code to the more declarative form. However, I can't debug  
what I don't have.

Filipe - replying to an e-mail from 2011 without consulting the  
documentation which explains how VBA is supported was not very helpful. If  
you think there is a bug in the library then please submit a bug report  
detailing which version you are using and providing a sample file.

Charlie

--

Sent from my Android phone.

[Charlie Clark]

Am .08.2015, 13:59 Uhr, schrieb John Bovey <J.D....@kent.ac.uk>:

> Filipe's code is ok, as is the latest version of the openpyxl code. He
> just needs to get the latest version. I just was not sure of the best
> way for him to do that.

You mean there is a bug in the 2.3-b1 ? and/or 2.2.5 ?

You can always use a branch checkout with pip:

bin/pip install -e
hg+https://bitbucket.org/openpyxl/openpyxl@2.3#egg=openpyxl

2.3-b2 and 2.2.6 are scheduled for release as soon as Eric has the time.

[John Bovey]

I am not sure which version Filipe is using, he though it was 2.3. The most recent 2.3 on bitbucket is ok, is that the same as 2.3-b1?
John

On 29 August 2015 13:02:44 BST, Charlie Clark <charli...@clark-consulting.eu> wrote:

Am .08.2015, 13:59 Uhr, schrieb John Bovey <J.D....@kent.ac.uk>:

 Filipe's code is ok, as is the latest version of the openpyxl code. He  
 just needs to get the latest version. I just was not sure of the best  
 way for him to do that.

You mean there is a bug in the 2.3-b1 ? and/or 2.2.5 ?

You can always use a branch checkout with pip:

bin/pip install -e  
hg+https://bitbucket.org/openpyxl/openpyxl@2.3#egg=openpyxl

2.3-b2 and 2.2.6 are scheduled for release as soon as Eric has the time.

Charlie

--

[Charlie Clark]

Am .08.2015, 14:07 Uhr, schrieb John Bovey <J.D....@kent.ac.uk>:

> I am not sure which version Filipe is using, he though it was 2.3. The
> most recent 2.3 on bitbucket is ok, is that the same as 2.3-b1?

No. That was released at the end of June. The changelog list what's
happened since then.

Looks like this is the relevant change:
https://bitbucket.org/openpyxl/openpyxl/diff/openpyxl/writer/workbook.py?diff2=c0033cf0ab44&at=2.3

[John Bovey]

On Sat, 29 Aug 2015 14:13:41 +0200
"Charlie Clark" <charli...@clark-consulting.eu> wrote:

> Am .08.2015, 14:07 Uhr, schrieb John Bovey <J.D....@kent.ac.uk>:
>
> > I am not sure which version Filipe is using, he though it was 2.3. The
> > most recent 2.3 on bitbucket is ok, is that the same as 2.3-b1?
>
> No. That was released at the end of June. The changelog list what's
> happened since then.
>
> Looks like this is the relevant change:
> https://bitbucket.org/openpyxl/openpyxl/diff/openpyxl/writer/workbook.py?diff2=c0033cf0ab44&at=2.3
>
> Charlie

Yes, I agree.

John

[filipeds...@gmail.com]

Hi Charlie,

The version I was using was the 2.3.0.b, that I got from here: https://pypi.python.org/pypi/openpyxl/2.3.0-b1

I did not install it through pip, but just placed the openpyxl folder in the same directory as my script and then imported the module. I honestly thought I was using the latest version. My bad :/

I tried my script just now, after downloading the 2.3 version available on bitbucket as John suggested, and everything seems to be working.

Thank you for your support.