Xuất dữ liệu Access ra Excel định mẫu sẵn
Nguồn tham khảo: https://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