Giải thích về Cách sử dụng mẫu trong ví dụ trước! Xin chào các bạn,
Tiếp theo 2 ví dụ đã đăng trong bài trước, xin phép được giải thích một chút về cách tiếp cận trong các ví dụ mà mình đã đăng.
A. Mục đích:
Sử dụng Excel để in ra các dạng biểu mẫu cùng loại cho nhiều khách hàng khác nhau
B. Cấu trúc
Cả hai ví dụ đều có điểm chung là:
* Có bảng dữ liệu chứa dữ liệu khách hàng cần được in ra. Trong ví dụ về báo giá (Quotation) còn có thêm bảng dữ liệu về sản phẩm, áp dụng với trường hợp khách hàng cần được báo giá về nhiều loại sản phẩm.
* Có biểu mẫu đã được định dạng sẵn để in ra
C. Cách tiếp cận
Cả hai ví dụ đều có cách tiếp cận giống nhau đó là lấy dữ liệu từ các bảng cho trước, xử lý và đưa vào biểu mẫu sau đó cho phép người dùng có thể quy định vị trí, kiểu in, chèn thêm ngắt trang.
Tuy nhiên, có sự khác nhau khá lớn giữa mẫu in LableEx và mẫu báo giá. Trong báo giá, tôi sử dụng tham chiếu tên toàn cục và cục bộ để chuyển dữ liệu từ bảng gốc sang bảng đích. Trong khi đó tại ví dụ in nhãn trong Excel, tôi sử dụng tham chiếu tương đối có hiệu chỉnh dòng, cột theo yêu cầu đầu vào.
Về nguyên tắc, để làm được việc in ra các báo giá hoặc biểu mẫu với Excel chúng ta cần thỏa mãn được các vấn đề sau:
* Có bảng dữ liệu đầu vào được thiết kế chuẩn mực.
* Có bảng chứa biểu mẫu được đặt trong một trang tính khác có các cột dữ liệu để in nhất quán và được định dạng theo yêu cầu người dùng.
* Các thao tác chính sẽ được đặt trong một trang tính điều khiển để dễ quản lý.
Cách thực hiện về cơ bản như sau - xin trích dẫn mã nguồn trong ví dụ về Quotation:
1. Khởi tạo các biến số (tên vùng, bảng tính, trang tính)
* Thiết lập các vùng tham chiếu dữ liệu trong Form mẫu cho dễ tham chiếu (cách đặt tên đã được giới thiệu khá rõ trong các bài khác của diễn đàn)
Trong ví dụ này tôi thiết lập các vùng sau:
- Quotation: Chứa toàn bộ dữ liệu của mẫu
- rngCommodities: Chứa vùng dữ liệu về sản phẩm sẽ được in ra tại báo giá
- txtCustomerName: Chứa tên khách hàng
- txtCustomerAdd: Chứa địa chỉ khách hàng
- txtCustomerTel: Chứa số điện thoại của khách hàng
- txtOfferDate: Nơi sẽ in ra ngày hiệu lực điều chỉnh
Và các vùng tham chiếu đến dữ liệu cấu hình như:
- txtPrintFrom: Chỉ ra mẫu tin đầu tiên để in báo giá
- txtPrintTo: Chỉ ra mẫu tin cuối cùng để in báo giá
- txtIsNewPage: Có chèn thêm ngắt trang vào báo giá tiếp theo không (1 có, 2 không)
- txtRowSeparator: Số dòng phân cách giữa các báo giá
* Thiết lập biến số và tham chiếu trong thủ tục. Ở đây, thủ tục kích hoạt lệnh khởi tạo báo giá là
PrepareQuotation:
Dim myWrkBook As Workbook ' Workbook hien thoi
Dim myDstWrkBook As Workbook ' Workbook cap copy den
' Khai bao Sheet tham so
Dim prmSheet As Worksheet ' Sheet chua du lieu tham so
Dim srcSheet As Worksheet ' Sheet chua du lieu khach hang
Dim refWrShet As Worksheet ' Sheet chua thong tin san pham
Dim dstRange As Range ' Vung chua du lieu can copy toi
Dim orgFormat As Range ' Vung chua form cua bieu mau
Dim i As Long
' Gan cac sheet nay voi sheet thuc te trong workbook
Set myWrkBook = ActiveWorkbook
Set prmSheet = myWrkBook.Sheets("ControlPanel")
Set srcSheet = myWrkBook.Sheets("S1")
Set refWrShet = myWrkBook.Sheets("Mathang")
' Khai bao cac bien so vung de truy cap
Dim RngData As Range ' Range chua data trong sheet khach hang
' Bay gio doc khoang cach can lam va in ra bao gia nhe...
' Khai bao bien so
Dim varPrintFrom As Long
Dim varPrintTo As Long
Dim varIsNewPage As Integer
Dim varRowSeparator As Integer
2. Đọc các dữ liệu cấu hình
' Lay cac gia tri dau vao
With prmSheet
varPrintFrom = .Range("txtPrintFrom")
varPrintTo = .Range("txtPrintTo")
varIsNewPage = .Range("txtIsNewPage")
varRowSeparator = .Range("txtRowSeparator")
End With
3. Tạo ra bảng tính chứa dữ liệu kết xuất cuối cùng.
' Chep form bao gia sang mot work book moi
Set myDstWrkBook = GetCopyForm(ActiveWorkbook.Sheets("Form"))
Đây là mã thủ tụcPrivate Function GetCopyForm(ObjToCopy As Worksheet) As Workbook
'
' Macro1 Macro
' Macro recorded 8/15/2008 by Paulsteigel
'
'
Dim objWrk As Workbook
Set objWrk = Workbooks.Add
ObjToCopy.Copy Before:=objWrk.Sheets(1)
Set GetCopyForm = objWrk
Set objWrk = Nothing
End Function
Tiếp theo, ' Tien hanh doc du lieu nhe
' rngHeader la vung chua Header cua Bang du lieu khach hang, thay vi phai dung cac diem bat dau
Set RngData = srcSheet.Range("rngHeader")
' ta se chi can dung thu tuc thi nhay ngay den dong chua du lieu dau tien
Set RngData = RngData.Offset(varPrintFrom)
' Gan Vung dich
Set dstRange = myDstWrkBook.Sheets("Form").Range("Quotation")
Set orgFormat = myWrkBook.Sheets("Form").Range("Quotation")
4. Duyệt qua vòng lặp để đọc các dữ liệu đầu vào và chép sang bảng dữ liệu kết xuất.
' Bay gio thuc hien vong lap de tien hanh copy du lieu nhe
For i = varPrintFrom To varPrintTo
TransferDataToForm RngData, dstRange, refWrShet
' Chuyen sang Vung tiep theo
Set RngData = RngData.Offset(1)
' Them dau ngan trang neu can thiet
If varIsNewPage = 1 Then
Set dstRange = dstRange.Offset(dstRange.Rows.Count + varRowSeparator + 1)
myDstWrkBook.Sheets("Form").HPageBreaks.Add dstRange.Offset(-1)
Else
Set dstRange = dstRange.Offset(dstRange.Rows.Count + varRowSeparator + 3)
End If
Set dstRange = dstRange.Offset(-1)
' Copy format tu bieu mau sang sheet moi
If i < varPrintTo Then
orgFormat.Copy
dstRange.PasteSpecial xlPasteAll
Application.CutCopyMode = False
End If
Next
5. Giải tỏa các biến đã tham chiếu khỏi bộ nhớ
' Giai toa cac bien so da khai bao va ket thuc
Set RngData = Nothing
Set prmSheet = Nothing
Set srcSheet = Nothing
Set myWrkBook = Nothing
6. Sơ lược về thủ tục chép dữ liệu sang biểu mẫu
Thủ tục
TransferDataToForm RngData, dstRange, refWrShetđược sử dụng để sao chép từng bộ dữ liệu cho từng báo giá. Cách viết như sau:
Private Sub TransferDataToForm(srcRange As Range, destRange As Range, refSheet As Worksheet)
' Ham de copy du lieu sang form moi
Dim cmdCode As Variant ' Bien mang se giu ma hang hoa
Dim cmdCode_arr As String ' Bien giu chuoi ma hang hoa
Dim foundCell As Range ' Bien giu Vung co chua ma hang da tim thay
Dim searchRange As Range ' Bien giu Vung tim kiem ma hang
Dim copyRange As Range ' Bien giu Vung tim thay de copy vao vung dich
Dim destRow As Range ' Bien giu Vung chua 1 dong dau tien trong bao gia ve hang hoa
Dim i As Long
' Xac dinh vung tim ma hang nhe
Set searchRange = refSheet.Range("B:B")
' Truoc tien la copy cac gia tri dong quan trong nhu ten tuoi, dien thoai dia chi truoc
With destRange
.Range("txtCustomerName") = srcRange.Cells(1, 3)
.Range("txtOfferDate") = srcRange.Cells(1, 4)
.Range("txtCustomerTel") = srcRange.Cells(1, 5)
.Range("txtCustomerAdd") = srcRange.Cells(1, 6)
cmdCode_arr = srcRange.Cells(1, 7) ' Day la bang danh muc hang nhe
End With
' Bay gio den cot cac hang hoa se bao gom trong bao gia
' Trong co so du lieu toi co danh muc hang duoc ngan cach bang dau "/"
' PRO14/CON15/CON16/MEA17/SEA18/CON19/CON20
' An dinh vung copy moi
Set destRange = destRange.Range("rngCommodities")
' Tach cac ma hang ra thanh mang de de xu ly
cmdCode = Split(cmdCode_arr, "/")
' Duyet qua danh sach mat hang bang vong lap,
For i = 0 To UBound(cmdCode)
' UBound(cmdCode) la ham tra ve so thu tu cua phan tu cuoi cung trong mang, so phan
' tu thuc cua mang se la Ubound()+1
' Tim kiem ma hang nao
Set foundCell = searchRange.Find(cmdCode(i), , xlValues, xlWhole, xlByRows, xlNext, False)
' Bay gio tim den cac gia tri bo sung cua hang hoa can cu vao dong da nhan duoc nhe
' Ghi nho lai vung chua thong tin de sao sang form bao gia
Set copyRange = searchRange.Range(foundCell.Offset(0, -1), foundCell.Offset(0, 3))
' Chep vao bo nho
copyRange.Copy
' Dan sang dong dau tien cua bao gia
destRange.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
' Bo che do cat dan
Application.CutCopyMode = False
Set destRow = destRange.Offset(1, 0)
' Bo sung them mot dong nua nhe
If i < UBound(cmdCode) Then destRow.EntireRow.Insert
' Gan lai Vung vua them de cho vong lap sau
Set destRange = destRow.Offset(-1, 0)
Next
' Giai phong cac bien khoi bo nho
Set foundCell = Nothing
Set searchRange = Nothing
Set destRow = Nothing
Set copyRange = Nothing
End Sub
Trong mã tôi có chú thích khá nhiều về các biện pháp kỹ thuật, mã nguồn chi tiết đặt trong file đính kèm trong bài trước.
Trong các thủ tục ở đây, có vài điểm kỹ thuật cần lưu ý:
* Cách lưu trữ nhiều khóa vào một dùng và chuyển chúng thành mảng - học theo cách trỏ tham chiếu ngoài của Access. Các bạn có thể tạo ra hộp thoại để chọn danh sách các hàng hóa cho một báo giá với Listbox cho phép chế độ chọn MultipleSelection
' PRO14/CON15/CON16/MEA17/SEA18/CON19/CON20
' An dinh vung copy moi
Set destRange = destRange.Range("rngCommodities")
' Tach cac ma hang ra thanh mang de de xu ly
cmdCode = Split(cmdCode_arr, "/") * Đoạn thủ tục sử dụng khả năng tìm kiếm trong Range của Excel một cách nhanh nhất mà không cần phải dùng cấu trúc lặp để duyệt qua các mẫu tin.
Set foundCell = searchRange.Find(cmdCode(i), , xlValues, xlWhole, xlByRows, xlNext, False) * Đoạn thủ tục sử dụng tính năng sao chép và dán của Excel.
' Chep vao bo nho
copyRange.Copy
' Dan sang dong dau tien cua bao gia
destRange.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
' Bo che do cat dan
Application.CutCopyMode = False
Hy vọng các ví dụ này sẽ giúp ích phần nào cho các bạn quan tâm.