paulsteigel > 26-06-13, 10:11 PM
Option Compare Database
Function Export2Excel(Optional qryName As String, Optional qryString As String, Optional ExlFileName As String) As Boolean
'On Error GoTo errHandler
If qryName = "" Then
If qryString = "" Then
MsgBox "Chua co cau lenh Truy van SQL..", vbInformation
GoTo errHandler
End If
' Tao Query tam thoi
CurrentDb.CreateQueryDef "tmp_Qry", qryString
qryName = "tmp_Qry"
End If
If ExlFileName = "" Then ExlFileName = CurrentProject.Path & "\" & qryName & ".xls"
' Xoa file Excel neu da ton tai
If Dir(ExlFileName) <> "" Then Kill ExlFileName
' Xuat ra Excel
'DoCmd.OutputTo acOutputQuery, qryName, acCmdOutputToExcel, ExlFileName
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, qryName, ExlFileName, True
Export2Excel = True
errHandler:
If Export2Excel Then
MsgBox "Da xuat thanh cong ra Excel..Ten tap tin la: [" & vbclrf & ExlFileName, vbInformation
Else
MsgBox "Viec xuat file ra Excel khong thanh cong...", vbInformation
End If
Err.Clear
On Error Resume Next
' xoa Query tam
If qryName = "tmp_Qry" Then DoCmd.DeleteObject acQuery, qryName
End Function
Sub Test()
'Call Export2Excel("Query1")
Call Export2Excel(, "SELECT a.tentg, Count(b.tensach) AS Dausach FROM tacgia AS a INNER JOIN sach AS b ON a.matg=b.matg GROUP BY a.tentg;")
End Sub