Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Get Input File Name and Location from excel document cell or yxdb file.

rking117
6 - Meteoroid

Hello,

 

I created a workflow which when run allows the user to select a file located on a LAN or on their computer and then outputs (writes) that location and file name into a cell in a temporary Excel document.  Where im stuck is i have multiple workflows that will be run one after the other that will need to take that input file from the cell in the excel document and use that in an Input Tool or some other input to make that excel document and tab accessible to the workflow.

 

Let me give you an example to make sure i have given you a clear message.

 

Workflow A (analytic app) when run asks the user to select a file.  This workflow writes the location and file name that the user selected to a temporary excel document called, "variable.xlsx" and places it on tab "var".  We will call this the "Variables" file. The variable file is written to a temp location that is accessible repeatedly until it is overwritten or deleted.

 

An example of this file location and name which the user would select is:

"C:\Alteryx\Rates\Prices_08-31-2019.xlsx" we will call this the "Rates" file.

 

This file location and name is written to the Variables file on the var tab in column 2.  That was the easy part for me.

 

From here i have about 12 workflows that will be run one after another but each of the 12 workflows will need to reference and input the rates sheet.  To make thing even more complicated is the Rates sheet has one tab per each workflow that will be run.

 

An example of the tabs are:

Branch Office A

Branch Office B

Region A

Region C

etc...

 

So basically when the BranchOfficeA workflow runs i need it to open the Variables file get the file name and then get the data from the Branch Office A tab. Then BranchOfficeB gets Branch Office B tab etc.  It is the input that im stuck on here. essentially i just want to make the input variable.

 

I hope i didnt confuse anyone here and i really appreciate your assistance on figuring this one out.

4 REPLIES 4
benakesh
12 - Quasar

Hi @rking117 ,

The dynamic input  is  your answer .  

You read  the  variable  file and  use formula tool  to build  file name  plus  sheet name ( ex :  filename.xlsx|||sheet1 )  .

Use dynamic  input  ( change entire file path )  to read  the file . 

rking117
6 - Meteoroid

Hi Benskesh,


Thanks for that, i tried using the dynamic input tool before as i though that would be the answer.  However, when i try to build  a simple query like this i get the following error message when i run it.

Error Message
---------------------------------------------
Dynamic Input (1) Error opening table: Microsoft Access Database Engine: The Microsoft Access database engine could not find the object 'Branch Office A'. Make sure the object exists and that you spell its name and the path name correctly. If 'Branch Office A' is not a local object, check your network connection or contact the server administrator.\3011 = -543884569


Workflow
----------------------------------------------
See attached workflow for details.

 

Note that i made the default file the same as the dynamic file that im trying to open from the input text box.

benakesh
12 - Quasar

Hi @rking117 ,

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Error-opening-table-Microsoft-Access-D...  has details about  similar issue . 

Add    '$'  at end of sheet name  and try  :  Pricing_08-12-2019.xls|||Branch Office A$  

If possible  you can create this  file  in current format  (xlsx)  instead of legacy "xls"  format . 

 

rking117
6 - Meteoroid

Bingo that did the trick. I reattached the working sample workflow for anyone else who needs to figure this out.

Labels