I had a user ask if they could use an Excel data source and specify the worksheet to use at run time. Short answer is no, the name of the worksheet to use is specified in the Data Mapper and is not variable or dynamic.
Longer answer: change the sheet name in Workflow.
This VBScript reads in the name of the worksheet to be used by Connect from a Watch Local Variable named “RunSheet”.
The Connect Data Map is hard-coded to use a worksheet named “ConnectSource”.
Execute this script before the Execute Data Map plugin.
Dim JobFile, RunSheet JobFile = Watch.GetJobFileName() RunSheet = Watch.GetVariable("RunSheet") 'launch Excel and open file Set xlObj = CreateObject("Excel.Application") Set xlFile = xlObj.WorkBooks.Open(jobfile) 'turn off screen alerts xlObj.Application.DisplayAlerts = False 'instatiate target worksheet, then rename it set xlWorksheet = xlFile.Worksheets(RunSheet) xlWorksheet.Name = "ConnectSource" 'save, close, then quit xlFile.Close True xlObj.Quit set xlWorksheet = nothing set xlFile = nothing set xlObj = nothing