🎉Community Raffle - Win $25

An exclusive raffle opportunity for active members like you! Complete your profile, answer questions and get your first accepted badge to enter the raffle.
Join and Win

Working with an Excel Sheet with changing sheet name

User: "Shawn_21372"
Shawn_21372
Altair Community Member
Updated by 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

 

Find more posts tagged with

English
Monarch
Sort by:
1 - 1 of 11
    User: "Lasse"
    Lasse
    Altair Community Member
    Updated by 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

Quick Links