Hi Sam
The simple answer is to create a query returning the fields you want in your
spreadsheet, and with a selection clause to limit the records selected - for
example, [solde]>100.
You can the use the TransferSpreadsheet method to export the results of the
query to Excel.
If you want to vary the selection criteria, or be a bit fancier, you could
use the function below. Just pass it the name of a query of a SQL string,
the name of the output file, and True if you want to leave the resulting
workbook open without saving it.
For example:
ExportToExcel "Select * from MyQuery where [solde]>100", _
"C:\My Folder\MyFile.xls"
--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand
========== start code ============
Public Function ExportToExcel(sQuery As String, _
sFileName As String, _
Optional fPreview As Boolean)
Dim oXL As Excel.Application
Dim rs As DAO.Recordset, i As Integer
On Error GoTo ProcErr
Set rs = CurrentDb.OpenRecordset(sQuery)
' Start Excel
Set oXL = CreateObject("Excel.Application")
With oXL
.SheetsInNewWorkbook = 1
.Workbooks.Add.Sheets(1).Activate
With .ActiveSheet
' Create a headings row with the field names
For i = 1 To rs.Fields.Count
.Cells(1, i) = rs(i - 1).NAME
Next
.Rows(1).Font.Bold = True
.Rows(1).HorizontalAlignment = xlCenter
' Copy the data from the recordset
.Cells(2, 1).CopyFromRecordset rs
' Autofit the columns
.UsedRange.Columns.AutoFit
End With
' This freezes the headings row to prevent it scrolling
With .ActiveWindow
.SplitRow = 1
.FreezePanes = True
End With
' Either show the result or save and quit
If fPreview Then
.UserControl = True
.Visible = True
Else
.ActiveWorkbook.SaveAs sFileName
.Quit
End If
End With
Set oXL = Nothing
ProcEnd:
On Error Resume Next
rs.Close
If Not oXL Is Nothing Then
' we had an error - quit Excel without saving
oXL.DisplayAlerts = False
oXL.Quit
Set oXL = Nothing
End If
Exit Function
ProcErr:
MsgBox Err.Description
Resume ProcEnd
End Function
======== end code ===========
