Excel and Office Automation – References

Portfolio references in which the work gets done where people already work — in Excel, Google Sheets, PowerPoint and Outlook: a spreadsheet calls an API and writes the responses, a price list is rearranged in one run, data is pulled from databases over ODBC connections, PowerPoint replaces images, and Outlook saves attachments and tracks mail on its own. Instead of a new program to learn, the user gets a button or a script in the tool they use every day. I do this kind of work as part of my Office add-in development service.

An Excel add-in that sends IDs to an API and writes back the results

Built in the third quarter of 2021.

Problem

The client had a list of IDs in Excel — at most around fifty rows, usually five or six — for which an API had to be called and the results returned to the same sheet. There was a script that extracted the data as JSON, but the values were typed into PostMan by hand. The client asked for a simple solution that does the whole process on its own: reads the IDs, calls the API and writes the responses, without installing extra software. They suggested cURL, but were open to other approaches.

Solution

A VSTO add-in for Excel was built, with a ribbon button, that runs the whole flow from the spreadsheet itself:

  • reading the IDs: the add-in reliably takes the list of IDs from the sheet;
  • automatic API calls: it goes through every ID, calls the API and collects the results;
  • writing to the same file: the data returned by the API is written back into the original Excel sheet;
  • ease of use: everything is started from Excel, without manual entry and without intermediate tools.

Technologies used

  • VSTO (Visual Studio Tools for Office): an Excel add-in with a ribbon button.
  • VB.NET and .NET Framework: the add-in's language and environment.
  • JSON: the API response format.

Result

Instead of typing into PostMan, the user gets the API responses for the whole list with one click in Excel, written into the same sheet.

Rearranging a price list in Excel with VBA

Built in the second quarter of 2021.

Problem

The client had an extensive price list with quantities arranged vertically, each in its own row. Some items had several prices depending on quantity, others only one. The client's partner wanted a horizontal layout, with a separate column for each quantity. Since there was no consistent pattern across items, it was clear that rearranging by hand was out of the question and a dynamic approach was needed.

Solution

An automated VBA process was built that reformats the price list:

  • dynamic formatting: the script recognizes a varying number of prices per item and adjusts the layout accordingly;
  • horizontal layout: each quantity gets its own column, as the partner requested;
  • flexibility: the solution works both with items that have one price and with those that have several.

Technologies used

  • Microsoft Excel: the platform.
  • VBA: automating the rearrangement.

Result

The price list is rearranged into the layout the partner wants with a single run of the script, no matter how many prices an item has.

An Excel sheet that pulls data and highlights changes

Built in the second quarter of 2021.

Problem

The client needed an Excel sheet that pulls product label data from a data sheet based on the item number. The data had to be copied, not just referenced as with VLOOKUP, because it had to be edited on the spot. Every change had to be highlighted in colour so changes could be tracked easily, and the data sheet had to be hidden so the end user sees only the main sheet.

Solution

  • direct data retrieval: data is copied from the data sheet, so it can be edited in the main sheet without relying on VLOOKUP;
  • change tracking: every edit of the retrieved data automatically changes the cell colour;
  • hidden data sheet: the user works in a clean main sheet.

Technologies used

  • Microsoft Excel: Excel's advanced features for data retrieval and interactivity.

Result

Labels are filled in by entering the item number, data is edited on the spot, and every change is immediately visible by its colour.

VBA management of ODBC connections in Excel

Built in the fourth quarter of 2020.

Problem

The client needed a short, clear VBA solution for managing ODBC connections in the active workbook: remove all existing connections, set up ten new ones in a loop from connection strings stored on one sheet, pull data through them and fill ten new sheets with it. The loop was a requirement, so the number of connections could easily be increased later.

Solution

  • ODBC connection management: existing connections are removed and re-established as needed;
  • connections in a loop: the number of connections can grow without changing the logic;
  • data retrieval and organization: data is fetched and structured before being written;
  • filling sheets: new sheets are created and filled with data automatically.

Technologies used

  • Microsoft Excel: the platform.
  • VBA: automating connections and data entry.
  • ODBC: connecting to data sources.

Result

The workbook connects to all sources and fills with data in a single run, and the work was delivered on time.

Client feedback

★★★★★5/5

“Dejan showcased exceptional proficiency and diligence, delivering everything on time and with precision. Thanks, Dejan!”

Google Apps Script for searching and copying data between sheets

Built in the first quarter of 2022.

Problem

In one Google Sheets document the client had three sheets. The first was the main table, with fixed columns and between 2,500 and 3,500 rows that the client added to by hand. The second held data to be found in the first and was added to every day, with different columns. The third had to be filled with data from the first sheet, according to what was searched for in the second. This work was repeated every day.

Solution

A Google Apps Script was built that searches and copies data between sheets:

  • dynamic search: data from the second sheet is found in the larger first sheet;
  • copying results: the matching data from the first sheet is transferred to the third;
  • adapting to the number of rows: the script works regardless of how many rows the sheets have and keeps the data consistent.

Technologies used

  • Google Apps Script: automating search and copying in Google Sheets.
  • Google Sheets: the spreadsheets the data is kept in.

Result

A significant part of the client's daily work is done by the script, and the client rated the outcome as perfect.

Client feedback

★★★★★5/5

“Everything went well. Actually, it was perfect. I highly recommend Dejan.”

A PowerPoint add-in for replacing images

Built in the third quarter of 2023.

Problem

PowerPoint users needed a way to replace an image in a presentation so that the new image keeps the position of the old one, with the choice of keeping or changing the image's proportions. The solution could not be a macro passed from file to file, but an add-in that is easy to distribute and install, so an installer was needed along with it.

Solution

A VSTO add-in for PowerPoint was built together with an installer:

  • replacing images while keeping their position: the new image lands exactly where the old one was;
  • proportions by choice: the image's aspect ratio can be kept or changed;
  • easy to share among users: the add-in is built to be simple to download and install;
  • always available: once installed, the add-in is there every time PowerPoint opens;
  • ribbon buttons: the commands sit in the PowerPoint ribbon, built with the visual designer.

Technologies used

Result

Users replace images in presentations with one click, without repositioning them, and the add-in is installed the same way for each of them.

Client feedback

★★★★★5/5

“Great communication, perfect delivery.”

An Outlook script that saves attachments and inserts links

Built in the second quarter of 2021.

Problem

The client used Outlook calendars heavily for tracking jobs and reporting. Users updated job status through forms and dragged various files — PDFs, images, Word documents, messages — into the calendar form. Inserting files directly caused problems, so dragged files had to be saved automatically to a network drive, leaving a link to the saved file in the form instead.

Solution

A VBA script for Outlook was built that takes care of files in calendar forms:

  • file detection: the script notices when a user drags a file into a calendar form;
  • saving files: the file is automatically saved to a predefined network drive;
  • a hyperlink instead of the file: the inserted file in the form is replaced with a link to the saved file;
  • folder organization: folders are created by set criteria, so files are easy to find.

Technologies used

  • VBA for Outlook: automation within Outlook itself.
  • Network drive: where the files are stored.

Result

Calendar forms stay light, without embedded files, and all attachments are neatly saved on the network drive and reachable through a link.

An Outlook script for tracking incoming mail

Built in the fourth quarter of 2020 and expanded in the first quarter of 2021.

Problem

The client wanted every incoming message in Outlook to be logged and checked daily: which folder it is in, whether it has been replied to, whether it is flagged for follow-up, and whether any message has been deleted. The goal was systematic mail management and clear accountability for every message.

Solution

A VBA script integrated into Outlook was built:

  • mail logging: every incoming message is recorded automatically, so none slips through;
  • daily status check: once every 24 hours the script checks the message's folder, whether it has been replied to and whether it is flagged for follow-up;
  • integrity check: if a message is missing, the script detects it and sends an alert;
  • highlighting: deleted or missing messages are highlighted automatically so the user notices them.

Technologies used

  • VBA for Outlook: tracking mail without disrupting the user's daily work.

Result

For every incoming message it is known where it is and what has been done with it, and accidentally deleted or moved messages are detected quickly.

Do you need automation in Excel or Outlook?

What development includes, how the price is set and how the work proceeds is described on the Office add-in development page. See larger add-ins among the Outlook add-ins for mail and calendar automation, the Outlook add-ins with API integration and the Word add-ins, and web page automation from Excel and Word on the browser automation and web scraping page.