4 Replies Latest reply: May 15, 2014 10:09 AM by warlok RSS

    External Inport Excel 2003 file is an invalid object

    warlok

      Everything I've tested that I've done on version 8.0 so far has worked fine except for one project where I have an external lookup. The file in question is saved as an Excel 2003 file. (.xls extension) That's created by an outside source not under my control.  Everything about the Lookup seems to look okay except it's listed in Monarch Pro as being an Invalid Object. If I turn around and resave the table in Excel 2007 format (.xlsx) it works fine with no problems. It just seems to have problems wit the 2003 format. I wouldn't have an issue with that myself, but the users who maintain the lookup table will most likely not have access to Excel 2007 or even begin to understand the differences in versions. They will forget and not maintain it under the new format. (Thanks Microsoft for making our lives oh so much easier!) Any ideas or suggestions? I'm stumped as to why the new version of Monarch is having issues with this since the old one did not and Excel 2003 is still a valid and supported product. The source, the links, the percentages all link and show.It just tells me at the top it's an invalid object. :confused:

        • External Inport Excel 2003 file is an invalid object
          warlok

          Well I'm not sure what caused the issue, but I had to delete the link and create from scratch to get it to sort of work again.

           

          With version 8 of Monarch Pro, the file would read in the field information which was a basic divison of 2 other fields. The answer would be a long decimal number 5.334455444 as an xample. Excel was set to display this number to 1 decimal point. So it would show 5.3 When it was read into Monarch it would pull the full amount as a number into it's workings. so I would et a result pulled form the table as 5.334455444 to work with.

           

          With Version 10.5, I've managed to recreate the external lookup but it refuses to do anything except a general filed type. meaning it's pulling it like a string. This also means that if face value is 5.3 it pulls 5.3 and disregards the rest of the decimal points.

           

          Short of changing the spreadsheet which is out of my hands and a none option, I'm not sure what I can do. I'm not sure why Monarch Pro is forcing the lookup information to be a type General. Any explanations? :confused:

            • External Inport Excel 2003 file is an invalid object
              Data Kruncher

              This sort of thing can be a frustrating exercise at times.

               

              Would it be too much trouble to first save the Excel file as a CSV file and then link to it?

               

              You may find that you need to increase the number of decimals displayed before saving the CSV file, otherwise Excel may save 5.334455 as 5.3 in the CSV file (as my copy of Excel 2010 does), and obviously Monarch will be limited to 5.3.

               

              This solution has worked for many in the past.

                • External Inport Excel 2003 file is an invalid object
                  warlok

                  I wish I could do that, it's more a file they use for display rather than actual work. If I had my way they'd save it the new format and that would be that. But people hate change. I get the feeling this is the beginning of things changing though. I'm just surprised the newer version of Monarch Pro has changed how it reads the file. Old version was fine new version changes. I understand MS changed their Office products (mumble mumble) so I understand certain changes would be needed to read the new format XLSX files. I didn't think it would or even should affect existing .xls files. And in fact that kind of surprises me. It's almost an MS decision where a new situation arises and Monarch can go one of two was, but instead of giving the user the decision which way to go, it's railroaded a certain way. Perhaps I'm ranting, but I've run into this a few times over the last few years and companies fail to see how it really cuts into their bottom line. The simple fact is it should be the end user who makes the decision. Why not allow me to define the field type of the data used in the external lookup? I still can't figure out why I run into issues using the old format and yetthe new format has no problem, it works fine and I get the result I had before and was expecting. I just wondered if there was a good reasoning for it that anyone knew about because I'm stumped as to why change it. I usually go with the adage that if it isn't broken, why change it, so I'm assuming there's a reason for it. I may never get the answer I'm looking for and might just be stuck with "work around it" Which is a valid answer, just not a good, business buillding one.

                   

                  Of course, i could be just being overly grumpy too.:)

                    • External Inport Excel 2003 file is an invalid object
                      warlok

                      Just an update to the issue. The fields I was trying to import was a calcuated field in the spreadsheet which were calculated using other fields. for example. C1 was represnting B1 / A1

                       

                      A solution which worked for me was to import the original fields A1 and B1 and have Monarch do the division work for me. It also allows me to round the results to as many decimal places as I want with Monarch.

                       

                      Not a perfect fix, but works good enough for me. I figured I'd share and let everyone know.