[AccessD] Automation Error Exporting A97 table to Excel

Max,
This seems to create the array nicely, however when I try to load it to the spreadsheet I get the below.  The code is below.

'Application-defined or object defined error.'

        Dim objXL As Object
        Dim objWkb As Workbook
        Dim objSht As Worksheet
        Set objXL = New Excel.Application
        With objXL
          .Visible = True
          Set objWkb = .Workbooks.ADD
          Set objSht = objWkb.Worksheets(1)
          With objSht
            
            .Cells.Columns.AutoFit
           .Range("a1").Resize(lngRecCt, _
                lngFldCt).Value = TempArray
          End With
        End With

>>> "Gmail" <max.wanadoo at gmail.com> 9/3/2007 2:54 PM >>>
Hi,
You could try this code.
Regards
Max
Function xv()
  Dim dbs As DAO.Database, rst As DAO.Recordset, fld As Field, varValue As
Variant
  Dim lngRecCt As Long, lngFldCt As Long
  Dim TempArray()
  Set dbs = CurrentDb
  Set rst = dbs.OpenRecordset("Select * from tBE_SystemForms")' your table
name
  rst.MoveLast
  lngRecCt = rst.RecordCount
  lngFldCt = rst.Fields.Count
  ReDim TempArray(lngRecCt, lngFldCt)
  lngRecCt = 0
  rst.MoveFirst
  Do While Not rst.EOF
    lngRecCt = lngRecCt + 1: lngFldCt = 0
    For Each fld In rst.Fields
      lngFldCt = lngFldCt + 1
      If IsNull(fld.Value) Then
        varValue = "" ' or 0 (zero) if that is what you want)
      Else
        varValue = fld.Value
      End If
      TempArray(lngRecCt, lngFldCt) = varValue
      Debug.Print lngRecCt, lngFldCt, TempArray(lngRecCt, lngFldCt)
    Next fld
    rst.MoveNext
  Loop
  Set dbs = Nothing: Set rst = Nothing: Set fld = Nothing
End Function 

-----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 7:39 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Automation Error Exporting A97 table to Excel

Some progress, as I see that if I delete the two memo fields in the source
table, the code I first presented works great.  So, how do I get those memo
fields in there?  Thanks again.

>>> "RANDALL R ANTHONY" <RRANTHON at sentara.com> 9/3/2007 1:04 PM >>>
John,
I just got a hit googling (natch, after coming up empty for two hours) and
this one is using a temp array to strip out null values?  I'll pursue this
course and see what comes up.

>>> "John Skolits" <askolits at nni.com> 9/3/2007 12:44 PM >>>
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 ( http://www.databaseadvisors.com/ )

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


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


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

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