[AccessD] Can't write to an Excel spreadsheet

FYI you can designate the top left cell and execute the paste without having
to designate the entire range. ie.
".Range("A2").CopyFromRecordset Rs1" would work just as well eliminating the
need to determine intMaxRow and intMaxCol.
Jim Hale

-----Original Message-----
From: Jim Moss [mailto:jim.moss at jlmoss.net]
Sent: Friday, January 06, 2006 12:49 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Can't write to an Excel spreadsheet


John,

Here's an example of Excel creation using CopyFromRecordset and
formatting. You should be able bind a control to a cell, just start macro
recording, do your control thing, and save the macro. Then you can copy
the vba into your access module.  You can also use macro recording to
figure out cell locking.

Jim


Set Rs1 = DbA.OpenRecordset("SELECT SupplierName, InvoiceNumber, PONumber,
DueDate, ERP, LocDesc, AmountInUSD, Status, APKey FROM
qrsAPTrialBalanceNorthAmerica WHERE SupplierName = GetSupplierNameLB();",
dbOpenSnapshot)

' CopyFromRecordset code
            intMaxCol = Rs1.Fields.Count
            If Rs1.RecordCount > 0 Then
                Rs1.MoveLast
                Rs1.MoveFirst
                intMaxRow = Rs1.RecordCount
                Set objXL = CreateObject("Excel.Application")
                With objXL
                    .Visible = False
                    Set objWkb = .Workbooks.Add
                    Set objSht = objWkb.Worksheets(1)
                        With objSht
                            .Range(.cells(2, 1), .cells(intMaxRow, _
                            intMaxCol)).CopyFromRecordset Rs1
                        End With
                        With objSht
                            .Columns("G:H").select
                            .Columns.NumberFormat = "#,##0.00"
                        End With
                        With objSht
                            .Columns("M:M").select
                            .Columns.NumberFormat = "#,##0.00"
                        End With
               End With
            End If
           

***********************************************************************
The information transmitted is intended solely for the individual or
entity to which it is addressed and may contain confidential and/or
privileged material. Any review, retransmission, dissemination or
other use of or taking action in reliance upon this information by
persons or entities other than the intended recipient is prohibited.
If you have received this email in error please contact the sender and
delete the material from any computer. As a recipient of this email,
you are responsible for screening its contents and the contents of any
attachments for the presence of viruses. No liability is accepted for
any damages caused by any virus transmitted by this email.