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
