[AccessD] Automation Error Exporting A97 table to Excel

Tue Sep 4 12:09:32 CDT 2007

Ooops, sorry. I didn't mean it to read that way. Merely that, generally
speaking, we work quite long hours in the UK and that I  will probably be
able to respond even if you are 5 hrs behind in time zones!
Max

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

Oh Boy! That P.S. may open a can of worms.

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

Can you send the table?  Don't need it all, just some sample rows which
included the ones that the  program chocked on.
Max
Ps. I always start early and finish late.  We do that in the UK.

-----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

--
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