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

There are limits on Excel 65K rows 32K per cell varies with version of 
Excel
There is also something about memo fields being truncated at 255 chars 
but there is a workaround.
If the worksheet aleady exists needs terminating "$" in name


Here are a couple of samples methods using ADO.

'? CopyTableToExcel("Products", "C:\Excel\Product2.xls", "C:\Program 
Files\Microsoft Office\Office\Samples\Northwind.mdb")
Public Function CopyTableToExcel(strTable As String, strXLSFile As 
String, strAccessFile As String)
Dim cnSrc As New ADODB.Connection
Dim num_copied As Long

    DoEvents
    strAccessFile = CurrentDb.Name
    cnSrc.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & strAccessFile & ";"
        'pick a sheetname "Books"
    cnSrc.Execute "SELECT * INTO [Excel 8.0;" & _
        "Database=" & strXLSFile & "].[Books] FROM " & _
            strTable, num_copied
    cnSrc.Close

    MsgBox "Copied " & num_copied & " records."
End Function
' Open the database and build the Recordset containing the data
'you want to transfer. Then open the Excel workbook,
'find the worksheet that should contain the data,
'create a Range on the worksheet,
'and use its CopyFromRecordset method to load the data.
'This example also calls AutoFit to make the column widths fit the data.


Function CopyTableToExcelCreate(strTable As String, strXLSFilename As 
String)
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim excel_app As Excel.Application
Dim excel_sheet As Excel.Worksheet

    DoEvents

    ' Open the Access database.
    Set conn = New ADODB.Connection
    conn.ConnectionString = _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & strXLSFilename
    conn.Open

    ' Select the Access data.
    Set rs = conn.Execute(strTable)

    ' Create the Excel application.
    Set excel_app = CreateObject("Excel.Application")

    ' Uncomment this line to make Excel visible.
'    excel_app.Visible = True

    ' Open the Excel workbook.
    excel_app.Workbooks.Open strXLSFilename

    ' Check for later versions.
    If Val(excel_app.Application.Version) >= 8 Then
        Set excel_sheet = excel_app.ActiveSheet
    Else
        Set excel_sheet = excel_app
    End If

    ' Use the Recordset to fill the table.
    excel_sheet.Cells.CopyFromRecordset rs
    excel_sheet.Cells.Columns.AutoFit

    ' Save the workbook.
    excel_app.ActiveWorkbook.Save

    ' Shut down.
    excel_app.Quit
    rs.Close
    conn.Close

    MsgBox "Ok"
End Function

Stephen Bond wrote:

>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 
>
>  
>
>------------------------------------------------------------------------
>
>No virus found in this incoming message.
>Checked by AVG Anti-Virus.
>Version: 7.0.344 / Virus Database: 267.10.17/84 - Release Date: 29/08/2005
>  
>

-- 
Marty Connelly
Victoria, B.C.
Canada