4 Replies Latest reply: May 15, 2014 10:04 AM by Steve Caiels RSS

    External Lookup **Invalid Object**

    debijo _

      On occassion I get this error with external lookups.  I can find nothing wrong with the xls file.  In fact, I got the error today when I setup an xls file as an external look up in a 2nd model.  (The same exact xls file worked in the first model.)  Any idea how to find out what is wrong?

       

      Thanks,

      Debi

      Monarch V10 Pro

        • External Lookup **Invalid Object**
          Olly Bond

          Hello debijo,

           

          If you're able to, try to use Access mdb files when exporting the intermediate data that you want to use for an external lookup instead. There are a few advantages to this over Excel format:

           

          - no limit of 65,000 rows

          - no misinterpreted field types e.g. "0123" remains a character field

          - no need to worry about named ranges

          - no need to worry about "First row contains field names"

          - no need to worry about versions of Excel or versions of the Monarch export

           

          You don't need to have Access installed on your PC - Monarch can write out and read from this format without problems.

           

          If you need to use Excel's calculation functions, you can replicate these in Monarch's calculated fields or summaries. If you need to write in comments on your data by hand, then Monarch's user-edited fields can be used.

           

          In my experience, there's never a real need to use Excel while manipulating your data, and it frequently causes problems. If I could persuade my IT department to uninstall Excel from my PC, I would be quite happy just using Monarch.

           

          Best wishes,

           

          Olly

            • External Lookup **Invalid Object**
              Steve Caiels

              Hi Debi,

               

              I'd try and update .Net.   3.5 is the latest I think and is a free update from Microsoft.com

               

              Regards,

              Steve.

                • External Lookup **Invalid Object**
                  debijo _

                  Thanks both for your responses. 

                  1)  I converted the data from excel to mdb using Monarch.  Monarch opened the excel sheet fine, and I exported it to mdb.  Then I go to my model, setup the external lookup and I get the same result saying it is invalid.  The wizard appears to completely work, I see the data, I give it the matching keys, I select the fields I want, and when I finish, it gives me the invalid message.

                  2)  I am already running .net 3.5 SP1.  I installed that prior to upgrading to Monarch V10.

                   

                  Any other suggestions??

                   

                  Thanks in advance,

                  Debi

                    • External Lookup **Invalid Object**
                      Steve Caiels

                      Hi Debi,

                       

                      mdb is usually more robust that xls, but is still heavily reliant on .Net and (I think) Jet being in perfect condition. I’d still guess something is wrong with one or the other.

                       

                      .Net 3.5 sp2 is out now and I think Microsoft Jet 4 is on SP8.

                       

                      It may be worth uninstalling Jet 3.5, then reinstall and apply SP2.

                       

                      Maybe Jet and .Net are perfect and it’s corruption within the Excel file that is finding its way into Access during the export.

                      Can you export to csv, then reload into a brand new Excel file and try again?

                       

                      Or even rejoin the csv into Monarch?

                       

                      I’m on holiday/vacation next week, so please don’t think I’m ignoring you if I don’t reply!

                       

                      Regards,

                      Steve.