[AccessD] Automation Error Exporting A97 table to Excel

Hi,
I changed it as follows and this worked ok for me.
Cut-n-past the full code below into a new module.
And then set a Reference to Microsoft Excel 11.0 Object Library and then
compile it. It should compile with no errors. 
You will notice that I also changed the order and values somewhat. It posted
the correct values and all the objects into the s/sheet with no errors.
Regards
Max
Ps. I have never done this before, so it is a learning curve for me too.


Option Compare Database
Option Explicit

Function xv()
' set Reference to Microsoft Excel 11.0 Object Library
  Dim dbs As DAO.Database, rst As DAO.Recordset, fld As Field, varValue As
Variant
  Dim lngRecCt As Long, lngFldCt As Long
  Dim TempArray()
  Dim objXL As Object
  Dim objWkb As Workbook
  Dim objSht As Worksheet
  Set objXL = New Excel.Application
  Set dbs = CurrentDb
  Set rst = dbs.OpenRecordset("Select * from tBELnk_MenuWindowModes")
  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
  With objXL
    .Visible = True
    Set objWkb = .Workbooks.Add
    Set objSht = objWkb.Worksheets(1)
    With objSht
      .Range("a1").Resize(lngRecCt + 1, lngFldCt + 1).Value = TempArray
      .Cells.Columns.AutoFit
    End With
  End With

  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 8:37 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [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.'