I have a morph file that produces an excel workbook with serveral sheets.
One of the sheets is an overview sheets for which I want to create hyperlinks in excel so that users can jump to the right sheet.
In EasyMorph the formule is like this:
=HYPERLINK(CONCATENATE("#";A2;"!A1");A2)
When I open the exported excel file, The formula only works when I press enter in one of the cells and fill down the formule.
My question: Is there a way to export to excel in such a way that the formula will work directly when opening the exported file ?
Not with the standard means of EasyMorph. As a workaround, you can have a separate Excel file with a VBA macro that will open the file created by EasyMorph and go through each formula and “press Enter” in each of them. Trigger that macro with the “Excel command” action after exporting data.
Perhaps @CraigT can explain better how to do it with VBA.
EasyMorph is writing these formulas to Excel a text. Excel hasn’t had a chance to “interpret” them into hyperlinks until you open Excel and “trigger” that by hitting Enter on each one.
As dgudkov mentioned, you will need a VBA macro somewhere to trigger this action (running through the cells with the formulas and “triggering” them to set them to hyperlinks). You would then trigger this macro with the Excel command action (“Run VBA macro” mode).
Probably the best approach is to create a macro in a separate .xlsm file that takes the output file’s path + name as an argument, opens it, runs through it to create the “live” hyperlinks, then resaves it.
Feel free to DM me if you need more guidance or detail regarding this.