Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Alteryx is here to help you solve your biggest data challenges. Read about the new Virtual Solution Center here.
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.

Labels