Đam mê và chia sẻ

Xuất dữ liệu Access ra Excel định mẫu sẵn

Nguồn tham khảo: http://www.giaiphapexcel.com/forum/showthread.php?73627-Gi%C3%BAp-chuy%E1%BB%83n-b%C3%A1o-c%C3%A1o-t%E1%BB%AB-d%E1%BA%A1ng-Access-sang-Excel

Code xuất sang excel:

Private Sub Command31_Click()
On Error Resume Next
Dim db As DAO.Database, rs As DAO.Recordset, mySQL As String, nYear As Long
Dim oApp As New Excel.Application, oBook As Excel.Workbook, oSheet As Excel.Worksheet
nYear = InputBox("Vui long nhap nam can trich loc.", "Nhap nam", Year(Now()))
If nYear = 0 Then Exit Sub

    Set oBook = oApp.Workbooks.Open(CurrentProject.Path & "\Temp.xlt")
    mySQL = "SELECT NMNVOCANH.Ngay, NMNVOCANH.NuocTho, NMNVOCANH.NuocSX, NMNVOCANH.ThatThoat, NMNVOCANH.DienTT, NMNVOCANH.BinhQuan, " & _
                    "NMNVOCANH.M1, NMNVOCANH.M2, NMNVOCANH.M3, NMNVOCANH.M4, NMNVOCANH.M5, NMNVOCANH.P1, NMNVOCANH.P2, NMNVOCANH.P3," & _
                    "NMNVOCANH.P4, NMNVOCANH.P5, NMNVOCANH.P6, NMNVOCANH.P7, NMNVOCANH.P8, NMNVOCANH.Cong, NMNVOCANH.Phen, NMNVOCANH.BQPhen, " & _
                    "NMNVOCANH.Voi, NMNVOCANH.BQVoi, NMNVOCANH.Clo, NMNVOCANH.BQClo " & _
                    "FROM NMNVOCANH " & _
                    "WHERE Year([ngay]) = " & nYear

    Set oSheet = oBook.Sheets("BaoCao")
    Set db = CurrentDb
    Set rs = db.OpenRecordset(mySQL, dbOpenSnapshot)
    
        oSheet.Range("A15").CopyFromRecordset rs
        MsgBox "Da xuat xong du lieu sang Excel", vbExclamation, "Hai Lua Mien Tay"
         
    rs.Close: Set rs = Nothing
    oApp.Visible = True
    db.Close: Set db = Nothing
    
    
End Sub

File Demo: app.box.com/s/d8ws96f2x2cp2jltghkv

Search site

© 2012 All rights reserved.