Working with an Excel Sheet with changing sheet name

Shawn_21372

Hi,

I have a situation where I am receiving an Excel spreadsheet from a third party which is used is an input in a Monarch model in an Automator flow. Lets call this workbook, Workbook1.  Workbook1 will have the same name (I'm identifying which file to use via script and renaming to a static name), however the sheet name will be different each day  (the date is being used in the sheet name).  Apparently changing the sheet name requires a good bit of effort on the 3rd party's side.

Is there a way to handle a Database Input with a changing sheet name?  I may be missing something simple, but I cannot get a Database Input .xlsx to work with a blank sheet.  If there is no simple way through the built-in actions, I believe this can be handled via script, but I'm having issues identifying what Reference / Assembly I should be importing in order to work with the Excel Application in a script step.

Any help would be appreciated.

Thanks,

Shawn

 

Lasse

Hi Shawn,

As far as I know there is no possibility to handle changing sheet names. It’s been a while since I figured this out. Of course it may be that the feature has been added in the recent past.

As you mentioned a script could be a solution. May be EPPlus-reference package could solve the problem. Just download the package and then add epplus.dll (included in the package) as name and officeopenxml as namespace in "References, imports".

Then script:

dim filepath As String = "C:\\testfile.xlsx"

Dim file as New FileInfo(filepath)

Using ExcelPackage As New ExcelPackage(file)

     Dim worksheet As ExcelWorksheet

     worksheet = ExcelPackage.Workbook.Worksheets(0)

     worksheet.name = "NameYouWant"

     ExcelPackage.SaveAs(file)

End Using

 

I'm rebembering that the package did not work with older versions of Automator but with newer ones yes.

 

-Lasse