Data-driven loop: how to switch data source?

Hi guys.



I tried to find the answer but could not.



I have data-driven test based on data in Excel sheet but I need to switch the source (for another Excel workbook/sheet) depending on what user of my automated tests prefers.



I cannot use filters within a spreadsheet due to complexity of data (there are several workbooks depending on testing sites which can be selected by configuration (e.g. selecting site I change all locations of folders: input/output data, logging, archiving). Site is logical thing, all stuff runs on the same workstation.



I found two workarounds so far:

1) make a copy of workbook (whole Excel file) selected in configuration to some fixed location which addressed in DD variable;

2) make copy of a spreadsheet into table type variable and use this variable instead of Excel sheet. Unfortunately, this approach requires manual structure syncing of Excel sheet and table variable, otherwise I cannot assign fields (names) within a KDT.



I just hope that I missed something and more elegant solution exists.