I am trying to mail merge in cells that have a value resulting from a formula. Below, for example, I have a date cell that is a concatenation of a day/month/year in the spreadsheet. Normally when I mail merge through word, it will just merge in the value. When I do it with the following code though, it will merge in the actual formula instead of what the figured value. Is there a way to have it pull the values instead of the formula?
I've tried this with Excel open and Excel closed. If the cell is just typed text, it merges fine. The only issue is with formulas.
Instead of 1/2/2010 it will merge in =CONCATENATE(C2, "/", D2, "/", E2)
from __future__ import print_function
import os
import openpyxl
from mailmerge import MailMerge
from datetime import date
os. chdir(r'CURRENT WORKING FOLDER')
wb = openpyxl.load_workbook('FullMerge.xlsm')
sheet = wb["Database"]
max_col = 104
sheet.delete_rows(sheet.min_row, 1)
template = "FullMerge.docx"
document1 = MailMerge("FullMerge.docx")
Date = str(sheet.cell(row = 1, column = 1).value)
document1.merge(
Date = Date
)
document1.write("FinishedMerge.docx")
Not very familiar with this environment, but openpyxl does indeed return the formula (if there is one). There do not seem to be two different properties that let you return the value or the formula, but you can eliminate the formulas by doing this:
wb = openpyxl.load_workbook('FullMerge.xlsm',data_only=True)