hi Nora,
Could anyone advise me on how to create an access report for a pivot table
that was created using access.
I tried using subreports but I could not export the pivot table to neither
word nor excel...
Do you want an Access report or an Excel export of it?
The Excel export is quite easy:
Public Sub Export()
On Local Error Resume Next
Dim ea As Object
Dim es As Object
Dim ew As Object
Dim rsData As DAO.Recordset
Dim rsField As DAO.Field
Dim DisplayAlerts As Boolean
Dim SQL As String
Set ea = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set ea = CreateObject("Excel.Application")
ea.Visible = False
End If
On Local Error GoTo LocalError
DisplayAlerts = ea.DisplayAlerts
ea.DisplayAlerts = False
Set ew = ea.Workbooks.Add
ew.Sheets.Item(3).Delete
ew.Sheets.Item(2).Delete
Set es = ew.Sheets.Item(1)
es.Name = "Pivot"
SQL = "SELECT * FROM yourPivotQuery"
Set rsData = CurrentDbC.OpenRecordset(SQL, dbOpenSnapshot)
If Not rsData.BOF And Not rsData.EOF Then
For Each rsField In rsData.fields
es.Range(Chr(65 + rsField.OrdinalPosition) & "1").FormulaR1C1 = _
rsField.Name
Next rsField
es.Range("A2").CopyFromRecordset rsData
End If
rsData.Close
Set rsData = Nothing
es.Columns.AutoFit
ea.DisplayAlerts = DisplayAlerts
ea.Visible = True
Set es = Nothing
Set ew = Nothing
Set ea = Nothing
Exit Sub
LocalError:
MsgBox Err.Description
End Sub
mfG
--> stefan <--
