• Tùy chọn
• Đăng nhập
• Đăng ký
• Xem như trên Máy tính
• Accelerated Mobile Pages Theme

Xuất dữ liệu từ report ra Excel theo mẫu

• Trang chủ

  • Thủ Thuật Access
  • Access Nâng Cao
  • Tương tác Chương trình ngoài
  • [Help] Xuất dữ liệu từ report ra Excel theo mẫu
• Xuất dữ liệu từ report ra Excel theo mẫu

• Xuất dữ liệu từ report ra Excel theo mẫu

  xiketuan > 19-07-20, 10:07 PM

  Xin chào các bạn,
  mình cập nhật dữ liệu từ form và muốn xuất ra Excel theo mẫu Report. Các bạn có code demo xuất dữ liệu ra file Excel giống như report đã tạo, các cột trong report đã có công thức tính toán từ form (các công thức ở code vba). Nếu mình xuất ra được excel theo mẫu, thì có thể dùng file excel khi có kết quả import lại vào data theo mẫu đã xuất ra. Nếu được thì sẽ giảm tải rất nhiều công việc ạ.
  
  Cảm ơn rất nhiều.
  Mình gửi hình demo
  
  
  

• RE: Xuất dữ liệu từ report ra Excel theo mẫu

  ongke0711 > 19-07-20, 11:28 PM

  Access chỉ xuất dữ liệu ra Excel từ nguồn là Table hoặc Query. Do đó các tính toán gì đó trên Form hay Report nên lưu thành table tạm hoặc query rồi xuất. Xuất theo kiểu mặc định này sẽ không theo mẫu thiết kế sẳn của Excel, chỉ có định dạng như Table thôi và xuất kiểu này mới có bộ code xuất dùng chung. Muốn xuất theo một mẫu thiết kế nào đó phải viết code riêng cho từng mẫu biểu của Excel.
  
  

  Mã PHP:

  Function DataToExcel(strSourceName As String, Optional strWorkbookPath As String, Optional strTargetSheetName As String)

    Dim rst As DAO.Recordset
    Dim excelApp As Object
    Dim Wbk As Object
    Dim Sht As Object
    Dim xlRng As Object
    Dim fldHeadings As DAO.Field

    'Lay du lieu cho recordset (table hoac query).
    Set rst = CurrentDb.OpenRecordset(strSourceName)

    'Tao phiên làm viec Excel moi.
    Set excelApp = CreateObject("Excel.Application")

    On Error Resume Next

    'Try to open the specified workbook. If there is no workbook specified
    '(or if it cannot be opened) create a new one and rename the target sheet.
    Set Wbk = excelApp.Workbooks.Open(strWorkbookPath)
    If Err.Number <> 0 Or Len(strWorkbookPath) = 0 Then
        Set Wbk = excelApp.Workbooks.Add
        Set Sht = Wbk.Worksheets("Sheet1")
        If Len(strTargetSheetName) > 0 Then
            Sht.Name = Left(strTargetSheetName, 34)
        End If
    End If

    'If the specified workbook has been opened correctly, then in order to avoid
    'problems with other sheets that might contain, a new sheet is added and is
    'being renamed according to the strTargetSheetName.
    Set Sht = Wbk.Worksheets.Add
    If Len(strTargetSheetName) > 0 Then
        Sht.Name = Left(strTargetSheetName, 34)
    End If

    On Error GoTo 0

    excelApp.Visible = True

    On Error GoTo ErrorHandler

    'Copy dòng tiêu de.
    For Each fldHeadings In rst.Fields
        excelApp.ActiveCell = fldHeadings.Name
        excelApp.ActiveCell.Offset(0, 1).Select
    Next

    'Copy du lieu xuong sheet.
    rst.MoveFirst
    Sht.range("A2").Select
    Sht.range("A2").CopyFromRecordset rst
    Sht.range("1:1").Select

    'Dinh dang dòng tiêu de.
    excelApp.Selection.Font.Bold = True
    With excelApp.Selection
        .HorizontalAlignment = -4108    '= xlCenter in Excel.
        .VerticalAlignment = -4108  '= xlCenter in Excel.
        .WrapText = False
    End With

    Set xlRng = Sht.UsedRange
    With xlRng
        .Font.Name = "Verdana"
        .Font.Size = 8
        With .Borders
            .ColorIndex = -4105    'xlAutomatic
            .LineStyle = 1    'xlContinuous
            .Weight = 2  'xlThin
        End With
        With .Rows(1)
            .Font.Bold = True
            .Interior.ColorIndex = 15
        End With
        .WrapText = False
        .EntireColumn.AutoFit
    End With

    excelApp.ActiveSheet.Cells.EntireColumn.AutoFit

    With excelApp.ActiveWindow
        .FreezePanes = False
        .ScrollRow = 1
        .ScrollColumn = 1
    End With
    Sht.Rows("2:2").Select
    excelApp.ActiveWindow.FreezePanes = True

    With Sht
        .Tab.Color = RGB(255, 0, 0)
        .range("A1").Select
    End With

    rst.Close
    Set rst = Nothing

    Exit Function

ErrorHandler:
    Msgbox "Ma loi: " & Err.Number & vbNewLine & "Noi dung loi: " & Err.Description, vbExclamation, "Thong bao"
    Exit Function

End Function 


• RE: Xuất dữ liệu từ report ra Excel theo mẫu

  xiketuan > 19-07-20, 11:37 PM

  (19-07-20, 11:28 PM)ongke0711 Đã viết: Access chỉ xuất dữ liệu ra Excel từ nguồn là Table hoặc Query. Do đó các tính toán gì đó trên Form hay Report nên lưu thành table tạm hoặc query rồi xuất. Xuất theo kiểu mặc định này sẽ không theo mẫu thiết kế sẳn của Excel, chỉ có định dạng như Table thôi và xuất kiểu này mới có bộ code xuất dùng chung. Muốn xuất theo một mẫu thiết kế nào đó phải viết code riêng cho từng mẫu biểu của Excel.

  Đúng rồi bạn, mình thiết kế command button xuất file sang excel từ dữ liệu query. Ví dụ chỉ một mẫu biểu excel như bảng điểm, như vậy mỗi lần xuất file thì lưu 1 tên riêng theo mẫu mà mình code đúng không bạn? Có thể giúp cho mình code demo với ạ.

• RE: Xuất dữ liệu từ report ra Excel theo mẫu

  ongke0711 > 19-07-20, 11:42 PM

  (19-07-20, 11:37 PM)xiketuan Đã viết: Đúng rồi bạn, mình thiết kế command button xuất file sang excel từ dữ liệu query. Ví dụ chỉ một mẫu biểu excel như bảng điểm, như vậy mỗi lần xuất file thì lưu 1 tên riêng theo mẫu mà mình code đúng không bạn? Có thể giúp cho mình code demo với ạ.

  
  Cái hàm xuất ở trên rồi đó, bạn chỉ cần cung cấp các tham số cho hàm rồi chạy thôi.
  Bạn test với CSDL của bạn đi. Nếu không áp dụng được bạn đưa dữ liệu mẫu của bạn lên đây, tôi xem bạn áp dụng sai ở đoạn nào rồi sửa.

• RE: Xuất dữ liệu từ report ra Excel theo mẫu

  hmhieu > 01-05-21, 03:49 PM

  (19-07-20, 11:28 PM)ongke0711 Đã viết: Access chỉ xuất dữ liệu ra Excel từ nguồn là Table hoặc Query. Do đó các tính toán gì đó trên Form hay Report nên lưu thành table tạm hoặc query rồi xuất. Xuất theo kiểu mặc định này sẽ không theo mẫu thiết kế sẳn của Excel, chỉ có định dạng như Table thôi và xuất kiểu này mới có bộ code xuất dùng chung. Muốn xuất theo một mẫu thiết kế nào đó phải viết code riêng cho từng mẫu biểu của Excel.
  
  

  Mã PHP:

  Function DataToExcel(strSourceName As String, Optional strWorkbookPath As String, Optional strTargetSheetName As String)

    Dim rst As DAO.Recordset
    Dim excelApp As Object
    Dim Wbk As Object
    Dim Sht As Object
    Dim xlRng As Object
    Dim fldHeadings As DAO.Field

    'Lay du lieu cho recordset (table hoac query).
    Set rst = CurrentDb.OpenRecordset(strSourceName)

    'Tao phiên làm viec Excel moi.
    Set excelApp = CreateObject("Excel.Application")

    On Error Resume Next

    'Try to open the specified workbook. If there is no workbook specified
    '(or if it cannot be opened) create a new one and rename the target sheet.
    Set Wbk = excelApp.Workbooks.Open(strWorkbookPath)
    If Err.Number <> 0 Or Len(strWorkbookPath) = 0 Then
        Set Wbk = excelApp.Workbooks.Add
        Set Sht = Wbk.Worksheets("Sheet1")
        If Len(strTargetSheetName) > 0 Then
            Sht.Name = Left(strTargetSheetName, 34)
        End If
    End If

    'If the specified workbook has been opened correctly, then in order to avoid
    'problems with other sheets that might contain, a new sheet is added and is
    'being renamed according to the strTargetSheetName.
    Set Sht = Wbk.Worksheets.Add
    If Len(strTargetSheetName) > 0 Then
        Sht.Name = Left(strTargetSheetName, 34)
    End If

    On Error GoTo 0

    excelApp.Visible = True

    On Error GoTo ErrorHandler

    'Copy dòng tiêu de.
    For Each fldHeadings In rst.Fields
        excelApp.ActiveCell = fldHeadings.Name
        excelApp.ActiveCell.Offset(0, 1).Select
    Next

    'Copy du lieu xuong sheet.
    rst.MoveFirst
    Sht.range("A2").Select
    Sht.range("A2").CopyFromRecordset rst
    Sht.range("1:1").Select

    'Dinh dang dòng tiêu de.
    excelApp.Selection.Font.Bold = True
    With excelApp.Selection
        .HorizontalAlignment = -4108    '= xlCenter in Excel.
        .VerticalAlignment = -4108  '= xlCenter in Excel.
        .WrapText = False
    End With

    Set xlRng = Sht.UsedRange
    With xlRng
        .Font.Name = "Verdana"
        .Font.Size = 8
        With .Borders
            .ColorIndex = -4105    'xlAutomatic
            .LineStyle = 1    'xlContinuous
            .Weight = 2  'xlThin
        End With
        With .Rows(1)
            .Font.Bold = True
            .Interior.ColorIndex = 15
        End With
        .WrapText = False
        .EntireColumn.AutoFit
    End With

    excelApp.ActiveSheet.Cells.EntireColumn.AutoFit

    With excelApp.ActiveWindow
        .FreezePanes = False
        .ScrollRow = 1
        .ScrollColumn = 1
    End With
    Sht.Rows("2:2").Select
    excelApp.ActiveWindow.FreezePanes = True

    With Sht
        .Tab.Color = RGB(255, 0, 0)
        .range("A1").Select
    End With

    rst.Close
    Set rst = Nothing

    Exit Function

ErrorHandler:
    Msgbox "Ma loi: " & Err.Number & vbNewLine & "Noi dung loi: " & Err.Description, vbExclamation, "Thong bao"
    Exit Function

End Function 


  
  Đã test, OK!
  Có 2 vấn đề mình cần trợ giúp:
  
  1) Bây giờ mình muốn thêm 1 cột STT (cột đầu tiên của bảng) và tự động đánh số thứ tự từ 1 đến hết dữ liệu xuất ra thì phải làm sao?
  2) Hàm trên chỉ chạy trên Table hoặc Query không có điều kiện, Khi mình cho chạy query có điều kiện truyền vào thì nó báo lỗi tại dòng Set rst = CurrentDb.OpenRecordset(strSourceName)
  
  Lỗi Run-time (3061): Too few parameters. Expected 2.
  
  Vậy để gọi được Query có điều kiện truyền vào từ ngoài thì sửa như thế nào? 
  
  Mong được giúp!

• RE: Xuất dữ liệu từ report ra Excel theo mẫu

  ongke0711 > 01-05-21, 11:29 PM

  (01-05-21, 03:49 PM)hmhieu Đã viết: 1) Bây giờ mình muốn thêm 1 cột STT (cột đầu tiên của bảng) và tự động đánh số thứ tự từ 1 đến hết dữ liệu xuất ra thì phải làm sao?
  

  
  
  Về đánh số thứ tự có thể đánh số thứ tự trong query trước khi xuất Excel. Tham khảo nhiều cách đánh Stt trong link này: https://thuthuataccess.com/forum/post-27113.html#pid27113
  
  
  

  (01-05-21, 03:49 PM)hmhieu Đã viết:
  2) Hàm trên chỉ chạy trên Table hoặc Query không có điều kiện, Khi mình cho chạy query có điều kiện truyền vào thì nó báo lỗi tại dòng Set rst = CurrentDb.OpenRecordset(strSourceName)
  
  Lỗi Run-time (3061): Too few parameters. Expected 2.
  
  Vậy để gọi được Query có điều kiện truyền vào từ ngoài thì sửa như thế nào? 

  
  Đúng là nếu là Query có tham số (parameters) thì nó báo lỗi nếu chỉ khai báo tên Query. Có 2 cách đối với Query có tham số:
  
  1. Dùng luôn câu lệnh SQL của Query đó. Trong hàm trên tham số strSourceName là dạng chuỗi (String), bạn chỉ cần copy câu lệnh SQL của query đó đưa vào biến chuỗi, sau đó chỉnh sửa đoạn nào có tham số lấy từ form cho phù hợp là xong.
  Vd: 
  

  Mã PHP:

  strSourceName = " SELECT * FROM Table1 WHERE ID =" & Me.txtID
Set rst = CurrentDb.OpenRecordset (strSourceName) 


  
  2. Nếu muốn dùng luôn tên Query mà không dùng câu lệnh SQL thì bạn phải khai báo lại tham số parameters trong code VBA nó mới hiểu.
  Vd: 
  
  

  Mã PHP:

  Dim qdf As DAO.QueryDefs
Set qdf = CurrentDb.QueryDefs("qryCongNo")
    qdf("[Forms]![frmBaoCaoTongHop]![txtTuNgay]") = [Forms]![frmBaoCaoTongHop]![txtTuNgay]
    qdf("[Forms]![frmBaoCaoTongHop]![txtDenNgay]") = [Forms]![frmBaoCaoTongHop]![txtDenNgay]
    qdf("[Forms]![frmBaoCaoTongHop]![cboKhachHang") = [Forms]![frmBaoCaoTongHop]![cboKhachHang]
    Set rst = qdf.OpenRecordset() 


• RE: Xuất dữ liệu từ report ra Excel theo mẫu

  hmhieu > 02-05-21, 03:53 PM

  (01-05-21, 11:29 PM)ongke0711 Đã viết:

  (01-05-21, 03:49 PM)hmhieu Đã viết: 1) Bây giờ mình muốn thêm 1 cột STT (cột đầu tiên của bảng) và tự động đánh số thứ tự từ 1 đến hết dữ liệu xuất ra thì phải làm sao?
  

  
  
  Về đánh số thứ tự có thể đánh số thứ tự trong query trước khi xuất Excel. Tham khảo nhiều cách đánh Stt trong link này: https://thuthuataccess.com/forum/post-27113.html#pid27113
  
  
  

  (01-05-21, 03:49 PM)hmhieu Đã viết:
  2) Hàm trên chỉ chạy trên Table hoặc Query không có điều kiện, Khi mình cho chạy query có điều kiện truyền vào thì nó báo lỗi tại dòng Set rst = CurrentDb.OpenRecordset(strSourceName)
  
  Lỗi Run-time (3061): Too few parameters. Expected 2.
  
  Vậy để gọi được Query có điều kiện truyền vào từ ngoài thì sửa như thế nào? 

  
  Đúng là nếu là Query có tham số (parameters) thì nó báo lỗi nếu chỉ khai báo tên Query. Có 2 cách đối với Query có tham số:
  
  1. Dùng luôn câu lệnh SQL của Query đó. Trong hàm trên tham số strSourceName là dạng chuỗi (String), bạn chỉ cần copy câu lệnh SQL của query đó đưa vào biến chuỗi, sau đó chỉnh sửa đoạn nào có tham số lấy từ form cho phù hợp là xong.
  Vd: 
  

  Mã PHP:

  strSourceName = " SELECT * FROM Table1 WHERE ID =" & Me.txtID
Set rst = CurrentDb.OpenRecordset (strSourceName) 


  
  2. Nếu muốn dùng luôn tên Query mà không dùng câu lệnh SQL thì bạn phải khai báo lại tham số parameters trong code VBA nó mới hiểu.
  Vd: 
  
  

  Mã PHP:

  Dim qdf As DAO.QueryDefs
Set qdf = CurrentDb.QueryDefs("qryCongNo")
    qdf("[Forms]![frmBaoCaoTongHop]![txtTuNgay]") = [Forms]![frmBaoCaoTongHop]![txtTuNgay]
    qdf("[Forms]![frmBaoCaoTongHop]![txtDenNgay]") = [Forms]![frmBaoCaoTongHop]![txtDenNgay]
    qdf("[Forms]![frmBaoCaoTongHop]![cboKhachHang") = [Forms]![frmBaoCaoTongHop]![cboKhachHang]
    Set rst = qdf.OpenRecordset() 


  
  Vấn đề 2, mình đã giải quyết được.
  Vấn đề 1, mình đã tham khảo link bạn cho và nhiều cách khác. Tuy nhiên, link trên thì chủ yếu là đánh STT trên Form, các tham khảo khác để đánh STT trên Query thì khi truy vấn liệu từ Table của Access nó đánh thứ tự đúng, nhưng truy vấn dữ liệu ở Table được link từ SQL Sever (qua công cụ ODBC Database) thì nó đánh số thứ tự không đúng (thử nhiều lần nhưng vẫn sai).
  
  Vì vậy, mình muốn chèn thêm Cột STT (cột A ) và đánh số thứ tự trên Bảng tính Excel khi đã xuất dữ liệu ra (chứ k phải chèn cột STT trên Query) thì bổ sung code như thế nào trong hàm DataToExcel ấy?
  
  Thank!

• RE: Xuất dữ liệu từ report ra Excel theo mẫu

  ongke0711 > 02-05-21, 06:54 PM

  (02-05-21, 03:53 PM)hmhieu Đã viết: Vấn đề 1, mình đã tham khảo link bạn cho và nhiều cách khác. Tuy nhiên, link trên thì chủ yếu là đánh STT trên Form, các tham khảo khác để đánh STT trên Query thì khi truy vấn liệu từ Table của Access nó đánh thứ tự đúng, nhưng truy vấn dữ liệu ở Table được link từ SQL Sever (qua công cụ ODBC Database) thì nó đánh số thứ tự không đúng (thử nhiều lần nhưng vẫn sai).
  
  Vì vậy, mình muốn chèn thêm Cột STT (cột A ) và đánh số thứ tự trên Bảng tính Excel khi đã xuất dữ liệu ra (chứ k phải chèn cột STT trên Query) thì bổ sung code như thế nào trong hàm DataToExcel ấy?

  
  Tôi không biết bạn thiết kế query như thế nào, đúng hay không nên không ra kết quả, chứ Linkked Table từ SQL SV thì cũng là Table nên việc xử lý nó như nhau thôi. Nó có khác biệt rõ là khi truy vấn liên quan đến font chữ Unicode tiếng Việt thì Query Access không làm được mà phải thông qua Pass Through query.
  
  Nếu bạn muốn xử lý trong bảng tính Excel thì dùng VBA cho Excel mà xử lý, nó cũng đơn giản như bạn gõ Cell bên dưới = Cell bên trên + 1 thôi.
  Giải thuật là gì:
  - Xác định Cell bắt đầu đổ dữ liệu từ Access xuống. Ví dụ: Sheet1.Range("A2")  --> "A1" là dòng tiêu đề
  - Xác định tổng số dòng của dữ liệu ghi xuống. Dùng rst.RecordCount.
  - Dùng vòng lập duyệt từng Cell + 1 cho đến dòng cuối cùng.
  
  Vd:
  
  

  Mã PHP:

  Dim i As Integer
    For i = 2 To rst.RecordCount + 1
        sht.Range("A" & i).Value = i - 1
    Next 

• Bài trước Bài tiếp