[AccessD] Automation Error Exporting A97 table to Excel

Actually, try to break the memo filed into 255 character chunks. I know it's
not the cleanest approach but it might work. Create a query against the
table. Use that query instead of the table to create the recordset. Include
all the fields except the memo field.  For the memo field try.

Expr1: Mid([FieldName],1,255) & Mid([FieldName],256,255) &
mid([FiledName],510,255) ..... Etc.


The only problem is at some point you have to decide how far you're going to
go repeating the 'Mid' function.

I bet there is another way. But try this and see if you cam make progress
with it.





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

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