[AccessD] Automation Error Exporting A97 table to Excel

Hi, Well after wasting some time with this I laid down, put a towel on my
head and instead of thinking what the detailed answer was to the error that
kept coming up, I starting to think laterally and asked myself "What are we
trying to achieve?".  The answer, was of course "Output a Table into Excel"
The answer is so simple.  Here is the single  line of code that solves the
problem.

DoCmd.TransferSpreadsheet acExport, , "tmp_Hold_Source",
"C:\tmp_Hold_Source", True ' change output path to suit.

Regards
Max
Ps. Works for Access V11.0, not sure about 97.
Pps. Does not need a reference to Excel any longer.

  

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of RANDALL R ANTHONY
Sent: Tuesday, September 04, 2007 4:12 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Automation Error Exporting A97 table to Excel

Sorry, Max, that's the whole gist of the situation.  My table contains 13
fields with dates, general data, and 2 memo fields.  Some of the fields,
including the memo fields can have null values.
You must have been working late yesterday, I'm East Coast USA.

>>> "Gmail" <max.wanadoo at gmail.com> 9/4/2007 10:54 AM >>>
What memo fields?  Sorry, I must have come into this a bit late.  I take it
that there are meno fields in your table.  Do they all contain data or are
some of them null?  Is it possible to send me the table off-line? (change
any sensitive bits if you want, but not the essential nature of the table).
Max.wanadoo at gmail.com
Regards
Max
Ps. My time zone is UK.  Where is yours? 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of RANDALL R ANTHONY
Sent: Tuesday, September 04, 2007 3:36 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Automation Error Exporting A97 table to Excel

Max,
Did as directed, changed 8.0 to 11, compiled, etc.  Still receive the object
defined error noted below.
It errors on this line:
.Range("a1").Resize(lngRecCt + 1, lngFldCt + 1).Value = TempArray

I also removed the memo fields from the table and your code works.  So, back
to the problem of memo fields...

>>> "Gmail" <max.wanadoo at gmail.com> 9/3/2007 5:53 PM >>>
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.'

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


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

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

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