Alteryx Designer

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

using dynamic input on excel files with different tab / worksheet names

Highlighted
5 - Atom

Hi I want to upload  to mutiple excel  files  from Column  eg: 4  to  10 and dynamically changing from Quarter to Quarter field values and also  want to pick up the data from the starting column A which is the months column .How will I do it through Macro .Can any help ?

Highlighted
8 - Asteroid

Hi @JAIN_P 

I think I've done something similar, though it sounds like you will have to add some steps so that you can change the sheet range as needed (which is why, I suppose, you are looking at a macro).

 

To get a specific range from an Excel sheet, I first used a Directory tool to get the full path to all the workbooks in my source folder. I then parsed the output field containing the path so I could work with the complete path. You can add a sheet name and range to the path to get what you want.

asteryx_0-1580493922951.png

The formula for my case assigns a new value to the [FullPath] field like this:

[FullPath]+"|'PFE Metrics$a1:c7'"

 

[FullPath] is what comes out of the Directory and Parse tools

The rest is in double quotes: first a | character, which Excel requires and then the range syntax in single quotes: 'sheetname$range'

 

You could then add multiple formulas for each of your use cases and then feed them into a macro that reads the data with a dynamic path string.

You can also query the workbook for the sheet names if you need to get them dynamically.

Highlighted
8 - Asteroid

Hi @Joe_Mako May I ask where are the two macros you mentioned? I downloaded the package from the link you provided and had it installed. Now I can see CReW Macros and CReW Test in the Alteryx tool bar. Are they what you mentioned? I am confused.

Labels