ciudades.py

#!python3
# my first VBA macro redone in Python
# related article: How I got fired up about programming
# https://bobbelderbos.com/2016/02/fired-up-about-programming/
# 1. download game here: http://juegosexcel.com/foro/viewtopic.php?t=6396
# 2. (to avoid loosing a lot of time using older excel libs) save file to xlsx
#
from collections import namedtuple
import re

from openpyxl import load_workbook

SPREADSHEET = 'ciudades.xlsx'
FIRST_SHEET = 'Hoja1'
SOLUTION_FORMULA = re.compile(r'=IF\((\S\d+)="([^"]+)".*')

Answer = namedtuple('Answer', 'cell city')

wb = load_workbook(SPREADSHEET)
ws1 = wb.get_sheet_by_name(FIRST_SHEET)


def get_answers():
    ''' returns tuple pairs of destination cell, right answer '''
    for col in list('CFIL'):
        for row in range(30, 160, 10):
            cell = col + str(row)
            formula = ws1[cell].value

            m = SOLUTION_FORMULA.match(formula)
            if not m:
                raise ValueError('{} does not match exp formula'.format(cell))

            coord, answer = m.groups()
            yield Answer(cell=coord, city=answer)


def update_spreadsheet(answers):
    ''' updates the spreadsheet cells with the right city answers '''
    for answer in answers:
        ws1[answer.cell] = answer.city


def save_spreadsheet():
    ''' save solution to a new spreadsheet '''
    save_to = SPREADSHEET.replace('.xls', '_solution.xls')
    wb.save(filename=save_to)


if __name__ == '__main__':
    answers = get_answers()
    update_spreadsheet(answers)
    save_spreadsheet()