[AccessD] Automation Error Exporting A97 table to Excel

You're in luck (I think).

I spent a good day trying to figure out a similar problem. 

Check your data types. I think I had problems with date/time fields that
were blank. Also remove any null values in a numeric fields. Change then to
zeros. You can do that using the NZ function.

Let me know if it fixes the problem.

John Skolits




-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of RANDALL R ANTHONY
Sent: Monday, September 03, 2007 12:30 PM
To: accessd at databaseadvisors.com
Subject: [AccessD] Automation Error Exporting A97 table to Excel

Greetings all,
I'm trying to do something fairly simple here, export a table to an excel
spreadsheet.  Tried one method, didn't work.
Copied Dev's export function below.  It works perfectly fine until the
CopyFromRecordset hits the fourth record, and gives me an 'Automation Error,
Method 'CopyFromRecordset' of object 'Range' failed.'  Any ideas?  M$'s KB
and googling have been fruitless.  Thanks!

 'copyright - dev ashish
    Dim objXL As Excel.Application
    Dim objWkb As Workbook
    Dim objSht As Worksheet
      Set rs = CurrentDb.OpenRecordset(sSQL, _
                        dbOpenSnapshot)
      iMaxCol = rs.Fields.Count
      If rs.RecordCount > 0 Then
        rs.MoveLast:    rs.MoveFirst
        iMaxRow = rs.RecordCount
        Set objXL = New Excel.Application
        With objXL
          .Visible = True
          Set objWkb = .Workbooks.ADD
          Set objSht = objWkb.Worksheets(1)
          With objSht
            
            .Cells.Columns.AutoFit
            .Range(.Cells(1, 1), .Cells(iMaxRow, _
                iMaxCol)).CopyFromRecordset rs
          End With
        End With
    End If


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com