Chuyên đề VBA

thuộc tính thường dùng Chuyên đề VBA – HIENLTH 122 5. Hành động Excute 6. Tập hợp Errors Mục tiêu sử dụng • Mục tiêu sử dụng – Tạo kết nối tường minh với CSDL, tiết kiệm tài nguyên cho hệ quản trị CSDL – Quản lí lỗi phát sinh khi làm việc với CSDL • Khai báo Public CN As ADODB.Connection • Sử dụng Set CN = New ADODB.Connection Chuyên đề VBA – HIENLTH 123 ‘Các thao tác trên biến CN CN.Close ‘Đóng kết nối với CSDL • Chú ý – Chương trình nên tận dụng tối đa khả năng dùng chung kết nối với CSDL – Ngắt kết nối càng sớm càng tốt khi không còn làm việc với CSDL – Không khai báo CN cục bộ Tạo kết nối với CSDL • Data provider – Là một phần mềm phục vụ cho các ứng dụng sử dụng ADO để thực hiện các thao tác về dữ liệu • Bước 1: Xác định các thông tin và đặc điểm của kết nối – Sử dụng DSN (Data Source Name), xem demo trên máy – ConnectionString • Provider: chỉ ra data provider của OLE-DB tương ứng với CSDL Chuyên đề VBA – HIENLTH 124 • Data Source: chỉ ra CSDL cần kết nối • User ID, Password: username và password để truy cập CSDL • Có thể lấy được Connection String từ ADO Data Control – CursorLocation • Chỉ ra tập hợp các chức năng mà hệ quản trị CSDL sẽ hỗ trợ trong phiên làm việc • Bước 2: Tiến hành kết nối – Gọi hành động Open Tạo kết nối với CSDL • Ngay tại VBA của Access: Dim conn As ADODB.Connection Set conn = CurrentProject.Connection • Ở ngoài Access: Public conn As ADODB.Connection Set conn = New ADODB.Connection Chuyên đề VBA – HIENLTH 125 conn.ConnectionString = "Provider = Microsoft.Jet.OLEDB.4.0 ;Data Source=C:\Documents and Settings\HPT\Desktop\QLHS.mdb" conn.CursorLocation = adUseClient conn.Open Tạo kết nối với CSDL • Kiểm tra kết nối If conn.State adStateOpen Then MsgBox “Ket noi khong thanh cong” Else MsgBox “Ket noi thanh cong” Chuyên đề VBA – HIENLTH 126 End If • Đóng kết nối conn.Close ‘hoặc Set conn = Nothing Các loại Cursor • Cursor – Là một thành phần của CSDL, kiểm soát việc di chuyển giữa các mẩu tin trong RecordSet, cập nhật dữ liệu hay khả năng nhìn thấy sự thay đổi dữ liệu từ người khác – CursorLocation: các chức năng hỗ trợ cho Cursor của CSDL • Loại CursorLocation mặc định được dùng là adUserServer – Tăng sự lưu thông trên mạng nhưng lượng dữ liệu truyền trên mạng nhỏ – Thời gian đáp ứng yêu cầu của người dùng nhanh – Thiếu nhiều chức năng của các đối tượng ADO Chuyên đề VBA – HIENLTH 127 • Microsoft Cursor Service cho OLE DB cung cấp rất nhiều chức năng mạnh cho data provider (hay ứng dụng sử dụng ADO) – Tăng tốc độ thực hiện một số chức năng – Đơn giản hoá việc lập trình – Giảm sự lưu thông trên mạng nhưng dữ liệu truyền trên mạng lớn • Để sử dụng Microsoft Cursor Service – Connection.CursorLocation = adUserClient – RecordSet.CursorLacation = adUseClient Các thuộc tính thường dùng • ConnectionString – Chuỗi thông tin dùng để tạo kết nối với một CSDL • ConnectionTimeout – Số giây tối đa chờ kết nối, nếu vượt quá, coi như không kết nối được (mặc định = 15) • CursorLocation – Xác định tập hợp các chức năng của Connection sẽ dùng Chuyên đề VBA – HIENLTH 128 • State – Trạng thái của mối kết nối (adStateClosed, Open, Connecting, Excuting, Fetching) • Provider – Chỉ định data provider của OLE DB sẽ dùng để kết nối với CSDL – Thường dùng chung trong ConnectionString Làm việc với Record Dim rs As ADODB.Recordset Set rs = new ADODB.Recordset • Mở: Recordset.Open Source, Connection, CursorType, LockType, Options Trong đó: Chuyên đề VBA – HIENLTH 129 – Source: tên bảng hay câu lệnh SQL – Connection: biến connect đã mở trước đó – CursorType: • adOpenForwardOnly: mở chỉ đọc • adOpenKeyset, adOpenDynamic : mở để thêm/sửa/xóa cập nhật kịp thời Làm việc với Recordset • Các thuộc tính: – Recordset.Source: Trả về tên Table hay câu lệnh SQL. – Recordset.ActiveConnection: Trả về connection – Recordset.CursorType Chuyên đề VBA – HIENLTH 130 – Recordset.LockType • Ví dụ: Mở connection sử dụng Table: rs.Open “HocSinh", conn Các giá trị của CursorType • CursorType: Loại cursor (xem cursor ở phần Connection) mà data provider sẽ sử dụng khi mở RecordSet • adOpenForwardOnly (mặc định) – Nhanh, chỉ dùng để duyệt qua các mẩu tin lấy được • adOpenDynamic – Có thể di chuyển tới - lui, cập nhật mẩu tin – Thấy được các mẩu tin do người khác thêm, xoá, sửa vào CSDL • adOpenKeySet Chuyên đề VBA – HIENLTH 131 – Có thể di chuyển tới - lui, cập nhật mẩu tin – Mẩu tin • Thêm bởi người khác: Không thấy • Xoá bởi người khác: không truy cập được • Sửa bởi người khác: thấy • adOpenStatic – Có thể di chuyển tới - lui, cập nhật mẩu tin – Không thấy người khác thao tác với dữ liệu Các giá trị của LockType • Locktype: hình thức khoá mẩu tin mà data provider sẽ sử dụng khi mở RecordSet • adLockBatchOptimistic – Cập nhật nhiều mẩu tin một lúc, chỉ khoá các mẩu tin vào lúc thực hiện hành động cập nhật (UpdateBatch) • adLockOptimistic Chuyên đề VBA – HIENLTH 132 – Cập nhật từng mẩu tin, chỉ khoá mẩu tin vào lúc thực hiện hành động cập nhật (Update) • adLockPessimistic – Cập nhật từng mẩu tin, khoá mẩu tin ngay khi có sự thay đổi dữ liệu • adLockReadOnly – Mẩu tin chỉ có thể đọc, không thể thay đổi dữ liệu Các thuộc tính thường dùng • AbsolutePosition: Vị trí mẩu tin hiện hành trong RecordSet • RecordCount: Tổng số mẩu tin trong RecordSet – Thường dùng để xác định số mẩu tin, RecordSet rỗng • BOF: Vượt ra trước mẩu tin đầu tiên – Thường dùng trong quá trình di chuyển mẩu tin hiện hành, tìm kiếm, lọc, • EOF: Vượt ra sau mẩu tin cuối cùng – Thường dùng trong quá trình di chuyển mẩu tin hiện hành, tìm kiếm, Chuyên đề VBA – HIENLTH 133 lọc, • State: Trạng thái kết nối với CSDL của RecordSet – Hai giá trị thường dùng: adStateClosed và adStateOpen • Bookmark: Đánh dấu mẩu tin hoặc di chuyển mẩu tin hiện hành tới vị trí đánh dấu • EditMode: Tình trạng của mẩu tin hiện hành – Giá trị thường dùng: adEditNone Ví dụ 1 Sub RecordsetOpenTable_1() Dim rs As ADODB.Recordset ‘Khai báo biến Recordset Set rs = new ADODB.Recordset rs.Open “HocSinh", conn Chuyên đề VBA – HIENLTH 134 ‘Thực hiện. rs.Close ‘Đóng kết nối Set rs = Nothing End Sub Ví dụ 2 Sub RecordsetOpenTable_2() Dim rs As ADODB.Recordset ‘Khai báo biến Recordset Set rs = new ADODB.Recordset With rs .Source = “HocSinh“ Chuyên đề VBA – HIENLTH 135 .ActiveConnection = conn .Open End With rs.Close ‘Đóng kết nối Set rs = Nothing End Sub Làm việc với Recordset • SELECT [DISTINCT] field_names FROM table_name WHERE criteria ORDER BY field_names [DESC]; • Ví dụ dùng SQL: Sub RecordsetOpenSELECT() Dim rs As ADODB.Recordset Dim strSELECT As String Chuyên đề VBA – HIENLTH 136 Set rs = new ADODB.Recordset strSELECT = "SELECT * FROM HocSinh WHERE NoiSinh=‘KhanhHoa‘ ORDER BY TenHS;“ rs.Open strSELECT, conn, adOpenKeyset rs.Close Set rs = Nothing End Sub Làm việc với RecordSet • Lấy giá trị 01 trường: Recordset.Fields(FieldName) Recordset(FieldName) Recordset.Fields(FieldIndex) Recordset(FieldIndex) Chuyên đề VBA – HIENLTH 137 • Ví dụ: ‘Sau khi mở kết nối, truy vấn SQL rs.Fields(“TenHS”) ’Lấy tên HS rs(0) ‘Lấy giá trị cột số 0 (cột đầu tiên) Làm việc với RecordSet • Các điều khiển: – Recordset.MoveFirst – Recordset.MoveLast – Recordset.MoveNext – Recordset.MovePrevious Chuyên đề VBA – HIENLTH 138 • Kiểm tra: – Recordset.BOF: Mẫu tin đầu tiên? – Recordset.EOF: Mẫu tin cuối cùng? • Di chuyển: – Recordset.Move NumRecords, Start Tìm 01 record thỏa mãn điều kiện nào đó? Recordset.Find Criteria, SkipRows, SearchDirection, Start Trong đó: – Criteria: Điều kiện tìm – SkipRows: Tìm từ SkipRows mẫu tin tính từ vị trí Chuyên đề VBA – HIENLTH 139 mẫu tin hiện tại. Mặc định là 0. – SearchDirection: • adSearchForward: Hướng tăng (record tiếp) • adSearchBackward: Hướng giảm (record trước) – Start: Vị trí bắt đầu Ví dụ về tìm Sub SearchRecordsWithFind() Dim rs As ADBDO.Recordset Dim strCriteria As String Set rs = new ADODB.Recordset With rs .Source = “HocSinh“ .ActiveConnection = conn .CursorType = adOpenKeyset .Open End With Chuyên đề VBA – HIENLTH 140 strCriteria = “NoiSinh=‘Khánh Hòa'“ rs.Find strCriteria Do While Not rs. EOF MsgBox rs(“HoHS") & " " & rs(“TenHS") rs.Find strCriteria, 1 Loop rs.Close Set rs = Nothing End Sub Thêm mới 01 mẫu tin (record) rs.AddNew rs(“tên_trường") = “giá_trị“ rs.Update Ví dụ: Chuyên đề VBA – HIENLTH 141 ‘Khai báo kết nối rs.AddNew rs(“MaHS”) = “Trần Văn” rs(“TenHS”) = “Chiến” rs.Update LockType và hành động cập nhật • Khi mẩu tin được thêm mới hay sửa đổi, dữ liệu được data provider tạm thời lưu trong bộ nhớ trước khi cập nhật vào CSDL – Để cập nhật dữ liệu vào CSDL, sử dụng hành động • Update • UpdateBatch – Để bỏ qua các thay đổi về dữ liệu, sử dụng hành động • CancelUpdate nếu cập nhật dùng Update • Gọi CancelUpdate khi EditMode=adEditNone sẽ có lỗi Chuyên đề VBA – HIENLTH 142 • CancelBatch nếu cập nhật dùng UpdateBatch • Các LockType – adLockReadOnly: Không cập nhật được – adLockPessimistic, adLockOptimistic: sử dụng Update • Dữ liệu tự động cập nhật khi di chuyển tới mẩu tin khác • Nếu không Update hoặc CancelUpdate, đóng RecordSet sẽ có lỗi – adLockBatchOptimistic: sử dụng UpdateBatch • Dữ liệu đã thay đổi sẽ bị mất khi đóng RecordSet mà chưa cập nhật Sửa 01 mẫu tin (record) rs.Find điều_kiện_tìm Do While Not rs.EOF rs(“tên_trường") = “giá_trị“ rs.Update rs.Find điều_kiện_tìm, 1 Loop Ví dụ: Chuyên đề VBA – HIENLTH 143 dkTim = “NoiSinh = ‘Khánh Hà’ “ rs.Find dkTim Do While Not rs.EOF rs(“NoiSinh”) = “Khánh Hòa” rs.Update rs.Find dkTim, 1 Loop Xóa 01 mẫu tin (record) rs.Find điều_kiện_tìm If Not rs.EOF Then rs.Delete rs.Update End If Ví dụ: Chuyên đề VBA – HIENLTH 144 dkTim = “MaHS = ‘HS001’ “ rs.Find dkTim If Not rs.EOF Then rs.Delete rs.Update End If Đại Học Sư Phạm Tp. Hồ Chí Minh Chuyên đề VBA VBA for Outlook Bắt đầu sử dụng Outlook 2003 Chuyên đề VBA – HIENLTH 146 Tùy chọn Chuyên đề VBA – HIENLTH 147 Gõ tên hiển thị trên Email Chuyên đề VBA – HIENLTH 148 Đặt địa chỉ Email • Giả sử dùng Gmail Chuyên đề VBA – HIENLTH 149 Cài đặt thông số • POP3 • pop.gmail.com • smtp.gmail.com Chuyên đề VBA – HIENLTH 150 Cài đặt thông số Chuyên đề VBA – HIENLTH 151 Lựa chọn cách kết nối Internet Chuyên đề VBA – HIENLTH 152 Thông báo kết quả Chuyên đề VBA – HIENLTH 153 Giao diện Outlook 2003 Chuyên đề VBA – HIENLTH 154 Cho phép dùng POP trong Gmail Chuyên đề VBA – HIENLTH 155 Bật POP Chuyên đề VBA – HIENLTH 156 Điều chỉnh cổng gửi/nhận thư • Tools / Email Account Chuyên đề VBA – HIENLTH 157 Điều chỉnh cổng gửi/nhận thư Chuyên đề VBA – HIENLTH 158 Điều chỉnh cổng gửi/nhận thư Chuyên đề VBA – HIENLTH 159 Điều chỉnh cổng gửi/nhận thư Chuyên đề VBA – HIENLTH 160 Điều chỉnh cổng gửi/nhận thư Chuyên đề VBA – HIENLTH 161 Điều chỉnh cổng gửi/nhận thư • POP3: 995 • SMTP: 465 Chuyên đề VBA – HIENLTH 162 Nội dung chương này • Xác định, copy, di chuyển, xóa email • Xác định người gửi, chủ đề, nội dung, • Gửi email • Thêm, xóa, download attach Chuyên đề VBA – HIENLTH 163 • Sử dụng Outlook ở các ứng dụng khác trong MS Office Khai báo Dim ns As NameSpace Dim ib As MAPIFolder Set ns = ThisOutlookSession.Session Set ib = ns.GetDefaultFolder(olFolderInbox) Chuyên đề VBA – HIENLTH 164 Khai báo Namespace và MAPIFolder. Ví dụ: Truy xuất tới thư mục Input trong Personal Folders. ns.Folders(1).Folders(3) ns.Folders("Personal Folders").Folders(“Input") Liệt kê tất cả các Folder Sub demo() Dim ns As NameSpace Dim folder As MAPIFolder Dim subfolder As MAPIFolder ' Dat namespace lam viec Set ns = ThisOutlookSession.Session folder ns.FoldersFor Each In MsgBox "TM cha: " & folder.Name If folder.Folders.Count > 1 Then For Each subfolder In folder.Folders MsgBox subfolder.Name Next 'subfolder End If Next 'folder Set ns = Nothing End Sub Làm việc với Email - MailItem • Khai báo: Dim msg As MailItem • Các thuộc tính: (msg.Tên_thuộc_tính) – BCC, CC: Địa chỉ BCC, CC – Body, BodyFormat: Nội dung thư – HTMLBody: Nội dung thư dạng HTML Chuyên đề VBA – HIENLTH 166 – Recipients: Người nhận – SenderName: Người gửi – Subject: Chủ đề thư – To: Tên người nhận – UnRead: Đã đọc chưa? – SenderEmailAddress: Email người gửi Mail Items • Các phương thức: – Close – Copy, Move, Delete – Save – Print Out Chuyên đề VBA – HIENLTH 167 – Send – Forward, Reply, ReplyAll Gửi email đơn giản Sub Send_First_Email() Dim mi As MailItem Set mi = Application.CreateItem(olMailItem) mi.To = "hpt7777@gmail.com" Chuyên đề VBA – HIENLTH 168 mi.Subject = "Test email" mi.Body = "test" mi.Send End Sub Ví dụ: Tạo email trả lời tự động Sub autoreply_Thanks() Dim ns As NameSpace Dim oInbox As MAPIFolder Dim msg As MailItem 'Tao doi tuong Inbox Set ns = ThisOutlookSession.Session Set oInbox = ns.GetDefaultFolder(olFolderInbox) 'Duyet các chua doc trong inbox For Each msg In oInbox.Items Chuyên đề VBA – HIENLTH 169 With msg If .UnRead Then With .Reply .Subject = "THANKS FOR EMAIL" .Body = "Thanks for email!!! Good luck!" .Send End With End If End With Next msg End Sub Chọn email người nhận • mi.To: Thêm 01 người nhận • mi.Recipients.Add:Thêm được nhiều người nhận Chuyên đề VBA – HIENLTH 170 Attachment • mi.Attachments.Add • mi.Attachments.FileName • mi.Attachments.Delete • mi.Attachments.SaveAs (Path) Chuyên đề VBA – HIENLTH 171 Sử dụng Email ở các ứng dụng khác • (Alt + F11) Tools / References –Microsoft Outlook 11.0 Object Library Chuyên đề VBA – HIENLTH 172 Khai báo sử dụng Dim ol As Outlook.Application Set ol = CreateObject("Outlook.Application") Dim ns As NameSpace Dim folder As MAPIFolder ‘Thiết lập kết nối Chuyên đề VBA – HIENLTH 173 Set ns = ol.GetNamespace("MAPI") ns.Logon ‘Thực hiện các công việc tương tự Outlook Ví dụ gửi mail ở Excel Public Function SendEMail(Recipient As String, Subj As String, Body As String) Dim App As Object Dim Mail As Object Set App = CreateObject("Outlook.Application") Set Mail = App.CreateItem(0) With Mail .Subject = Subj ‘Tiêu đề thư Chuyên đề VBA – HIENLTH 174 .Recipients.Add (Recipient) ‘Email người nhận .Body = Body ‘Nội dung thư End With Mail.Send Set Mail = Nothing Set App = Nothing End Function Câu hỏi và thảo luận Chúc các bạn hoàn thành tốt bài tập và ứng dụng vào công việc thực tế! Chuyên đề VBA – HIENLTH 175