1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40
| Private Sub Export_Click()
Dim Rep_Export, Criteria, Column As String
Column = InputBox("Please select your column", "Column selection")
CriteriaName = InputBox("" & Column & "= ?", "Criteria selection")
Criteria = Chr(34) & CriteriaName & Chr(34)
Dim db As DAO.Database
Dim qry, qryD As DAO.QueryDef
Dim strConnect As String
Dim strSQL As String
Set db = CurrentDb
'Finding the connexion path to the current table
strConnect = db.QueryDefs("Export").Connect
'Copy the SQL model of the main query
strSQL = db.QueryDefs("Export").SQL
'replace Column and Criteria by the new values defined at the beginning
strSQL = Replace(strSQL, "Column", "" & Column)
strSQL = Replace(strSQL, "Criteria", "" & Criteria & "")
'New query created
Set qry = db.CreateQueryDef("ExportTemp", strSQL)
'Connect the new query to the current table path
qry.Connect = strConnect
'Export selected data with the query to a new XL file named: "Export 'Criteria' (MM/DD/YYYY HH.MM).xls"
DoCmd.OutputTo acOutputQuery, "ExportTemp", "Excel97-Excel2003Workbook(*.xls)", "Export " & CriteriaName & _
" (" & Month(Now) & "." & Day(Now) & "." & Year(Now) & " " & Hour(Time) & "h." & Minute(Time) & ") .xls"
'Seeking all the queries
For Each qryD In db.QueryDefs
With qryD
'Seeking query named "ExportTemp": we want to suppress the query created earlier
If .Name = "ExportTemp" Then
'querry found, we suppress it and stop the For
db.QueryDefs.Delete "ExportTemp"
Exit For
End If
End With
Next qryD |
Partager