0

I have a Python code which is run from an Excel Workbook and that exports a table to the workbook through pasting from clipboard.

In the Python dataframe which is exported to Excel some columns contain text strings, which are Excel formulas referring to named ranges in the destination Excel file where the code exports the results. For example one text string is:

=VLOOKUP(INDEX(EPT,ROW()-ROW(EPT)+1,12),TechnologyData_EPT,3,FALSE)

where EPT and TechnologyData_EPT are named ranges in the Excel Workbook.

After pasting, the columns will display the text string and not the formula results. Pressing F9, Ctrl+Alt+F9, Ctrl+Alt+Shft+F9 won't update the formulas. The cell format is Standard, not Text.

I tried to write a Find and Replace module in the VBA code, finding "=" and replacing it with "=", but this won't recalculate the formulas either. It works though if I do it manually outside VBA.

Worksheets("EPT").Range("EPT").Select
fnd = "="
rplc = "="
Range("EPT").Replace what:=fnd, Replacement:=rplc, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False

Any clue about why this is happening? I would like to make the formulas update automatically when running the code.

EDITED

Just to be clearer, in python Excel formulas are added with the following module:

def xlLookup(lu_table, lu_clm, lu_key, sep=';'):
    if sep==';':    formula = '=LOPSLAG(INDEKS(EPT;RÆKKE()-RÆKKE(EPT)+1;{});'+lu_table+';{};FALSK)'
    else:           formula = '=VLOOKUP(INDEX(EPT,ROW()-ROW(EPT)+1,{}),'+lu_table+',{},FALSE)'
    return formula.format(lu_key, lu_clm)

The final dataframe is exported to clipboard and then copied in the Excel workbook through the following VBA code:

With Sheets(SheetName)
     .Rows(FirstRow & ":" & .Rows.Count).Delete 'clear
     .Cells.NumberFormat = "General"
     .Paste (.Cells(FirstRow, 1))
End With

After pasting, the formulas are displayed as text string and so far I haven't found a way to make them calculate automatically - the only solution I found is to do a manual Find and Replace (Find = and Replace =).

Matteo N.
  • 1
  • 1
  • Could be that python is pasting the formulas with a leading `'`? if so, you should remove it. Also this line: `Worksheets("EPT").Range("EPT").Select` isn't necessary, just `ThisWorkbook.Sheets("EPT").Cells.Replace...` – Damian May 28 '19 at 08:41
  • Possible duplicate of [Save Pandas DataFrames with formulas to xlsx files](https://stackoverflow.com/questions/51348874/save-pandas-dataframes-with-formulas-to-xlsx-files) – asongtoruin May 28 '19 at 08:43
  • dont u have to tell thats a formula u are putting. like cel.formula="=VLOOKUP(INDEX(EPT,ROW()-ROW(EPT)+1,12),TechnologyData_EPT,3,FALSE)" ? – Luis Curado May 28 '19 at 08:57
  • How are you pasting the formula? Without seeing your code it is hard to help, but you should be using `[CellReference].Formula=` or similar (my personal preference is always to use `FormulaR1C1=`). –  May 28 '19 at 09:00
  • I edited my post with more elements, I hope it's clearer now. – Matteo N. May 29 '19 at 08:11

0 Answers0