+ Reply to Thread
Results 1 to 4 of 4

Thread: Export won't overwrite existing table

  1. #1
    wburton is offline Junior Member Monarch Forum Newcomer (Welcome!)
    Join Date
    Jun 2009
    Location
    Albuquerque, NM, USA
    Posts
    3

    Exclamation Export won't overwrite existing table

    I have built a lookup table on sheet1 to work with test data (an actual export) on sheets 2&3. I left sheets 2 & 3 intact but deleted the data to test a new export. I export the data, "adding" to the existing files, "overwriting" the existing table. All seems well until I open the xlsx, receive an error:
    Excel found unreadable content in 'filename.xlsx'. Do you want to recover the contents of the workbook? If you trust the source of this workbook, click Yes.

    Open clicking Yes, the workbook[Repaired] opens and I notice instead of overwriting sheet2 I have a shiny new sheet21 with my exported data.

    I have tried several variations including appending the table instead of overwrite, try xls (export informs me it will only work with Excel 97 or newer???), and a couple others I have forgotten now.

    Any suggestions? Please say 'yes'.

  2. #2
    Data Kruncher is offline Monarch Forum Distinguished Fellow Emeritus Perpetual Guru of Monarch
    Join Date
    Sep 2002
    Location
    Vancouver, Canada
    Posts
    1,705

    Default

    Nope, that's not going to work out too well.

    Hello and welcome to the forum wburton.

    The reason for the failure is that Monarch relies upon named ranges when exporting to Excel files.

    The range name that Monarch creates is the same as the sheet name, in your case, Sheet1.

    I wasn't able to get the unreadable content error in my testing (patched version of Excel 2007 that avoids the error maybe? Just speculating), but I did export to a a sheet named Untitled. There were 1704 records, so with the headers it had 1705 rows of course.

    Monarch automatically created a named range called Untitled with the formula =Untitled!$A$1:$G$1705

    I then selected all of the rows to 1705 and deleted the rows. Checking the named range immediately, the effect of the deletion is the named range Untitled now had the formula =Untitled!#REF!

    Saved and closed the Excel file, and ran the Monarch export to add to the file, overwriting Untitled. Monarch knows the sheet exists because it scans the file for available sheet names before presenting the list of sheets in the export wizard.

    But when Monarch performs the export, it sees that there's a problem with the Untitled named range, so it creates a new range by appending a 1 to the end of the desired name. That's why you got Sheet21.

    The better way to clear out existing data is to let Monarch just overwrite the sheet. Barring that, remove the existing data in Excel manually not by physically deleting the rows, but by instead just clearing the contents of the populated cells. This approach won't muck with the named range that Monarch wants to use, and subsequent Monarch exports won't have any trouble.

    If you clean up (ideally, delete the problematic named range altogether) the problem named range(s) in your Excel file, you should be able to have Monarch export to it without any trouble.

    HTH,
    Kruncher
    Maximize your Monarch skills in 30 days with this training package.
    ---
    Now supporting Monarch V10.5 Pro and V9.01 Pro.
    Help us help you. Post a sample of your problem between the [code] and [/code] tags and preview your post.

  3. #3
    wburton is offline Junior Member Monarch Forum Newcomer (Welcome!)
    Join Date
    Jun 2009
    Location
    Albuquerque, NM, USA
    Posts
    3

    Default

    Thanks DK
    Sounds like we're on the same page - I had tried deleting the rows/sheets but got the #REF errors in my VLOOKUPS.
    I haven't actually created a named range on the data sheets but the VLOOKUPs creates them within the formulas behind the scenes. ?

    I've tried just deleting the data from the sheets - you mentioned:
    "The better way to clear out existing data is to let Monarch just overwrite the sheet. Barring that, remove the existing data in Excel manually not by physically deleting the rows, but by instead just clearing the contents of the populated cells."
    I think I've tried every way possible to overwrite the sheet (populated as well as blank), or append to the sheet after clearing all the cells.
    I have about 250 organizational reports to populate and I'm lazy... err, efficient so I hope to minimize copying from Untitiled1 over to Untitled and such.

    Thanks again!!

  4. #4
    Join Date
    Jun 2006
    Location
    New Braunfels, Texas
    Posts
    52

    Default

    Quote Originally Posted by Data Kruncher View Post
    Nope, that's not going to work out too well.

    Hello and welcome to the forum wburton.

    The reason for the failure is that Monarch relies upon named ranges when exporting to Excel files.

    The range name that Monarch creates is the same as the sheet name, in your case, Sheet1.

    I wasn't able to get the unreadable content error in my testing (patched version of Excel 2007 that avoids the error maybe? Just speculating), but I did export to a a sheet named Untitled. There were 1704 records, so with the headers it had 1705 rows of course.

    Monarch automatically created a named range called Untitled with the formula =Untitled!$A$1:$G$1705

    I then selected all of the rows to 1705 and deleted the rows. Checking the named range immediately, the effect of the deletion is the named range Untitled now had the formula =Untitled!#REF!

    Saved and closed the Excel file, and ran the Monarch export to add to the file, overwriting Untitled. Monarch knows the sheet exists because it scans the file for available sheet names before presenting the list of sheets in the export wizard.

    But when Monarch performs the export, it sees that there's a problem with the Untitled named range, so it creates a new range by appending a 1 to the end of the desired name. That's why you got Sheet21.

    The better way to clear out existing data is to let Monarch just overwrite the sheet. Barring that, remove the existing data in Excel manually not by physically deleting the rows, but by instead just clearing the contents of the populated cells. This approach won't muck with the named range that Monarch wants to use, and subsequent Monarch exports won't have any trouble.

    If you clean up (ideally, delete the problematic named range altogether) the problem named range(s) in your Excel file, you should be able to have Monarch export to it without any trouble.

    HTH,
    Kruncher
    I have some coworkers using a model I created, and only one of them is getting the error mentioned in this post. We don't modify the exported file, as it is a lookup file for other models. However, after she exported the table, I even tried to open it and received the same error. I am not sure why or how to fix it. Any ideas?

    Thanks!

+ Reply to Thread

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts