Timeline for Can I really make python work with VBA
Current License: CC BY-SA 4.0
26 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Jun 11, 2018 at 21:45 | history | edited | Robert Harvey | CC BY-SA 4.0 |
deleted 9 characters in body
|
| Jun 11, 2018 at 20:39 | answer | added | S Meaden | timeline score: 1 | |
| May 23, 2017 at 12:40 | history | edited | CommunityBot |
replaced http://stackoverflow.com/ with https://stackoverflow.com/
|
|
| Aug 29, 2016 at 20:42 | history | edited | BuckTurgidson | CC BY-SA 3.0 |
details one the need for a VBA layer
|
| Aug 29, 2016 at 20:28 | comment | added | BuckTurgidson | @jpmc26. Most of time users read and 'click to show details' on the data. Sometimes they can enter data and push a push a button edit/saves/send data. | |
| Aug 29, 2016 at 20:23 | vote | accept | BuckTurgidson | ||
| Aug 29, 2016 at 18:51 | comment | added | JeffO | You can try something to create the excel files for the users like: openpyxl.readthedocs.io/en/default/#. | |
| Aug 29, 2016 at 17:34 | comment | added | Doc Brown | @jpmc26: I guess at the time when VBA came up, the decision did not look so bad as it might look today. And the deep integration of VBA into Excel has still its value (for example, I often use the macro recorder to find out how to do some things programmatically). I have written lots of VBA programs with embedded sheets as templates in the past, which can be done directly in Excel with almost no overhead. A similar solution for an addin needs always some more programming effort (I did this, too, so I know what I am talking about). | |
| Aug 29, 2016 at 16:56 | comment | added | jpmc26 | @DocBrown Sensible. It completely defeats the purpose of even having the ability to embed them, though, which just goes to show it was a bad design decision. lol. | |
| Aug 29, 2016 at 14:27 | comment | added | Doc Brown | @jpmc26: ok, now I got you. One can perfectly write VBA programs as part of one Excel document, and keep the user's data in a second document. The VBA macro then can create reports as a third document. All that is pretty simple, one has just to care for this separation, no magic involved. This is mandatory if you ever want to be able to deploy a version 2.0 after version 1.0. Actually this is what I always told people in the first 30 minutes of my introductory VBA lessons ;-) | |
| Aug 29, 2016 at 14:01 | comment | added | jpmc26 | @BuckTurgidson I have a clarification I'd like to ask for. Are your users entering data in the Excels you give them, or could you just generate an Excel document as output? | |
| Aug 29, 2016 at 13:54 | comment | added | jpmc26 | @DocBrown "if the deployment of new Excel VBA programs is simple or not depends heavily on if there is a clear separation between the program and user's data ... I guess the OP has made this separation ..." I was trying to tell you why I didn't consider this a possibility. As far as I'm aware, VBA code must be embedded in the user's data file. This is even what the OP describes doing in the question ("save a new workbook with a new version name"). So I think your assumption is unwarranted; I don't know how the OP is overcoming this difficulty. | |
| Aug 29, 2016 at 13:47 | comment | added | Doc Brown | @jpmc26: not sure I understand what you are trying to tell me. What do you mean by "I didn't think this was possible with VBA" - I did not suggest anything which might be possible "with VBA". My suggestion is more a full replacement for VBA code. However, Excel-DNA will also allow to write .NET functions which can be called from VBA, but I am under the impression that is not what OP is really after. For Access, however, I don't know any comparable solution. | |
| Aug 29, 2016 at 13:40 | comment | added | jpmc26 | @DocBrown Of course, but I didn't think this was possible with VBA. I even have an existing question about doing that with Access that never received a real answer. | |
| Aug 29, 2016 at 11:44 | comment | added | Brandin | "Any words on the risks I'm taking" - develop a demo/proof-of-concept first and try to deploy that to a beta tester customer. If he has trouble due to admin rights, DLLs, Excel version, etc, then you'll know before you spend any more time on it. | |
| Aug 29, 2016 at 11:30 | answer | added | Doc Brown | timeline score: 4 | |
| Aug 29, 2016 at 7:46 | comment | added | BuckTurgidson | @Doc Brow. Python is mostly because I'm used to and because I really care for quick development. It feels now that steeping into VB.NET first is the natural path. | |
| Aug 29, 2016 at 6:14 | comment | added | Doc Brown | @BuckTurgidson: then Excel-DNA is probably what you are looking for. It might be easier, however, to start with a VB.NET add-in before you integrate IronPython into this. | |
| Aug 29, 2016 at 6:10 | comment | added | BuckTurgidson | @DocBrown. Want is to use the python-built object in VBA. Just printing the excel file with the data would mean give up on-click events. COM is not a solution because of the admin rights. Idea now is excel self loaded add-in. I need the self loaded mostly while in development. | |
| Aug 29, 2016 at 6:09 | comment | added | Doc Brown | As an alternative which works seemlessly with Excel, does not need any messing around with the registry, and can be used for making single-file deployments: you might consider to use Excel-DNA for creating an Excel-Addin, and combine this with IronPython, if it really must be Python. I used this only with VB.NET and C# by myself, however. | |
| Aug 29, 2016 at 5:59 | comment | added | Doc Brown | @jpmc26: if the deployment of new Excel VBA programs is simple or not depends heavily on if there is a clear separation between the program and user's data, so the first one can be updated without overwriting the second. I guess the OP has made this separation, otherwise he would already be in big trouble. | |
| Aug 29, 2016 at 5:55 | comment | added | Doc Brown |
Deploying a compiled executable or a python script is definitely as simple as deploying an Excel document, once the target machine contains the required run time environment. However, it seems you want to use Python from VBA by COM??? Is that really necessary? That is what it makes harder in your case. Can't you just run your Python script in a separate process by utilizing VBA's Shell command and implement the I/O through files?
|
|
| Aug 29, 2016 at 5:31 | comment | added | BuckTurgidson | @jpmc26. Indeed this statement cannot be generalized. It's true in my case. | |
| Aug 29, 2016 at 1:31 | comment | added | jpmc26 | "how amazingly easy is to deploy a new version in excel" It might be easy to save a new version, but how hard is it to make sure everyone keeps up to date? You'll see plenty of horror stories about trying to manage who is on what version. | |
| Aug 29, 2016 at 0:19 | review | First posts | |||
| Sep 28, 2016 at 0:20 | |||||
| Aug 29, 2016 at 0:18 | history | asked | BuckTurgidson | CC BY-SA 3.0 |