r/excel • • 8d ago

Waiting on OP Python vs Excel/VBA for an automated document generator used by non-technical staff?

Hello everyone

Let me give you a little context I am currently working in a french hospital, and I would like to develop a tool to automate the creation of administrative letters, if you don't know french public administrations are VERY VERY VERY outdated i am working here as a buying manager and as expected from a place like this we lack staff a lot because of budget cuts so i am most of the time doing administrative task like writing letters instead of studying prices and doing negotiations.

We regularly receive supplier quotations and need to generate different types of formal boring documents ( to be honest we could just sign the pricing proposal but the Head of Department is from the medical field have no training in administration and she is very old so she wants to keep it that way ) so we write letters for things such as price acceptance letters, price revision and adjustment letters, product reference changes, product substitutions, currently, much of this work is done manually using Word.

Right now I have a dropdown to choose which type of letter I want, some fields for the supplier, contract number, product references, prices, dates etc. I also added another sheet where I can select which hospitals are concerned, and a small supplier database.

But I' am not really happy with it.

For example, if I choose a letter about changing a product reference, I still see fields asking for old prices, new prices and other stuff that has nothing to do with that letter

What I would really like is for the form to change depending on which letter I select. So only the information I actually need would appear. And then once everything is filled in, I just click a button and it generates the Word letter using our existing templates. Being able to export it to PDF would be nice too.

Now here's where I'm a bit lost.

Should I keep working on Excel and use VBA to make it better? Or should I just start over with Python and make a proper little application?

I am not a developer btw, I'm just interested in this stuff and trying to learn as I go, so for those of you who have more experience with this kind of thing, what would you do?

Would you stick with Excel/VBA or go for Python? And if Python is better, how would you make something that people can use without having to install Python themselves?

One important detail this is a hospital environment, so computers have IT restrictions, and most users aren't particularly good with computers. my goal is to eventually present a reliable, easy to use solution to my Head of Department.

Also how complicated would it be to make different forms for each letter type without having to code everything separately?

Sorry if these are stupid questions I am still figuring things out haha.

Any advice or feedback would really help!

so i would love your advice on

  1. Is Excel/VBA sufficient for this kind of application?

  2. Would Python be a better long-term solution?

  3. What GUI framework would you recommend for non-technical Windows users?

  4. How would you handle different document types with their own required fields?

  5. How difficult would deployment and maintenance be in a restricted corporate environment?

  6. I'm still learning, so any feedback on the architecture, user experience, or technical approach would be greatly appreciated.

Thanks :))

( sorry for my english I am trying to not use ia )

24 Upvotes

21 comments sorted by

•

u/AutoModerator 8d ago

/u/conn-ie - Your post was submitted successfully.

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

27

u/Gringobandito 8 8d ago

You want to create templates in Word and then you a mail merge from Excel to fill in the relevant fields. Excel is not for writing letters.

6

u/justwileyenough 8d ago

This is the way. Python would create unnecessary hassles.

6

u/duranimal9 4 8d ago

VBA as long as IT allows macro enabled workbooks. I am working on something similar for various reports. You could setup a template for each different letter with placeholders, then have input fields in excel and the VBA would replace all of the placeholders with the specific information and save as word or pdf.

10

u/Oprah-Wegovy 1 8d ago

VBA because it’s native to Excel. You need to install a Python interpreter on any machine that needs to use what you want and you can’t guarantee that will happen. Anyone running Excel can use your VBA script.

4

u/bradland 279 8d ago

I've been working in technology, specifically developing websites and web applications since the late 1990s. Absolutely do not go rogue and write your own application. Use Excel + VBA.

Depending on the organization, if they catch you developing applications on your own, you can face reprimand for violation of security policies. Especially in a hospital environment. I know it probably sounds like hypocrisy, but writing an "application" using Excel + VBA is typically okay because Excel is an approved platform. Although, some organizations disallow VBA, which is its own challenge.

If the system already uses Excel + VBA, stick with that. If you think that there is opportunity in building a Python application, then you should explore that with your managers before writing any code.

If you do end up authoring an application, it's not going to go like you expect. There's a lot more to app development than simply writing code. The company ends up owning the intellectual property, including the code. They won't want you to write the app in just any language/framework. They'll want you to use approved tools that align with their broader technology environment.

Think of it like owning a fleet of cargo trucks. If the entire fleet is Renault, and you go out and buy a Volvo, the shop that maintains the trucks now has to buy all the software, special tooling, an parts inventory that is specific to Volvo. The fleet manager is going to be pretty pissed off.

It's much easier to swim in your existing lane and just use Excel + VBA.

2

u/Acceptable-Sense4601 2 8d ago

Truth. Went through this myself. To learn react/typescript/flash/etc i wrote an application that our security people said is fine to host from my desktop given there’s no external api calls and there is RBAC using Active Directory, but to fully deploy it as a legit app, id have to have someone else be familiar with the code in case i leave, interface with all the right teams on my own for getting it deployed with SSO, graph API, Linux server deployment, and so on. I just gave up trying to go all out with it.

2

u/pdf_to_excel_nerd 8d ago

Excel + VBA is genuinely enough for this, and you already have the data layer in place. For the "only show relevant fields" part, the cleanest approach is a VBA UserForm with a dropdown for letter type, and then show or hide the text boxes based on what they pick. One Word template per letter type saved in a known folder, and the macro opens the right template, finds the placeholders, fills them from the form values, saves as PDF. No Python install needed on the other machines, no extra software to manage. The UserForm is also way friendlier for non-technical users than a separate app or command line. For the different letter types you do not need to code them separately: one macro, one UserForm, just a Select Case on the dropdown value to pick which template to open and which fields to show. Happy to go more detailed on any of the pieces if it helps.

1

u/Oleoay 1 8d ago

In terms of a GUI, just use the excel workbook itself. Add an Instructions tab with the step by step process and your dropdown menus for users to interact with. Have users click buttons tied to VBA macros for each step.

1

u/bobo5195 8d ago

I do alot of this random advice.

need tools people understand and have access to when you are gone. Python with no one writing python is bad - at a software house with programmers it works. What you are doing is Excel with mail merge, or I think is even just a word form. Stick to something users know.

You need to work out tools and what programs you have access to. A folder of files is fine for now as an example. This might need a lot of search but been there before that all you need is to know the name of tool to go searching. Ask an LLM is against the rule but may help you here.

Off the top of my head.

  1. Word has a template option for this. I found it clunky it may work better. It is just a bunch of fields you open the template.

  2. If you want a drop down list i have always used sharepoint. Can create some drop downs enter the files. I say that as there is requirment here. Draft an invoice is great but you probably want a record of invoice X was created by X with this info on this date.

  3. LLMs are really good at this stuff there is a way to setup with agents. At home this is probably a prompt a few minutes. BUT i know what it is like to work in a lock down enviroment and for these letters you want a paper trail.

Question answers

  1. Yes VBA is maybe overkill i personally avoid

  2. No, people dont know python

  3. GUI framework is the tools. What people know which is office.

  4. I would have different documents - click on invoice gibe it

  5. A folder of stuff. You need to work out features vs how many people know this train someone.

  6. Get some example users both good and bad. Try and develop agile bit at a time.

1

u/0x80070002 8d ago

VBA because it has all the files in one file

1

u/Hg00000 15 8d ago

To answer your questions:

  1. VBA is most of Visual Basic. You can build nearly any kind of program in VBA. It is an old language, so it can be more difficult to do some tasks in VBA than it would be in a more modern language like Python.

  2. Developing and distributing a Python program will be very different compared to distributing an Excel/VBA application. "Better" is a difficult term to evaluate. The real question is how are you going to distribute a new release when its available? (This is an argument for a web application, since someone can point their browser at your page and automatically get the latest version of your program.)

  3. You should ask that on r/Python.

  4. To prototype this, I'd look at having an Excel file for data entry with one file per page. Then put a button on that page that opens the correct Word template and puts the data in there. Once you have that working for a few different cases, then move on to the bigger application.

  5. Very. Deployment is hard, and users don't like clicking the "update" button.

  6. Keep it simple. Try to get all the data for one letter on one page. If you can't go back and look at it again. Throwing together a quick prototype for yourself is easy, but making a polished piece of software that users aren't going to break is hard.

1

u/InfoMsAccessNL 1 7d ago

In Access with vba you can build a user friendly ui what looks like a complete application. An Access developer can make this for under 700,-

1

u/mystique0712 7d ago

Python with a simple web framework like Flask or FastAPI would be a better long-term solution for creating a dynamic form and generating documents.

You can package it as an executable using PyInstaller for easier deployment on restricted systems.

For handling different document types, use conditional logic in your Python code to display fields based on the selected document type.

1

u/Ok_Negotiation7811 7d ago

Excel vba is the way to go 🥳

1

u/PeruseAndSnooze 7d ago

Let me know if you would like help ( I am a data engineer with a decade of experience). Echoing here use VBA

1

u/halwahahn 8d ago

I use MS access for these purposes