View Underlying Excel Formulas

CGS123

I'm trying to create a list of all the external sources of an excel file, and I'd rather not have to inspect, using naked eye only, every formula within each tab.  Is there a way to, with Alteryx,  view formulas underlying values in an excel file without advance preparation of the file?

SeanAdams

Hey @CGS123,

Firstly - welcome to the community, and congratulations on your first post!

As NJ says, you can look at the formulae to see where there are absolute data points vs. formulae.

If you want to get to the external data sources - the easiest way is to write a little VBA code to enumerate through the data sources: 

https://code.adonline.id.au/list-external-data-references-excel/

Are you trying to scan a bunch of spreadsheets or only one?   If only one, then just pop some code in behind the sheet and you can export the list of external connections.   If you're trying to do this for an entire folder full of spreadsheets, then the easiest way is to do something similar using an Auto Hotkey job which would create an Excel object in memory and do the same as this above.

Let me know if this gives you a good direction towards a solution (and possibly mark this as solved if it does) - or if you still have questions possibly worth popping a sample sheet onto this thread and we can work with you on a specific solution?

Cheers

Sean