Automate Excel Report Updates from Google Sheets

I developed a VBA-based tool for automatically updating Excel reports. The goal was to link the manager's working Excel file to Google Sheets, where the source data was regularly updated, and eliminate the need for manual copying between tables.

One-click update

The solution works with the click of a button in Excel: the macro downloads the latest version of Google Sheets in .xlsx format, opens it as a temporary file, extracts data from five specified sheets, and transfers it to the corresponding sheets of the main Excel report. When updating, not only the values are preserved but also the visual structure of the tables: cell colors, fonts, alignment, column widths, row heights, and filters.

Long barcode display

An issue with the display of long barcodes was also addressed. Excel automatically converted such values to scientific format (E+12), so the macro was updated to handle columns containing barcodes and force formatting of the values in a readable format.

Cross-platform support

The solution was also adapted for different operating systems: on macOS, file downloads are performed via curl, and on Windows, via the URLDownloadToFile system API. This allows the same Excel file to be used on both my computer and my supervisor's computer without installing any additional software.