What Have You Automated at Work with VBA, Python or Something Else?

ywpn on 24/01/2019 - 13:26

I was chatting to a friend who's kind of new to the workforce and started in a reporting role at a small/medium business - taking spreadsheets and making reports, graphs and charts, etc.
He said there are several people dedicated to doing the same report week-in, week-out.
From what he described, it sound like most of this report could be automated with either VBA, some advanced formulas, or even Python.

I was wondering what you peeps out there have automated?

Why don't more people invest in this?

Education & Work

Comments

  • 87percent on 24/01/2019 - 13:47
    +5

    there's no point telling your boss that you've automated work.. least I dont

    • trustnoone on 24/01/2019 - 14:51
      +5

      I used to laugh at this sort of advice because how could being helpful hurt, but I worked in this place once where we had to churn out written articles that were put on an internal site. It was so difficult for the manager to do that we only needed to do make two articles a week.

      Me trying to be helpful found a much easier way to upload the articles, what took them 2 days now took them about 20 minutes. After that we were all forced to write near 10 articles a week because it was so much easier to upload.

      With the added stress and workload, Never again.

      • Ninja9 on 24/01/2019 - 18:20

        Did you get a pay raise?

        • trustnoone on 25/01/2019 - 11:16

          Nah, but I did become the guy they went to for all IT related issues from then onwards as well. So it was really a lose lose haha

  • Quantumcat on 24/01/2019 - 14:00
    +4

    Part of a job I had involved assigning incoming incidents to team members so they didn't sit in limbo. I would download the list each morning and afternoon, paste it into another spreadsheet where one sheet counted how many incidents each person had assigned (just with a function), and I had a macro to go through all the assigned ones and assign them to team members so they all had a roughly equal number of assigned incidents, and generate URLs I could click to open each incident with the username filled in so all I needed to do was click save. If I didn't do that there would be a dozen plus clicks to assign each one not to mention reading and working out who had too many and not to assign to them etc.

    There were others I did in that job, usually organising complicated spreadsheets that the inbuilt function couldn't handle (can't remember what it is called now), where we would get the same kind of spreadsheet multiple times a day and have to organise it on the same - used to take my colleagues 30+ minutes per spreadsheet, afterwards took around 2 minutes.

    Another one I did, with auto hot key, was when I worked as an online tutor. Shifts would get released each Saturday and how many you got was determined how fast you could click and assign them to yourself. And sometimes people would unassign them during the week and you might get lucky enough to see that and grab it before someone else did. So I made a macro to have the calendar page open and place the mouse on each spot and check if it was green or red, click it if it was green, and assign it to me. I had every computer in the house on this page when shifts were released with the macro set to start at different days to maximise the likelihood of getting the most shifts possible. I would usually end up with more than full time amount of hours and could unassign at my leisure, and work exactly when I wanted to. When out of the house I would set my desktop to the calendar page to grab any shifts that people unassigned during the week within a few seconds of it happening.

  • SBOB on 24/01/2019 - 14:12
    +1

    all time time
    but then you bill your time as if you did it manually ;)

  • beeawwb on 24/01/2019 - 14:16
    +5

    Years ago, we used to have a reporting system that would reconcile daily data overnight and make it available the next morning. The problem was that in emergency situations there was no "live" data available. If you wanted to see how much data was coming in you had to wait until the next day to get that. Was less than ideal.

    There was 1 terminal system, with one command, that would give you live data. BUT… it would only give it to you 16 lines at a time, and you had to Pg Up / Pg Dn to go through each page, and it wouldn't tell you how many pages there was until you got to the last page and received a "Last Page" message.

    I wrote some VBA to control the terminal and rapidly page through (I actually had to put a delay in the code because it would run faster than the terminal could keep up) and copy all the live information in to Excel, 16 rows at a time, until it found the Last Page message. Could process thousands of lines in minutes, and I added summaries so that you could get multiple updates throughout the day (morning, afternoon, COB) and it would compare "current" vs "last run" so you could see the changes between each run.

    Gave it to the business, got lots of positive feedback and development of the program became a launching pad for the next stages of my career. I later spoke with a manager who dealt with emergencies, and it turns out they used to hire temps to manually do what my program was doing in a fraction of the time. Thankfully we moved on from that program and developed a proper reporting system.

  • fishball on 24/01/2019 - 15:15
    +1

    The first time I started this was because I didn't feel like manually doing something so I looked at automating/simplifying it. Pretty much made my 8 hour task into an 8 minute task while the VBA macro ran. I left the job soon after and got something better, never felt the need to pretend I was busy and getting free money out of it, that just makes me bored and I hate boredom.

    I've automated a bunch of reporting/ETL stuff using VBA/SQL and powershell/batch files over the years. Pretty standard stuff what you'd expect, grabbing excel files and reports then making new reports. Did this for a few years at various jobs and now I work in RPA/refactoring while involves more advanced automation in VBA. I use the windows API calls in VBA (eg. findwindow/sendmessage/postmessage/UIAutomation etc) to emulate things like Blue Prism/UIPath so that I can use Excel to control external programs (that aren't Microsoft based). I've also automated data extraction from mainframe systems, emails, websites and the like using VBA as well as sending emails, posting web forms etc.

    It's pretty interesting and fairly powerful stuff but not widely adopted. If you're interested, there's a sizable industry in Robotics Process Automation (RPA) which does this stuff. The reason we use VBA instead of Blue Prism etc is due to corporate limitations (can't install other stuff) and licensing costs associated with these RPA tools. If I had a choice I wouldn't be using VBA for anything beyond simple macros tbh.

  • spiff on 24/01/2019 - 15:48

    For those who have responded or anyone else who's familiar, what are the best resources to learn VBA? I've got a ton of Udemy courses which I struggle to find the motivation to complete. But online courses are better than books I assume?

    • Diji1 on 24/01/2019 - 16:11
      -4

      Nope. Learning online is a terrible way to learn things.

      • spiff on 24/01/2019 - 16:11

        So… what do you suggest?

        • Domingo on 24/01/2019 - 16:44
          +8

          Diji1 doesn't provide suggestions, just throws stones.

    • fishball on 24/01/2019 - 16:31
      +2

      Online courses can work, you just really need to learn by doing though.

      Once you've done some basic learning you can just set yourself a little project to do. Could be anything; something simple like copying a sheet to a new sheet or new workbook.

      If you run into something you can't do, google it. It might sound stupid but being able to understand the code snippets posted on stackoverflow and converting it into something you can use is actually useful for learning.

    • beeawwb on 24/01/2019 - 16:36
      +2

      15 years at this company, 10+ doing similar roles, and I'm entirely self taught. Well, not entirely, I did computing classes in year 10/11/12 and a Computer Science at Uni (which I didn't finish) which I've drawn on.

      I can't say I've done any formal courses either (or at least none I've found useful), it's all just been Google when I need to figure something out. That said, I've found Chip Pearson to be a huge asset for Excel and VBA, and I have multiple bookmarks for Everything Access for Access VBA. There's another site I'm trying to think of which had lots of VBA examples that I used to use, but I can't find it at the moment. I'll update this if I do.

  • Ninja9 on 24/01/2019 - 18:10
    +1

    I automate my emails, reports and lot of data manipulation and formatting. Anything i can automate i automate.

    • ywpn on 25/01/2019 - 11:00

      Automate sending out emails?

      • Ninja9 on 25/01/2019 - 20:06

        Sorting incoming emails, sending out emails and creating folder structure etc

  • poboy on 24/01/2019 - 19:12
    +1

    Yes. Taught myself python over the last 12 months. I consider myself pretty good at Excel, but Python just takes you to the next level.

    The ability to work across multiple files, allow for errors and consolidate into new files is a game changer.

    I have written scripts to create customised promotional PDFs, customised customer reports and business dashboards. I have saved myself a month of work each month.

    Love it. Interested? Look up automate the boring stuff as a 101 intro.

  • Hazzard on 24/01/2019 - 20:12
    +2

    Dilapidation reports using VBA in word.

    VBA code automatically inserts heaps of pictures into a word document. When I start the macro, it asks for folder location, picks images from the location, asks for picture height (width determined automatically from aspect ratio) and figure caption (All images normally have the same caption say "Figure 1 - Unit 5" in our case with only the figure number incrementing. It also gives me the option to enter the file name as the caption incase the images are not specific).

    All the images are sorted alphabetically according to file name (unfortunately numbers are sorted alphabetically too), compressed to 220ppi by the macro and inserted accordingly. Sorting is required if the images are obtained from a folder on a server.

  • [Deactivated] on 25/01/2019 - 02:50

    VBA, "Advanced Formulas" and Python to make report in Excel?

    Just get Power BI Desktop, it's free.
    So many office workers are hugely incompetent at the even the most basic functions of their job.

  • Euphemistic on 30/01/2019 - 06:59

    Why wouldn’t you automate stuff if you can? Leaves more time to ozbargain. Not to mention that a lot of reports etc are just pointless busy work that someone wanted a few years ago and you just had to keep doing while no one looks at them. Pointless beuracracy.

    I’ve simplified plenty of work practices over the years, including spreadsheet macros etc. it makes sense to spend a few hours to save that time in the future.

Login or Join to leave a comment
Login Join