query export to xml (repeating and non-repeating elements)

[SirGibble]

I want to export the data from a query into a presentable excel document. It seems the best way to do this is with xml but xml assumes that all of my data is of the repeating element type and I need the data from some fields to only appear once.
Is there a way to specify which fields of my query should not repeat? Is there another way to achieve this?​

 

[DCrake]

Here is a link to an MS article on transfering data from Access to Excel
http://support.microsoft.com/kb/247412

David

 

[Atomic Shrimp]

The best way to get your data into a presentable excel document is to use automation to directly control the push of data into the cells you want, and directly control their formatting, etc.

An excellent primer on this is here:
http://www.mvps.org/access/modules/mdl0006.htm

Some more advanced stuff, based on the same ideas:
http://www.mvps.org/access/modules/mdl0035.htm

 

[SirGibble]

Thanks for your replies - just what I was looking for.

 

[SirGibble]

So far I have successfully exported my query data to a excel sheet by putting an arbitrary id number in the criteria of one of the columns of my query.

Now I am trying to pass that same id number from a form that I have open it is giving me a run-time error '3061': Too few parameters. Expedcted 1. at this line of my code:
Set rs = CurrentDb.OpenRecordset("qry_exportRS", dbOpenSnapshot)

Can anyone help?

This is the expression I am using in the criteria of my query [Forms]![frm_Contracts]![sfm_Contract_Details].[Form]![contract_detail_id]

and here is the complete code:

Private Sub Command9_Click()
'Send records to the first
'sheet in a new workbook
'
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Dim rs As Recordset
Dim intMaxCol As Integer
Dim intMaxRow As Integer
Dim oBookName As String

Set rs = CurrentDb.OpenRecordset("qry_exportRS", dbOpenSnapshot)
intMaxCol = rs.Fields.Count
If rs.RecordCount > 0 Then
rs.MoveLast: rs.MoveFirst
intMaxRow = rs.RecordCount

Set oExcel = CreateObject("Excel.Application")
With oExcel
.Visible = True

Set oBook = oExcel.Workbooks.Add

Set oSheet = oBook.Worksheets(1)
With oSheet
.Range(.Cells(1, 1), .Cells(intMaxRow, _
intMaxCol)).CopyFromRecordset rs
End With
End With
End If

oBookName = oBook.Worksheets(1).Range("a1").Value

'Save the Workbook and Quit Excel
oBook.SaveAs "C:\Documents and Settings\cgibb\Desktop\itd_" & oBookName & ".xls"
oExcel.Quit
End Sub

 

[gemma-the-husky]

try

dim dbs as database

set dbs=currentdb

set rst = dbs.openrecordset etc

----------
you often get issues with persisitence of the currentdb object for sonme reason - so i always do this now - perhaps this will help.

 

[SirGibble]

Unfortunately this didn't work. Any other ideas?

 

[SirGibble]

I solved this problem by using querydefs

Set db = CurrentDb
Set qdf = db.QueryDefs("qry_exportRS")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rs = qdf.OpenRecordset(dbOpenDynaset)

 

[boblarson]

Actually all you likely needed was to add

DAO to the declaration

Dim rs As DAO.Recordset

instead of

Dim rs As Recordset