Python Forum

Hi,

I want to copy just the first 7 columns of my excel sheets to a new file, but columns 4,5,6 and 7 contain formulas linked to lots of cells further right. I create the sheets in the target file first. I can successfully copy the data over, but I just want the current value copied, not the formula.

How can I get the value as a number copied over?

Starting row 4, Column 4 and column 6 contain percentages, 5 and 7 just contain numbers. I have this code, which works, but gives me the formulas, not the number values, in the new file. All the values for cells with formulas are zero, because the relevant cells in the target file are not populated, so the formula equals zero.

for sheet in sourceSheetNames:
	sourceFileActiveSheet = sourceFile.get_sheet_by_name(sheet)
	targetFileActiveSheet = targetFile.get_sheet_by_name(sheet)
	maxRowsourceFile = sourceFileActiveSheet.max_row
	for rowNum in range(1, maxRowsourceFile + 1):
            for colNum in range(2, 8): # copy everything up to and including column 7
                value = sourceFileActiveSheet.cell(row=rowNum, column=colNum).value
                targetFileActiveSheet.cell(row=rowNum, column=colNum, value=value)

You could perhaps create a copy of the worksheet outside of python, without the formulas, as this tutorial suggests.

You should be able to find a solid answer at https://automatetheboringstuff.com/chapter12/.

Thanks for the links.

I'll tinker some more. There must be a way to access the value stored in the formula at any given time. Maybe as simple as int(the formula)