Could someone please explain why I get a spreadsheet full error when exporting report data into .xls and the amount of rows isn’t anywhere near excel’s limitation?
rich308 (BOB member since 2004-04-01)
How much data are you exporting? I believe there is a Microsoft flaw in MDAC that still limits the auto creation of spreadsheets to 32K rows (even thought Excel can handle 64K rows).
The easiest thing to do (and faster) is to export as text and open the text file in the spreadsheet. If you use a tab as the delimiter and name the file with a .tab extension, Excel will simply open it up. At least it does on my machine. You will bypass the 32K limitation, I think.
Steve Krandel 
(BOB member since 2002-06-25)
It was actually something like 26k rows. I’ve been exporting as .txt and opening in excel. The only issue with that is that any null fields get populated with an ‘#EMPTY’.
rich308 (BOB member since 2004-04-01)
Just a couple of clarifications. There are two Excel options for exporting. The one simply listed as Excel is actually older (assuming for VERY backward compatibility) and is limited to 16,384 rows. The one listed as Excel 97 is the current one and is limited to the expected 65,535 rows.
As far as text file exports, specifying an extension of .txt will trigger the tab-delimited format. If you choose “all files” as the format, you can control the extension and the delimiter if you like.
I wrote a rather extensive paper on all of this recently (integrating BusObj as a data source for Excel / Access / ADO). 
Hmmm … sounds like a possible topic for the next international user conference
Dwayne Hoffpauir (BOB member since 2002-09-19)
The literal #Empty in the export is a problem. Another one that we ran across is that occasionally the microcube contains a numeric value in scientific notation (like 7e-005) … perfectly valid in both BusObj and Excel. Unfortunately, the data provider export will “force” numerics to floating point by appending a “.00” to the end of any values that don’t contain a decimal. 
Well, Excel doesn’t like the “7e-005.00” as a numeric amount … imagine that!
The solution is to create a simple report (the wizard works well, just remove the default title cell) and format it the way you like. Make null cells display zero, for example. Then, export the report tab instead of the data provider. Just do Save As and choose Text files … voila, a properly formatted, tab-delimited text file!
Dwayne Hoffpauir (BOB member since 2002-09-19)
Since you didn’t respond if the suggestions provided fixed your problem, I just wanted to throw one other idea out. If you are getting a bunch of “null” rows in BO, put a Defined filter on any field that has the nulls as below:
=Not IsNull(<yourField>)
That should force BO to display only rows with data and may help with the export problem.
Joel Seguin (BOB member since 2004-12-08)
Can you please let me know where I can access the paper about integrating BusObj as a data source for Excel / Access / ADO.
suraj (BOB member since 2005-01-12)
Thank you very much for your time and concern.
suraj (BOB member since 2005-01-12)
The white paper mentioned in this topic has been moved to the BOB’s Downloads forum and can be found here.
Dwayne Hoffpauir (BOB member since 2002-09-19)