[AccessD] Data from temp table being rammed into an Excel spreadsheet

Thanks Jim and Marty,
 
As a quick fix I had coded a bypass to the problem  (the customer is across the ditch in Australia and needed some sort of resolution immediately, even a temporary one), BUT left the original code in there as well, but with an elegant message if the error occurred.
 
Now they report the original problem has gone away.  Completely.  Go figger ....
 
At the risk of plagiarising a line from Woody's Watch ... 'trustworthy computing!'  Bah humbug.
 
Anyway, your submissions have been filed for future reference.  Thanks again.
 
Stephen Bond
 
 

-----Original Message-----
From: Stephen Bond 
Sent: Tuesday, 30 August 2005 9:02 a.m.
To: accessd at databaseadvisors.com
Subject: [AccessD] Data from temp table being rammed into an Excel spreadsheet



In A2K I have built a temp table and am trying to then export it to a spreadsheet using the following line of code: 
  
  objXLRange.FormulaArray = objXLApp.Transpose(varResults) 

where 'Dim objXLApp As Excel.Application ' 
and 'Dim objXLRange As Excel.Range' and has been defined precisely, according to the size of the incoming data in the temp table,

and varResults is a 2-dimension array being the result of SELECT * FROM tmpTable.  The column count is fixed at 18 but the Row count is variable.


This works well when the temp table has 303 rows or less.  Over 303 rows, and I get 'Error 13 Type Mismatch'.  I have looked for corrupted data coming in thru the query that creates the temp table, but after many hours of experimentation it comes back to an apparent limit on the number of Rows.


I don't remember where I got the code from and I don't pretend to understand the 'FormulaArray' property in Excel, but someone here might have been down this track .... any takers?


  
Stephen Bond