Toad World® Forums

Updating Excel Reports - Duplicate Sheet


#1

Hi, I followed the instructions on this sheet but rather than updating an existing named sheet, TDA creates a second sheet with a suffix e.g.-FileName _1_2. See image attached. I have already confirmed that my settings are set NOT to add a sufix but it is still doing this. What I would like it to do is to update the data on the specified sheet and keep all the other tabs intact (since those are the report tabs that draw from the data). Instead, TDA is erasing adding a sheet. I tried toggling the replace existing file setting but that erases the whole file and simply replaces it with just the data sheet.

This problem is the same if I try and program this from the automation page using the select to file and specify a specific file and specific sheet (row and column). Again, my expectation would be for the data to update in that specific sheet and leave everything else in the file intact. Instead it is creating a secondary sheet. Or as mentioned before if I specify replace file it replaces the whole thing -including my report sheets.

Please help and thanks,

Antonio

Snap1.jpeg

Server Error in ‘/’ Application.


Operation is not valid due to the current state of the object.

    Operation is not valid due to the current state of the object.

         **Description:**
        An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

         **Exception Details:** System.InvalidOperationException: Operation is not valid due to the current state of the object.

        **Source Error:**

`

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.`

        **Stack Trace:**
   [InvalidOperationException: Operation is not valid due to the current state of the object.] System.Web.HttpValueCollection.FillFromString(String s, Boolean urlencoded, Encoding encoding) +4008959 System.Web.HttpUtility.ParseQueryString(String query, Encoding encoding) +213 Telligent.Evolution.Rest.Framework.Infrastructure.RestPluginHttpHandler.ProcessRequest(HttpContext context) +325 System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +625 System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +270

``


Version Information:
Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.272

">Update_your_Excel_reports…_automatically.docx


#2

I’m not able to reproduce this issue. What version of TDA are you using? I remember this was occurring this way in one Beta release. Also are you exporting a table, query or query file?

Can you send me a copy of your export template? Perhaps I can see what is different in how I am exporting.

Debbie


#3

You must be using the 2.6 Beta. I changed the behavior in this release a little. If a named sheet exists I create a new sheet with an appended time_stamp to make the name unique. If you want the namedsheet to be deleted, currently you need to choose the Overwrite option. This will delete the whole file and export to a new named datasheet. I picked this route instead of deleting a specific named sheet.

Does this work for you? Or is it confusing?

Debbie

P.S. Let me know as soon as you can as we only have a few more days to make changes to 2.6.
NamedWordSheet.png


#4

Okay I took another look at exporting to Excel and I can see the issue you are running into. I have added a couple of new options. Here is the new behavior.

  1. If you export to a new file without any other options the data will go to the first sheet and be named Sheet1.
  2. If you export again to that same file without any other options it will put the data in the second sheet named Sheet2. (and so on. Basically it is appendng worksheets)
  3. New to TDA 2.6 there is an edit box for entering a Worksheet name. You can enter the name for a worksheet or if the file already has an existing named sheet you can point to it and use this name. (As shown in screenshot)
  4. This will export to the existing named sheet (or create one if a new file) and NOT append like your current experience.
    4a) The same behavior is available for named ranges.
  5. If you would like to clear the data before sending the data I have added an option to do this. This does not delete the sheet and re-add, but just clears the data before exporting. Handy if the number of rows is less than the last time.
  6. If you would like to use a named worksheet but append a timestamp to the name and create a new worksheet each time, there is a new option for this. This would be handy if you want to schedule exporting a result set and compare the changes.

I think these options will better suit your needs. I will get them into the next Beta so you can give it a whirl before we make the next release.

Debbie
ExcelWorksheetOptions.png