We recommend switching to the latest versions of Edge, Firefox, Chrome or Safari. Using Internet Explorer will result in a loss of website functionality.

Dynamic Sheet in Excel

Comments

4 comments

  • Avatar
    Gerry Mullin

    This might not be the most intuitive data flow but hopefully it more or less achieves what you need. What it does:

     

    1. If CurrentMonth exists on the input Excel file, then we will read that sheet, append your new data to the end.

    2. If CurrentMonth does not exist, we just create a dataset that has the new data on it.

    3. We will make a copy of your input file to the new file name (I set it as a data flow parameter). 

    4. If step 1 was true, we will overwrite the CurrentMonth sheet with the updated dataset on our newly created file.

    5. If step 2 was true, we will create a new sheet named CurrentMonth to the end of the newly created Excel file, with all the other sheets remaining on the output file.

     

    Attached files

    Dynamic Excel - 23 Sept 2020.lna
    File-NoCurrentMonth.xlsx
    File.xlsx

     

    0
    Comment actions Permalink
  • Avatar
    Rubal Grewal

    You are awesome Gerry!!! Thank you so much for the quick support and the sample shared.

    I will test it and share the update... Really Appreciate your efforts

    0
    Comment actions Permalink
  • Avatar
    Rubal Grewal

    It worked fine Gerry but for the output excel, it is just writing new data. The old worksheets present in the file are not getting reflected in the new file. Also, for the tab name in the new sheet generated, how can I make it dynamic based on the current year and month.

    0
    Comment actions Permalink
  • Avatar
    Gerry Mullin

    Hi Rubal, I don't follow when you say "The old worksheets present in the file are not getting reflected in the new file."

    I just ran the data flow again against the files uploaded (File.xlsx and File-NoCurrentMonth.xlsx) in each case the final output maintained the existing sheets on the workbooks and either appended the new data that existed in the node "Some New Data To Append" to the end of the existing data (in the case of File.xslx) or just wrote out that data as new onto a sheet named CurrentMonth.

    With regard to making the sheet name dynamic I don't think that is possible (or at least not easily) on the output as the name is taken from the pin name of the node and that cannot be tweaked dynamically unfortunately. The only way I can see to rename the sheet after writing out the file would be to have some custom code from Python or Java to do the rename. This is possible and it looks like there is plenty of information online about it but not something I have tried before unfortunately.

    0
    Comment actions Permalink

Please sign in to leave a comment.