Working with an Excel Sheet with changing sheet name

Shawn_21372
Shawn_21372 Altair Community Member
edited September 2021 in Community Q&A

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

 

Tagged:

Answers

  • Lasse
    Lasse Altair Community Member
    edited September 2021

    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

Categories