Chương 23 - Excel VBA Programming For Dummies (1 người xem)

[handung107]

Excel VBA tương tác với các phần mềm khác trong bộ Microsoft Office như thế nào​

.

Bài dịch của Workman​

Nếu bạn đang sử dụng Excel, tôi đoán bạn cũng đang sử dụng các phần mềm khác thuộc bộ Microsoft Office. Ngoài việc gần như ai cũng dùng Word, tôi đoán bạn cũng rất quen thuộc với Power Point và Access

Trong chương này tôi sẽ trình bày một vài ví dụ đơn giản để minh họa cho việc Excel VBA tương tác với các phần mềm khác trong bộ Microsoft Office như thế nào.

1. Khởi động một chương trình khác trong Excel

Khởi động một chương trình khác từ Excel là một việc làm rất hữu ích. Ví dụ, bạn có thể muốn chạy một chương trình Microsoft Office khác hoặc ngay cả chạy các câu lệnh DOS từ macro VBA của Excel.

Sử dụng hàm Shell

Hàm shell giúp cho việc chạy một chương trình khác từ Excel trở nên rất đơn giản. ví dụ sau khởi động chương trình Calculator (máy tính), có tập tin là CALC.EXE.

Sub startcalculator()
Dim Program As String
Dim TaskID as Double
On Error Resume Next
Program = "CALC.EXE"
taskid = Shell(Program, 1)
If Err <> 0 Then
If Err <> 0 Then MsgBox "Cant start " & Program
End If
End Sub

Hình 23-1 thể hiện chương trình máy tính của Windows sau khi chạy đoạn mã trên.

Hàm Shell trả lại số thứ tự của chương trình được chạy. bạn có thể sử dụng số này để kích hoạt lại chương trình. Tham số thứ 2 của hàm Shell xác định chương trình này được hiển thị như thế nào (một là mã để xác định một cửa sổ Windows và hiện lên trên màn hình). Bạn có thể tham khảo các tham số khác trong phần Help.

Nếu hàm Shell không cần thiết, nó sẽ báo lỗi. do đó, đoạn mã trên tôi sử dụng câu lệnh On Error để hiển thị khi không thể tìm thấy chương trình cần chạy hoặc có thể chạy được nhưng vì lý do gì đó bị trục trặc.

Thế nếu chương trình Calculator đang chạy thì sao? StartCalculator đơn giản là mở thêm một cửa số khác. Phần lớn các trường hợp bạn sẽ mở một chương trình đang chạy sẵn. đoạn mã dưới đây sẽ giải quyết vấn đề này.

Public taskid

Sub StartCalculator2()
Dim Program As String
Dim taskid As Double
Program = "CALC.EXE"
AppActivate "Calculator"
On Error Resume Next
If Err <> 0 Then
Err = 0
taskid = Shell(Program, 1)
If Err <> 0 Then MsgBox "Cant start " & Program
End If
End Sub

kích hoạt một chương trình Microsoft Office

nếu một chương trình bạn muốn khởi động thuộc nhóm Microsoft, bạn có thể sử dụng ActivateMicrosoftApp. Ví dụ đoạn mã sau khởi động Word


Sub startword()

Application.ActivateMicrosoftApp xlMicrosoftWord

End Sub

Nếu Word đang chạy từ trước, Word sẽ được kích hoạt. Các hàm khác cho phương thức này:

xlMicrosoftPowerPoint (PowerPoint)
xlMicrosoftMail (Mail)
xlMicrosoftAccess (Access)
xlMicrosoftFoxPro (FoxPro)
xlMicrosoftAccessProject (AccessProject)
xlMicrosoftSchedulePlus (SchedulePlus)

 

[handung107]

2. Tự động hóa trong Excel

Bạn có thể viết một macro Excel để kiểm soát các chương trình khác, như Microsoft Word. Chính xác hơn, macro Excel kiểm soát phần lớn những chức năng của Word. Ta gọi là máy chủ tự động. trong trường hợp này, Excel là một chương trình con, và Word là chương trình chủ.

Khái niệm tự động khá là hấp dẫn. ví dụ một người lập trình muốn vẽ một đồ thị, anh ta có thể chạy đến một chương trình khác, đem theo một mớ Object, vẽ một đồ thì, xử lý đồ thị này bằng nếu công cụ và phương thức trong chương trình này. Tự động trong trường hợp này xóa nhòa các ranh giới giữa những chương trình. Ví dụ, người dùng có thể đang sử dụng một Object của Access khi đang chạy Excel mà không hề biết.

Một vài chương trình như Excel có thể hoạt động với tư cách là một chương trình chủ hay chương trình con. Có những chương trình chỉ có thể hoạt động với tư cách là một chương trình con hay chỉ hoạt động là một chương trình chủ.

Trong phần dưới đây, tôi sẽ minh họa cách sử dụng VBA để tiếp cận và tận dụng những Object của các chương trình khác. Ví dụ sử dụng Microsoft Word, nhưng có thể sử dụng có các chương trình khác nữa.

Lấy phiên bản của Word

Ví dụ dưới đây minh họa cách thiết lập một W Word Object để chạy trong môi trường Word. Chương trình sẽ tạo ra một Object, hiển thị phiên bản Word, sau đó đóng chương trình Word, cuối cùng hủy Object, giải phóng bộ nhớ đã sử dụng.

Sub GetWordVersion()

Dim WordApp As Object
Set WordApp = CreateObject("Word.Application")
MsgBox WordApp.Version
Set WordApp = Nothing

End Sub

Object được thiết lập trong chương trình này là vô hình. Nếu bạn muốn thấy Object này được sử dụng như thế nào, bạn hãy thiết lập thuộc tính Visible thành True
WordApp.Visible = True

Phần lớn những ví dụ về tự động trong chương này sử dụng kỹ thuật Binding muộn thay vì Binding sớm. Thế hai cách này khác nhau như thế nào? Khi bạn sử dụng kỹ thuật Binding sớm, bạn phải thiết lập một liên kết đến thư viện Object cụ thể cho từng phiên bản, sử dụng Tool-References trong VBE. Khi bạn sử dụng Binding muộn, bạn không cần thiết lập liên kết. cả hai cách đều có ưu và nhược điểm riêng.

 

[handung107]

3. Điều khiển Word trong Excel

Ví dụ ở hình số 23-2 minh họa cho việc này. Chương trình MakeMemo sẽ tạo ra 3 Memo trong Word và sao lưu các Memo này thành 3 file khác nhau. Những thông tin để tạo ra 3 Memo này được chứa trong một Worksheet.

Mã cho MakeMemo khá dài nên tôi không tiện chép vào đây. Nếu bạn muốn biết chi tiết hãy vào Website của cuốn sách này để tham khảo.


Chương trình MakeMemo bắt đầu bằng việc lập một Object gọi là WordApp. Nó sẽ chạy qua 3 dòng ở Sheet1 và sử dụng thuộc tính và phương thức của Word để tạo ra từng Memo và sao lưu lại. có mộtvùng được đặt tên là Message (ở ô E6) gồm mộtđoạn văn bản sử dụng trong Memo. Tất cả các hoạt động được làm âm thầm, không ai thấy Word chạy như thế nào. Hình số 23-3 cho thấy Memo được tạo ra như thế nào.

4. Điều khiển Excel từ Word

Như bạn có thể thấy, ta có thể điều khiển Excel từ những chương trình khác. Ví dụ bạn có thể tính toán trong Excel và trả kết quả lại cho văn bản Word.

Bạn có thể tạo ra những Excel Object với những hàm sau:

Object chương trình: CreateObject (“Excel.Application”)
Object Workbook: CreateObject (“Excel.Sheet”)
Object đồ thị: CreateObject (“Excel.Chart”)

Ví dụ mô tả trong phần này là một macro chạy trong Word. Macro này tạo ra một Object Workbook của Excel từ một Workbook gọi là “Projections.xls”. macro này hỏi người dùng 2 giá trị và tạo ra một bảng, một đồ thị và tất cả được lưu trữ trong văn bản Word.

Workbook đầu tiên được mô tả trong hình 23-4. MakeExcelChart sẽ hỏi user 2 giá trị rồi chèn giá trị này vào Worksheet.

Khi Worksheet tính toán lại, đồ thị cũng sẽ được cập nhật. dữ liệu từ đồ thị sẽ được copy từ Excel và dán vào trong văn bản. kết quả thể hiện trong hình 23-5

Mã của chương trình MakeExcelChart:

Sub MakeExcelChart()
Dim XLSheet As Object
Dim StartVal, PctChange
Dim Wbook As String

' Tao mot van ban moi
Documents.Add

' Prompt for values
StartVal = InputBox("Gia tri bat dau?")
PctChange = InputBox("Phan tram thay doi? Vi du, '5.2%'")

' Create Sheet object
Wbook = ThisDocument.Path & "\projections.xls"
Set XLSheet = GetObject(Wbook, "Excel.Sheet").Activesheet

' Put values in sheet
XLSheet.Range("StartingValue") = StartVal
XLSheet.Range("PctChange") = PctChange
XLSheet.Calculate

' Insert page heading
Selection.Font.Size = 14
Selection.Font.Bold = True
Selection.TypeText "Monthly Increment: " & _
Format(PctChange, "0.0%")
Selection.TypeParagraph
Selection.TypeParagraph

' Copy data from sheet & paste to document
XLSheet.Range("data").Copy
Selection.Paste

' Copy chart and paste to document
XLSheet.ChartObjects(1).Copy
Selection.PasteSpecial _
Link:=False, _
DataType:=wdPasteMetafilePicture, _
Placement:=wdInLine, DisplayAsIcon:=False

' Kill the object
Set XLSheet = Nothing
End Sub

 

[handung107]

5. Gửi một email cá nhân sử dụng Outlook

Ví dụ trong phần này minh họa việc tự động hoá với Outlook. Đoạn mã sau tạo một email cá nhân sử dụng nếu thông tin chứa trong Excel.

Hình số 23-6 thể hiện Worksheet chứa những thông tin được sử dụng trong email: tên, địa chỉ email, và số tiền thưởng. chương trình này duyệt qua các dòng trong Worksheet, lấy dữ liệu và tạo ra một email cá nhân.

Sub SendEmail()
Dim OutlookApp As Object
Dim MItem As Object
Dim cell As Range
Dim Subj As String
Dim EmailAddr As String
Dim Recipient As String
Dim Bonus As String
Dim Msg As String

'Create Outlook object
Set OutlookApp = CreateObject("Outlook.Application")

'Loop through the rows
For Each cell In Columns("B").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "*@*" Then
'Get the data
Subj = "Your Annual Bonus"
Recipient = cell.Offset(0, -1).Value
EmailAddr = cell.Value
Bonus = Format(cell.Offset(0, 1).Value, "$0,000.")

'Compose message
Msg = "Dear " & Recipient & vbCrLf & vbCrLf
Msg = Msg & "I am pleased to inform you that "
Msg = Msg & "your annual bonus is "
Msg = Msg & Bonus & vbCrLf & vbCrLf
Msg = Msg & "William Rose" & vbCrLf
Msg = Msg & "President"

'Create Mail Item and send it
Set MItem = OutlookApp.CreateItem(0)
With MItem
.To = EmailAddr
.Subject = Subj
.Body = Msg
.Display
'NOTE: To actually send the emails, use .Send instead of .Display
'.Send
End With
End If
Next
End Sub

Ví dụ này sử dụng phương thức Display, phương thức này hiển thị tin nhắn email. Nếu bạn muốn gửi email, bạn có thể sử dụng phương thức Send.

Lưu ý rằng có 2 Object : Outlook và MailItem. Outlook được tạo ra bằng câu lệnh sau:
OutlookApp = CreateObject("Outlook.Application")

MailItem được tạo ra bằng câu lệnh sau:

Set MItem = OutlookApp.CreateItem(0)

Đoạn mã gán giá trị cho To, Subject và Body của tin nhắn, và sử dụng phương thức Send để gửi từng tin một. hình 23-7 thể hiện một email vừa được tạo ra.

 

[handung107]

6. Làm việc với ADO

ActiveX Data Object (ADO) là một Object cho phép bạn tiếp cận vào dữ liệu chứa trong nhiều loại format khác nhau. Trong phần này tôi chỉ giới thiệu một ví dụ đơn giản sử dụng ADO để lấy dữ liệu từ một CSDL Access.

Lập trình ADO rất phức tạp. nếu bạn muốn tiếp cận đến những dữ liệu ngoài Excel, bạn nên đầu tư công sức đọc nhiều sách viết về vấn đề này. Ví dụ này chỉ bạn có được cảm nhận ADO làm việc như thế nào.

Ví dụ này lấy dữ liệu từ Access gọi là Budget.mdb. CSDL này có một bảng tên là Budget, có 7 field. Ví dụ này lấy dữ liệu nào trong field “Item” có chữ “Lease” và field “Division” có chữ “N. America”. Những dữ liệu thỏa điều kiện điều kiện chứa trong một Object Recordset, sau đó dữ liệu được chuyển sang Worksheet.

Sub ADO_Demo()
' This demo requires a reference to
' the Microsoft ActiveX Data Objects 2.x Library

Dim DBFullName As String
Dim Cnct As String, Src As String
Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset
Dim Col As Integer

Cells.Clear
MsgBox "This demo retrieves the data for the records in which ITEM = LEASE and DIVISION = N. AMERICA."

' Database information
DBFullName = ThisWorkbook.Path & "\budget.mdb"

' Open the connection
Set Connection = New ADODB.Connection
Cnct = "Provider=Microsoft.Jet.OLEDB.4.0; "
Cnct = Cnct & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Cnct

' Create RecordSet
Set Recordset = New ADODB.Recordset
With Recordset
' Filter
Src = "SELECT * FROM Budget WHERE Item = 'Lease' "
Src = Src & "and Division = 'N. America'"
.Open Source:=Src, ActiveConnection:=Connection

' Write the field names
For Col = 0 To Recordset.Fields.Count - 1
Range("A1").Offset(0, Col).Value = Recordset.Fields(Col).Name
Next

' Write the recordset
Range("A1").Offset(1, 0).CopyFromRecordset Recordset
End With
Set Recordset = Nothing
Connection.Close
Set Connection = Nothing
End Sub

Không giống như những ví dụ khác trong chương này chương trình này sử dụng kỹ thuật Binding sớm. Do đó, yêu cầu phải có liên kết với thư viện Microsoft ActiveX Data Object 2.0. trong VBE, sử dụng Tools-References để lập liên kết này.

Ví dụ này, vừa với CSDL Access có sẵn trong Website