Excel Automation: CopyFromRecordset - what other commands are available?

S

Stapes

Hi

I want to be able to process each record in the recordset
individually, and output one line of excel data at a time. This is
because I want to sort my data into Company, Currency, Matured / Yet
to Mature and Sales / Purchases, with group footers after each section
in my spreadsheet and I want a different worksheet for each company.
Is there a command like CopyFromRecordset that just does individual
records?

Stapes
 
A

Arvin Meyer [MVP]

Not that I know of, but you can limit the recordset to 1 or a few records at
a time by grabbing only those cells/rows in the range that you want.
 
P

Pieter Wijnen

Oh yes there is
You can create a (temporary) Query for each company & Do a
TransferSpreadSheet
Query: SingleCompany:
Parameters Forms!myForm!CompanyID Long;
SELECT C.* FROM MyTableOrQuery C
WHERE C.CompanyID=Forms!myForm!CompanyID

Sub OutPutCompanies()
Dim Db As DAO.Database
Dim QdfBase As DAO.QueryDef, QdfTemp As DAO.QueryDef
Dim RsCo As DAO.Recordset
Dim CID As Access.TextBox

Set Db = Access.CurrentDb
Set CID = Access.Forms!MyForm!CompanyID
Set QdfBase = Db.QueryDefs("SingleCompany")
Set RsCo = Db.OpenRecordset("Select CompanyID, CompanyName FROM Company
Order By 2", DAO.DbOpenSnapshot)
While Not RsCo.EOF
CID.Value = Rs.Fields(0).Value
On Error Resume Next
set QdfTemp = Nothing
Set QdfTemp=Db.CreateQueryDef(Rs.Fields(1).Value,QdfBase.SQL) ' Might
have to do some Replaces for invalid characters
If QdfTemp Is Nothing Then
Set QdfTemp=Db.QueryDefs(Rs.Fields(1).Value)
QdfTemp.SQL = QdfBase.SQL ' In case you've changed it
End If
On Error Goto 0
' the next line not really neccessary, but what the heck
QdfTemp.Parameters(0).Value = CID.Value ' or
Access.Eval(QdfTemp.Parameters(0).Name)
Access.DoCmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel97,
QdfTemp.Name,"C:\MyFile.xls"
QDef.Close
Db.QueryDefs.Delete QdfTemp.Name
Rs.MoveNext
Wend
Rs.Close : Set Rs = Nothing
Set Db = Nothing
End Sub

HTH (bar my usual typos)

Pieter
 
Top