3 Replies Latest reply: May 15, 2014 9:59 AM by Nick Osdale-Popa RSS

    Yet another ? on Project Exports

    Nick Osdale-Popa

      v7.01

       

      Never had the use of Projects until now.

       

      Same report/model will be used each time.  I set up the project to open the report and model, but I can't seem to get the Project Export to create a new tab each time.

      I'm downloading financial data, I want it to always download to a tab called Current.  The user will be renaming the tab to the month. On the next download though, it's appending/overwritting the data, there's no option to create a new tab. And if the user changes the structure of the spreadsheet, an error occurs.

       

      Am I missing sumthin'?

        • Yet another ? on Project Exports
          Gareth Horton

          Nick

           

          The problem lies with the Named Range that Monarch creates in Excel.

           

          In the first instance, you run a Project export that creates a tab called Nick, but also creates a named range called Nick.

           

          Everything is fine.

           

          Now you rename the tab in Excel to January, but the named range called Nick is still there.

           

          Now you try it again, but it overwrites it as it finds the named range (which has updated to refer to the newly-named tab)

           

          So, you will need to delete the original named range, as well as renaming the tab, before you do a consecutive export.

           

          To delete a named range, go to the Insert menu, select the Name menu item, select the Define item, choose the name from this list, and click the Delete button. (Excel 2k)

           

          VBA-Wise:

           

          Deleting a range can be done like this:

           

          ThisWorkbook.Names("Nick").Delete

           

           

          Gareth

           

           

          Originally posted by Nick Osdale-Popa:

          v7.01

           

          Never had the use of Projects until now.

           

          Same report/model will be used each time.  I set up the project to open the report and model, but I can't seem to get the Project Export to create a new tab each time.

          I'm downloading financial data, I want it to always download to a tab called Current.  The user will be renaming the tab to the month. On the next download though, it's appending/overwritting the data, there's no option to create a new tab. And if the user changes the structure of the spreadsheet, an error occurs.

           

          Am I missing sumthin'? /b[/quote]

          • Yet another ? on Project Exports
            Grant Perkins

            Nick,

             

            Gareth has nailed the problem (as usual).

             

            I just tried the alternative of getting the user to COPY the table rather than rename and that looks like a pretty safe option as well.

             

            I set the Project Export to append (since no data changes between runs) and I get the appended data in my first table and no changes to the copied table from which I had deleted a chunk of data toprove the point!

             

            I guess it is down to how much you can trust the users to always get it right? Or give them an Excel script that would automate the process of copying the table(s)?

             

            FWIW.

             

            Grant

             

             

            Originally posted by Nick Osdale-Popa:

            v7.01

             

            Never had the use of Projects until now.

             

            Same report/model will be used each time.  I set up the project to open the report and model, but I can't seem to get the Project Export to create a new tab each time.

            I'm downloading financial data, I want it to always download to a tab called Current.  The user will be renaming the tab to the month. On the next download though, it's appending/overwritting the data, there's no option to create a new tab. And if the user changes the structure of the spreadsheet, an error occurs.

             

            Am I missing sumthin'? /b[/quote]

            • Yet another ? on Project Exports
              Nick Osdale-Popa

              Thanks for the info Gareth!