Input Excel tab containing Formula

[Hakimipous]

Hello!

I've come across a bunch of excel files where I need to read only one Tab (called "Output")

However the data in  each cells in the tab I need to read the data from are all linked to an another tab in forms of formula looking like this : 

=Alteryx!A2 or =Output!G2 (or again =Calc!N18*1)

The issue is that when I first input my data into Alteryx, I only have Null vallues

Only way to make Alteryx read the real value, is to open the Excel file, then close while saving.

Edit : here is a pic of my data 

Issue is that I have 100 + files so I was wondering if there was any way around it

Thanks !

[derekbelyea · Accepted solution]

Have you investigated going upstream in the data flow?  It may be worth considering whether the values you need are elsewhere in the  spreadsheet in a form that you can extract.  Sometimes it just makes sense to remove some or all of the logic in the Excel template and re-build it in Alteryx.  In the extreme you can think about removing Excel entirely from the equation.

[Hakimipous]

@derekbelyea

Thanks for the reply ! 

The values I need are in that specific sheet because they are the results of calculus made across all other sheets.  They are in a way unique.

Also removing Excel from the equation is also not an option, as my data depends on macros and calculations performed on Excel 

[derekbelyea]

Here is a hint:  I have had some success using the Workflow Events function together with the dynamic creation of DOS BAT files.   With the large number of Excel files on hand you likely will want to pair up this idea with the Directory tool and an Alteryx Batch Macro.  Instead of the Events function you may want to try the RUN tool.

[Hakimipous]

@derekbelyea

So the idea is to open the excel files via a macro and a Bat file before running the workflow?

[derekbelyea]

Yes, I imagine you can make it work but possibly someone in the Alteryx Community can offer a less convoluted solution.  

[Hakimipous]

@derekbelyea

That's something worth trying! I know there is a Macro in excel than can do it, but yeah it's not the ideal as it takes times and I would rather try to perform on Alteryx as much as I can.

Thanks for the idea ! I'll give it a try tomorrow

[KaneG]

There is a big question here.... What is happening when you open it and save it?

Does that change the format? Run Macros? I think that might be your first question before finding an answer.

[Hakimipous]

@KaneG 

I don't think it changes anything. At least not something I can witness.  I directly see the data in the tab I'm interested in. 

I'm not an excel expert, but if it asks me to save the file while I'm close it, even if I haven't touched anything, means that the macros takes effect only if I open the file? (if that makes sense)

How it works is that I make several copies of a template, in which I insert data into a specific tab via a Workflow I have created(The one called Alteryx)

Then via some Macros in excel, it will produce calculus and data in a tab called Output. That's the tab I would like to read back via Alteryx again. 

So this is when I input the excel file into Alteryx without opening it on Windows

And this is after I open, then close the excel file while saving

Only after doing that manual manipulation I can see the correct data in Alteryx

[KaneG · Accepted solution]

OK, so let me see if I have this straight...

1. Alteryx creates a tab in each spreadsheet called 'Alteryx'
2. There is then Macros in Excel that perform calculations and produce data in a tab called 'Output'
3. You then want Alteryx to read in this tab 'Output'

If the above is correct, then Excel will have to be opened somehow for those Macros to be triggered and the data saved. You could maybe script that in a Batch File, however it seems like a long way around...

As @derekbelyea mentioned, can you go upstream, i.e. what happens in the Macros? Can that be replicated in Alteryx? I'm sure it can, it just depends on whether the Macros access data that is locked down.

Kane