Grab Values from Excel via VBA, YAML and Python (pywin32)

Public Sub grabFormula()
Dim wks As Excel.Worksheet
Dim quotes As String
quotes = """"
Set wks = ActiveSheet
For Each cell In Selection.Cells
    Debug.Print " - " & quotes & "'" & wks.Name & "'!" & cell.Address & quotes
Next cell
End Sub
' ---
Public Sub grabText()
Dim wks As Excel.Worksheet
Dim quotes As String
quotes = """"
Set wks = ActiveSheet
For Each cell In Selection.Cells
    Debug.Print " - " & quotes & cell.Value & quotes
Next cell
End Sub

We then use python to generate YAML:

from pathlib import Path
import os
import yaml
import re

# Input data from your example
cell_references_raw = """# Cell References
 - "'My Sheet'!$G$6"
 - "'My Sheet'!$H$6"
 - "'My Sheet'!$I$6"
"""

cell_values_raw = """
# ----------------------------------------------------------------
# Cell Values 
# ----------------------------------------------------------------
 - "Blah"
 - "Blah Blah"
 - "Blah"
# ------------The end ----------------------------
"""


# Extract items using regex pattern matching
def extract_items(text, pattern_string):
    pattern = re.compile(pattern_string)
    items = []
    for line in text.split("\n"):
        match = pattern.search(line)
        if match:
            items.append(match.group(1))
    return items


# Extract cell references
cell_refs = extract_items(cell_references_raw, r'- "(.*)"')

# Extract cell values
cell_values = extract_items(cell_values_raw, r'- "(.*)"')

# For debugging
print(f"Found {len(cell_refs)} references and {len(cell_values)} values")

# Create mapping
if len(cell_refs) == len(cell_values):
    # Create YAML content manually
    yaml_content = ""
    for ref, val in zip(cell_refs, cell_values):
        # Handle multiline values with pipe character
        if len(val) > 60 and ' ' in val:
            yaml_content += f'"{ref}": |\n'
            # Indent each line of the value with two spaces
            wrapped_val = ""
            current_line = "  "
            for word in val.split():
                if len(current_line) + len(word) + 1 > 80:  # Wrap at 80 chars
                    wrapped_val += current_line + "\n"
                    current_line = "  " + word
                else:
                    if current_line == "  ":
                        current_line += word
                    else:
                        current_line += " " + word
            wrapped_val += current_line + "\n"
            yaml_content += wrapped_val
        else:
            # For empty or short values, keep on one line with quotes
            yaml_content += f'"{ref}": "{val}"\n'

    # Write to file
    with open(os.path.join(Path.cwd(), 'cell_mapping.yaml'), 'w') as f:
        f.write(yaml_content)

    print("YAML file successfully created!")
else:
    print(f"ERROR: Mismatch in number of references ({len(cell_refs)}) and values ({len(cell_values)})")

Now that we have got the YAML, the last script will just copy it to another Excel file structured the same way

import yaml
from win32com import client as c
from icecream import ic


def get_win32com_object(object_name='Excel.Application'):
    try:
        _object = c.gencache.EnsureDispatch(object_name)
    except AttributeError:
        # Corner case dependencies.
        # import os
        import re
        import sys
        import shutil
        # Remove cache and try again.
        module_list = [m.__name__ for m in sys.modules.values()]
        for module in module_list:
            if re.match(r'win32com\.gen_py\..+', module):
                del sys.modules[module]
        shutil.rmtree(os.path.join(os.environ.get('LOCALAPPDATA'), 'Temp', 'gen_py'))
        _object = c.gencache.EnsureDispatch(object_name)
    return _object


xl_path = r'my_path.xlsx'
xl_app = get_win32com_object()
wk_book = xl_app.Workbooks.Open(xl_path)

with open('cell_mapping.yaml', 'r') as fin:
    data = yaml.safe_load(fin)

for key, value in data.items():
    xl_app.Range(key).Value = value

rdapaz/GetValuesFromExcel.md