Does anyone have suggestions for tweaking the export feature to allow creation of multiple sheets within a single workbook? I'm building a model to export records that fall within a series of distance buffers and would like them to appear as individual sheets within a single XLS file for analysis, rather than having to work across multiple XLS files.
I've altered the Table to Excel code to set the sheet name to that of the input dataset to uniquely identify each sheet, but when I try to run the tool as a batch, the process returns an overwrite error and terminates. If I set the geoprocessing options to allow overwrite, the batch runs, but the resulting XLS file only has the sheet from the last dataset processed.
Given the alterations to the original input parameters I've described, could the code be modified to follow some variation of the process below?
Inspect the output directory for a file with the same name as the output filename parameter:
If no, create the XLS file with the exported sheet named for the input dataset and save the file;
If yes, skip the creation step and go directly to adding the dataset sheet, and save the file.I'd imagine that with this modification, the script could be run once through all the datasets by incorporating some sort of "for...in..." iteration through the list of datasets, but I'd also like to incorporate something to prevent the overwrite problem if I come back and run the model with some new variables (i.e. I'd like them to be added as new sheets to the same "master" XLS file).
I've seen scripts that can collect multiple CSV files as sheets within a single XLS document, but it seems like a somewhat clunky workaround.
Any tips/tricks/pointers are appreciated!