Hello All,
I have an Excel Spreadsheet (Office 2003) with about 14000 rows, 40 columns worth of data that I am loading to a Notes database. Everything loads fine, all of the documents are created with all of ther fields on the form loaded properly. I also have an Excel template that is used as a reporting tool within the Notes database. If I click my button to create the report, for certain documents, I get an OLE Automation Error.
I found out that from the initial load spreadsheet, the docs that get the error (about 200), have a really strange format in the cell. The data in the cell is just a description (text). Some times it has numbers and special characters in there, but the content all looks the same if I look at the format of the cell. Example:
In the cell is this: Dealer code is invalid
The format of the cell is General
the sample view is ######################
If I change the format of the cell to Text, it is the same, but if I change the format of the cell to Number it is Dealer code is invalid?!?! I also tried to blank (clear) the cell and type it in myself, even starting off the cell with a space and then typing, with the same result!?!?
I also notice that in the “Name Box”, it has OLE_LINK1 in there instead of the cell reference number (i.e. C7). And it cannot be removed??
I then thought I would fix it in my lotus script load agent (converting the data to a string - then load it to the field, to a variant - then to as string - then to the field, and a couple of other conversions, all with no success!! I still get an OLE automation error when I try to create a report back out to the excel spreadsheet template??
I also tried to fix it in my lotus script create report agent (converting the data to a string - then load it to the cell, to a variant - then to as string - then to the cell, and a couple of other conversions) all with no success as well!!
Has anyone else run into this dilemma, and is there a solution available?? I do believe that I should fix the excel spreadsheet that I load from to solve this (doing this will be quicker than any other way that I know of) but I am not really sure what I need to do to fix it (I put this issue on a Microsoft forum as well), unless someone has another idea that I could try.
Any help would be appreciated…
Thanks,
Mick
ADDENDUM:
Very bizarre, made a copy of the load spreadsheet, went into that spreadsheet, and deleted the entire row (messed up one) of the spreadsheet, saved it, then inserted a new row where that one was (all cells in the row should be clean, right), I inputted the row exactly as it is from the original load spreadsheet, and I got the same result!!! I then tried to input one character at a time and then preview the cell under “Format Cells”, and for some reason, it like gets to a limit and displays the # sign for every entered character to a point. Some other cell entries allow me to type in as many characters as I want, but these cells that have a problem will only let me go so far before the thing turns to #?? It is not consistent either!!! In the example above, as soon as I start typing the character “i” in “invalid” and exit the cell or save it, it goes bad!! Geez Microsoft, how f’d up is that!!!
I guess what I am going to do is to fix all the ones I can on the load spreadsheet, and record the last part of the description in a text file (Word document), and update them once I get them into notes, I tried this with the one above and it worked (got out to the report template without OLE Automation Error!! What a mess!!!