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

Stephen,

The following code does a copy from recordset:

intMaxCol = rs1.Fields.Count
            If rs1.RecordCount > 0 Then
                rs1.MoveLast
                rs1.MoveFirst
                intMaxRow = rs1.RecordCount
                Set objXL = New Excel.Application
                With objXL
                    .Visible = False
                    Set objWkb = .Workbooks.Add
                    Set objSht = objWkb.Worksheets(1)
                        With objSht
                            .Range(.Cells(2, 1), .Cells(intMaxRow, _
                            intMaxCol)).CopyFromRecordset rs1
                        End With
                End With
            End If

Jim


> 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
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>