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.
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,
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!