Spreadsheet Automation


If you find yourself spending multiple hours at a time on repetitive tasks in Microsoft Excel, chances are these tasks can be automated by programming VBA code into your Excel document. Let us improve your Excel files with our VBA code.Below are two ways we've used VBA programming in recent projects.

Recent Project

Permit Recording and Reporting

Pull each permit associated with a project with the click of a button!

We built a solution for inputting, tracking, and reporting government-environmental permits for oil & gas projects. This solution features a user-friendly data entry method, an error-preventing project naming standard, and project reporting automation. Reporting what permits are associated with the project of interest was automated using visual basic for applications (VBA) in Excel. Building this solution in Excel allowed the user to continue interacting with a familiar interface. The automation applied in this project saves the user from hunting through the sheets to determine what types of permits are associated with what project. Now, the user can simply input a project name and output it's associated permits all while maintaining a separate sheet for each permit type.

Time Saved by Millstream Solution: 20 minutes/project
Key Words: Visual Basic for Applications (VBA), Data Validation, Microsoft Excel, Automation

Recent Project

Authorization for Expenditure (AFE) Generator

Store inputs and generate AFEs with the click of a button!

We built a solution in Excel to automate the drafting of AFEs for oil & gas projects. This solution gives the user a method for entering values for each well individually or entering values for a number of wells at the same time. Entering values individually is useful for wells with a unique pricing scheme. Entering values for a number of wells at the same time is useful for inputting a whole pad at once or if an item's price changes and the user needs to go back and redraft past AFEs. The file also saves all previous inputs to a 'database' sheet.

Once inputs are made, any number of wells can be chosen to generate the AFEs for. With the click of a button, the inputted price scheme values are taken and run through calculations in the background. Then, for each well, a new excel file is created with the calculated values in an AFE format. The calculated values are also saved to a PDF file in the AFE format. The VBA code finishes by opening a file explorer window for the user to view the generated Excel and PDF files.

This solution is especially helpful when a price changes. For example, if the snubbing unit day rate is reduced by $2000/day for the foreseeable future, we can save a lot of time on redrafting AFEs. Instead of going back to each AFE file individually to change the value and reprint with the new total, we can simply open our database sheet and change this value for each well by clicking and dragging. Then, to recalculate we just click the button and generate the new AFEs. This process could take as little as 5 minutes compared to as many as a couple days without our solution.

TL;DR: VBA code pulls inputs then calculates outputs generated into AFE format for an unlimited number of wells

Time Saved by Millstream Solution: 30 minutes/well
Key Words: Visual Basic for Applications (VBA), Microsoft Excel, Automation