摘要：本文通過代碼演示，介紹如何從Active Directory中檢索信息并將信息顯示在帶格式的Excel工作表中。
|
使用 Excel：快速回顧 我們可以僅僅使用下面兩行代碼就能創(chuàng)建 Excel工作簿： **************************************** Sub BuildWorkbook() Dim objExcel as Application Set objExcel = CreateObject("Excel.Application") objExcel.Workbooks.Add End Sub **************************************** 在默認(rèn)情況下，您在任何時候創(chuàng)建的 Excel 工作簿都運行在不可見的窗口之中。要實際地看一看所創(chuàng)建的Excel 工作簿，需要將 Visible 屬性設(shè)置為 TRUE，如下所示： **************************************** Sub BuildWorkbook() Dim objExcel as Application Set objExcel = CreateObject("Excel.Application") objExcel.Workbooks.Add objExcel.Visible = True End Sub **************************************** 上面的三行代碼將在屏幕上顯示一個空白的電子表格。 將數(shù)據(jù)添加到電子表格中 當(dāng)電子表格關(guān)閉時，您可以很容易地將數(shù)據(jù)添加到電子表格中。首先您簡單地引用一個單元格，然后相應(yīng)地設(shè)置值。例如，假設(shè)我們想要將文本“My Workbook”輸入第一行第一列。試一試下面的代碼，看看會發(fā)生什么： **************************************** Sub addData() Dim objExcel as Application Set objExcel = CreateObject("Excel.Application") objExcel.Visible = True objExcel.Workbooks.Add objExcel.Cells(1, 1).Value = "My Workbook" End Sub **************************************** 當(dāng)您運行這個代碼后，會看到類似如下面圖1所示的輸出： 圖 1. 將數(shù)據(jù)寫入 Excel 電子表格 哈哈，很酷吧！如果您想要添加其他的數(shù)據(jù)，我們只需要多引用幾個單元格并且設(shè)置合適的值就可以了。例如，下面的腳本將四種動物的名稱添加到我們的電子表格中： **************************************** Sub addDatas() Dim objExcel As Application Set objExcel = CreateObject("Excel.Application") objExcel.Visible = True objExcel.Workbooks.Add objExcel.Cells(1, 1).Value = "小貓" objExcel.Cells(1, 2).Value = "小狗" objExcel.Cells(1, 3).Value = "小兔" objExcel.Cells(1, 4).Value = "小豬" End Sub **************************************** 當(dāng)您運行這段代碼后，會看到類似如下圖2所示的輸出： 圖 2. 將數(shù)據(jù)寫入 Excel 電子表格中的多個單元格 現(xiàn)在，真的非常酷……！嗯，不錯，您真行！但這還不夠，是嗎？可以將數(shù)據(jù)添加到電子表格是一件了不起的事哦，不過，讓我們考慮一下這個問題。我們?yōu)槭裁丛敢庠?Excel 中顯示數(shù)據(jù)而不愿意在命令窗口中顯示數(shù)據(jù)或?qū)⑵浔４娴轿谋疚募心?？我們之所以愿意這么做，最可能的原因并不是選擇這兩種方法會讓您花大量的精力去安排數(shù)據(jù)的格式。至少我們可以說將數(shù)據(jù)輸出到命令窗口或保存到文本文件會缺少某種美感。這就是我們愿意使用Excel的原因；Excel使您能夠創(chuàng)建格式漂亮、易于閱讀的輸出。但是在這里我們還無法保證有格式漂亮的輸出；畢竟，您可能連“小貓”或“小狗”這樣的名字都讀不到。因此，在進一步討論之前，讓我們先來談一談Excel中的格式設(shè)置。 Excel中的格式設(shè)置 如果您曾經(jīng)使用過 Excel，您就知道在 Excel 中設(shè)置格式是多么的容易：您只需選擇一兩個單元格，然后就可以應(yīng)用某種格式了(更改字體大小、更改單元格的背景顏色、將文本設(shè)置為黑體等等，想做什么都可以)。在程序化地使用Excel時，您也可以做同樣的事情。例如，僅僅通過使用一些設(shè)置格式的命令，您就可以設(shè)置活動單元格(即您正在輸入的單元格)的格式。下面的代碼將單詞“My Workbook”輸入到第1行第1列的單元格中，然后進行如下操作： 1、將文本設(shè)為黑體(通過將Bold屬性設(shè)置為TRUE)。 2、將字體大小設(shè)為 24(通過將Size屬性設(shè)置為 24)。 3、將字體顏色設(shè)為紅色(通過將ColorIndex屬性設(shè)置為3)。 **************************************** Sub Setformat() Dim objExcel As Application Set objExcel = CreateObject("Excel.Application") objExcel.Visible = True objExcel.Workbooks.Add objExcel.Cells(1, 1).Value = "My Workbook" objExcel.Cells(1, 1).Font.Bold = TRUE objExcel.Cells(1, 1).Font.Size = 24 objExcel.Cells(1, 1).Font.ColorIndex = 3 End Sub **************************************** 當(dāng)您運行這個代碼時，您會看到如下圖3所示的輸出： 圖 3. 將帶格式的數(shù)據(jù)寫入 Excel 電子表格 屏住呼吸，怎么樣？ 最好的是除了 ColorIndex 屬性略顯復(fù)雜之外（等一會兒我們將討論它），這段代碼是非常簡單的。如果想要將文本設(shè)為斜體該怎么辦？可以使用下面這行代碼： **************************************** objExcel.Cells(1, 1).Font.Italic = TRUE **************************************** 如果想要使用 Times New Roman 字體該怎么辦？可以使用下面這行代碼： ************************************************* objExcel.Cells(1, 1).Font.Name = "Times New Roman" ************************************************* 注：遺憾的是，我們沒有時間全面介紹您在處理時會使用的許多格式設(shè)置選項。不過，請您關(guān)注，我將在以后摘錄一些很好的關(guān)于 Excel 對象模型的信息。 好的，但 ColorIndex 屬性又怎么樣呢？雖然在 Excel 中有兩種不同的方法更改顏色，但 ColorIndex 可能是最簡單的（它還可以告訴您一些關(guān)于其他方法的內(nèi)容）。在 Excel 中，有56種內(nèi)置的顏色(索引號 1–56)可供您使用；您必須做的事情就是將 ColorIndex 屬性的值設(shè)置為期望的索引號。唯一的問題就是：您如何知道索引號3是紅色，而索引號4是您真正感興趣的綠色陰影呢？ 下面這些簡單的程序代碼會向您顯示所有的56個索引號都代表什么顏色： ************************************************* Sub TestColor() Dim objExcel As Application Set objExcel = CreateObject("Excel.Application") objExcel.Visible = True objExcel.Workbooks.Add Dim i As Integer For i = 1 to 56 objExcel.Cells(i, 1).Value = i objExcel.Cells(i, 1).Interior.ColorIndex = i Next End Sub ************************************************* 當(dāng)您運行這段代碼后，您會看到如下圖4所示的輸出： 圖 4. Excel ColorIndex 值 您可以看出，如果您想要將字體顏色設(shè)置為清綠色，則只需將 ColorIndex 設(shè)置為 8 就行了。 順便提一句，如果您仔細(xì)地看過了前面的代碼，您現(xiàn)在就會知道如何設(shè)置單元格的背景顏色：只需使用 Interior.ColorIndex 屬性就行了。例如，要將單元格的顏色設(shè)置為紅色，可以使用下面的代碼： ************************************************* objExcel.Cells(1, 1).Interior.ColorIndex = 3 ************************************************* 在繼續(xù)討論之前，先來為我們的格式設(shè)置代碼做另外一件事。您可能會想得起來，我們的測試代碼向我們顯示以下圖5所示的輸出： 圖 5. 將帶格式的數(shù)據(jù)寫入 Excel 電子表格 問題在哪里？嗯，首先單詞“My Workbook”并沒有全部顯示在第一列中。那好，如果我們在第一行第二列中輸入一些內(nèi)容會怎么樣呢？我們會看到類似于圖6這樣的輸出： 圖 6. Excel 電子表格中大小設(shè)置不正確的列 沒有正確地得到我們所希望的輸出類型。顯然，我們需要做的是將第一列加寬一點。但怎么做呢？ 使用范圍 到此為止，我們還只是設(shè)置了活動單元的格式，為其更改字體大小、單元格顏色等等。然而，有時您需要使用多個單元格?；蛟S您想要更改特定行中所有單元格的字體大小?；蛟S您想要對您收集的所有數(shù)據(jù)進行排序。或許，和下面的例子一樣，您想要重新設(shè)置整個列的大小。如果這樣，您就需要使用范圍，即一組指定的單元格。 雖然有幾種不同的方法指示范圍中包含的單元格，但是它們有一點是共同的：它們都需要您創(chuàng)建 Range 對象的實例，然后 指定哪些單元格是該范圍的一部分。例如，下面是一些創(chuàng)建范圍的常用方法。 要創(chuàng)建包含單個單元格的范圍： ************************************************* Set objRange2 = objExcel.Range("A1") ************************************************* 要創(chuàng)建包含整個列的范圍： ************************************************* Set objRange = objExcel.ActiveCell.EntireColumn ************************************************* 正如您所期望的，有相似的命令來創(chuàng)建包含整個行的范圍： ************************************************* Set objRange = objExcel.ActiveCell.EntireRow ************************************************* 如果您想要選擇的行或列不同于帶有活動單元格的行或列怎么辦？沒問題。使用所需的行或列中的一個單元格來創(chuàng)建范圍，然后使用 Activate 方法來使其成為活動單元格。此時，設(shè)置代表整個行或列的范圍。例如，下面這段代碼使單元格 E5 成為活動單元格，然后通過選擇整個行來創(chuàng)建包含第 5 行中的所有單元格的范圍： ************************************************* Set objRange = objExcel.Range("E5") objRange.Activate Set objRange = objExcel.ActiveCell.EntireRow ************************************************* 要創(chuàng)建包含一組單元格的范圍： ************************************************* Set objRange = objExcel.Range("A1:C10") ************************************************* 注意，您在這里做的是指定起點 (A1) 和終點 (C10)。Excel 會自動選擇這兩個點之間的所有單元，并把它們放在范圍之中。 要創(chuàng)建包含所有數(shù)據(jù)的范圍： ************************************************* Set objCell = objExcel.Range("A1").SpecialCells(11) ************************************************* 在這個例子中，11 是表示包含數(shù)據(jù)的電子表格中最后的單元格的參數(shù)。這個命令所創(chuàng)建的范圍從單元格 A1 開始一直延伸到所有包含數(shù)據(jù)的單元格。 為了進行演示，讓我們再看一下前面的代碼，看看我們是否能夠使它變得更漂亮一點。在這個經(jīng)過修改的代碼中，我們將把動物的名稱放在單列（而不是單行）中，并且我們將給該列加上粗體標(biāo)簽 (Name)。然后，僅僅是為了使它變得更美觀一些，我們將： 1、更改帶標(biāo)簽的單元格 (1,1) 的背景顏色和字體顏色。 2、創(chuàng)建包含我們正在使用的五個單元格的范圍 (A1:A5) 并更改字體大小。 3、創(chuàng)建包含帶有四個動物名稱的單元 (A2:A5) 的范圍并更改背景顏色。 4、選擇列 A 并使用 Autofit 方法來重新設(shè)置列的大小，以便所有的文本都適合。 代碼如下： ************************************************* Sub TestRange() Dim objExcel As Application Set objExcel = CreateObject("Excel.Application") objExcel.Visible = True objExcel.Workbooks.Add objExcel.Cells(1, 1).Value = "Name" objExcel.Cells(1, 1).Font.Bold = TRUE objExcel.Cells(1, 1).Interior.ColorIndex = 30 objExcel.Cells(1, 1).Font.ColorIndex = 2 objExcel.Cells(2, 1).Value = "小貓" objExcel.Cells(3, 1).Value = "小狗" objExcel.Cells(4, 1).Value = "小兔" objExcel.Cells(5, 1).Value = "小豬" Set objRange = objExcel.Range("A1","A5") objRange.Font.Size = 14 Dim objRange As Range Set objRange = objExcel.Range("A2","A5") objRange.Interior.ColorIndex = 36 Set objRange = objExcel.ActiveCell.EntireColumn objRange.AutoFit End Sub ************************************************* 下面圖7是最終的輸出結(jié)果： 圖 7. Excel 電子表格中美觀的格式 數(shù)據(jù)排序 您可以對Excel中的輸出進行排序。將下面這兩行代碼放在上面的程序的末尾(我們等會兒解釋這兩行代碼的含義)，然后再次運行程序： ************************************************* Dim objRange2 As Range Set objRange2 = objExcel.Range("A1") objRange.Sort objRange2,,,,,,,1 ************************************************* 您應(yīng)該看到如下圖8所示的輸出： 圖 8. Excel 電子表格中經(jīng)過排序的數(shù)據(jù) 您必須按范圍對 Excel 中的數(shù)據(jù)進行排序。因而，您需要創(chuàng)建一個范圍，它包含您想要按其進行排序的列的第一個單元格。因為我們想要按列A進行排序，所以我們創(chuàng)建的范圍包含單個單元格：A1。下面這行代碼的作用就在于此： ************************************************* Set objRange2 = objExcel.Range("A1") ************************************************* 這種 Sort 方法看起來很瘋狂(這么多逗號！)，但這是因為我們僅僅按單列進行排序。當(dāng)您在 Excel 中對一些內(nèi)容進行排序時，您必須依次指定所有的排序參數(shù)；如果您不使用參數(shù)，則將其保留為空。下表對這些參數(shù)進行了總結(jié)。 ************************************************* 參數(shù)位置 說明 1 按第一列進行排序。必須將其指定為范圍。 2第一列的排序次序。將其設(shè)置為 1 表示升序（默認(rèn)），將其設(shè)置為 2 表示降序。 3按第二列進行排序。必須將其指定為范圍。 4不用于腳本。將其保留為空。 5第二列的排序次序。 6按第三列進行排序。必須將其指定為范圍。 7第三列的排序次序。 8指示將要進行排序的數(shù)據(jù)是否有標(biāo)題行。將其設(shè)置為 1，指示該數(shù)據(jù)有標(biāo)題行；將其設(shè)置為 0，指示該數(shù)據(jù)沒有標(biāo)題行；而0可以讓 Excel 確認(rèn)數(shù)據(jù)是否有標(biāo)題行。 ************************************************* 下面是在我們的示例代碼中解釋排序參數(shù) objRange2,,,,,,,1 的方式： ************************************************* 參數(shù) 說明 objRange2按第一列進行排序。在我們的例子中，這是包含單元格 A1 的范圍。 , 第一列的排序次序。我們不使用這個參數(shù)，所以將其保留為空。 , 按第二列進行排序。我們不使用這個參數(shù)，所以將其保留為空。 , 不用于腳本。將其保留為空。 , 第二列的排序次序。我們不使用這個參數(shù)，所以將其保留為空。 , 按第三列進行排序。我們不使用這個參數(shù)，所以將其保留為空。 , 第三列的排序次序。 1指示將要進行排序的數(shù)據(jù)有標(biāo)題行。 ************************************************* 在稍后的代碼中，我們將舉例對兩個不同的列進行排序。 哦，從 Active Directory 中提取數(shù)據(jù) 我們將要做的事情是編寫一個程序代碼來搜索 Active Directory 并提取每個用戶賬號的信息；然后，我們使用這些信息來建立公司電話目錄。注意，當(dāng)我向您展示用于搜索 Active Directory 的代碼時，我們將不會采用任何方式來討論這些代碼；畢竟，這是一個關(guān)于 Excel 的話題。(如果您想要獲得更多關(guān)于使用程序代碼來搜索 Active Directory 的信息，您可以參考其它一些參考資料) 首先，讓我們來看一看下面這段代碼，然后我們將對相關(guān)的部分做一些介紹： ************************************************* Const ADS_SCOPE_SUBTREE = 2 Sub GetDataAD() Dim objExcel As Application Set objExcel = CreateObject("Excel.Application") objExcel.Visible = True objExcel.Workbooks.Add objExcel.Cells(1, 1).Value = "Last name" objExcel.Cells(1, 2).Value = "First name" objExcel.Cells(1, 3).Value = "Department" objExcel.Cells(1, 4).Value = "Phone number" Dim objConnection, objCommand Set objConnection = CreateObject("ADODB.Connection") Set objCommand = CreateObject("ADODB.Command") objConnection.Provider = "ADsDSOObject" objConnection.Open "Active Directory Provider" Set objCommand.ActiveConnection = objConnection objCommand.Properties("Page Size") = 100 objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE objCommand.CommandText = _ "SELECT givenName, SN, department, telephoneNumber FROM " _ & "'LDAP://dc=fabrikam,dc=microsoft,dc=com' WHERE " _ & "objectCategory='user'" Dim objRecordSet Set objRecordSet = objCommand.Execute objRecordSet.MoveFirst Dim x As Integer x = 2 Do Until objRecordSet.EOF objExcel.Cells(x, 1).Value = _ objRecordSet.Fields("SN").Value objExcel.Cells(x, 2).Value = _ objRecordSet.Fields("givenName").Value objExcel.Cells(x, 3).Value = _ objRecordSet.Fields("department").Value objExcel.Cells(x, 4).Value = _ objRecordSet.Fields("telephoneNumber").Value x = x + 1 objRecordSet.MoveNext Loop Dim objRange As Range Set objRange = objExcel.Range("A1") objRange.Activate Set objRange = objExcel.ActiveCell.EntireColumn objRange.Autofit Set objRange = objExcel.Range("B1") objRange.Activate Set objRange = objExcel.ActiveCell.EntireColumn objRange.Autofit Set objRange = objExcel.Range("C1") objRange.Activate Set objRange = objExcel.ActiveCell.EntireColumn objRange.Autofit Set objRange = objExcel.Range("D1") objRange.Activate Set objRange = objExcel.ActiveCell.EntireColumn objRange.Autofit Set objRange = objExcel.Range("A1").SpecialCells(11) Dim objRange2 As Range Set objRange2 = objExcel.Range("C1") Dim objRange3 As Range Set objRange3 = objExcel.Range("A1") objRange.Sort objRange2,,objRange3,,,,,1 End Sub ************************************************* 確實，這段代碼看起來 有點復(fù)雜。但實際上，它非常簡單。例如，看到了用斜體字顯示的代碼行了嗎？嗯，現(xiàn)在，我們完全可以忽略它們。它們用于： 1、創(chuàng)建可見的 Excel 實例（您已經(jīng)知道如何去做了）。 2、標(biāo)記四個用于輸出的列 (Last name、First name、Department、Phone number)。您也已經(jīng)知道如何去做了。 3、搜索 fabrikam.com 域并檢索所有用戶賬號的 SN（姓或名）、givenName（名）、department 和 telephoneNumber 屬性。如果您不知道如何去做，可以參考其它相關(guān)資料。 事實上，只有開始編寫這些用黑體字顯示的代碼行之后，程序編寫才開始變得有趣： ************************************************* x = 2 Do Until objRecordSet.EOF objExcel.Cells(x, 1).Value = _ objRecordSet.Fields("SN").Value objExcel.Cells(x, 2).Value = _ objRecordSet.Fields("givenName").Value objExcel.Cells(x, 3).Value = _ objRecordSet.Fields("department").Value objExcel.Cells(x, 4).Value = _ objRecordSet.Fields("telephoneNumber").Value x = x + 1 objRecordSet.MoveNext Loop ************************************************* 這是我們從 Active Directory 中實際提取數(shù)據(jù)并將其顯示在 Excel 中的地方。為此，我們首先將變量 x 的值設(shè)置為 2。該變量指示電子表格中的當(dāng)前行。我們?yōu)槭裁磸牡诙虚_始而不從第一行開始呢？其原因很簡單，我們把列標(biāo)題放在了第一行。因此，我們從第二行開始顯示數(shù)據(jù)。 接下來，我們創(chuàng)建一個循環(huán)來循環(huán)顯示返回記錄集中的所有記錄。循環(huán)中的第一個命令是： ************************************************* objExcel.Cells(x, 1).Value = _ objRecordSet.Fields("SN").Value ************************************************* 這段代碼選擇第 2 行（用 x 表示）第 1 列，并且將該值設(shè)置為 objRecordSet.Fields("SN")。這是記錄集中第一個用戶的 SN（姓）。然后，腳本轉(zhuǎn)到第 2 行第 2 列，并且將該單元格的值設(shè)置為用戶的 givenName（名）。在為 department 和 telephoneNumber 執(zhí)行了相同的操作之后，腳本將 x 增加 1（從而使 x = 3）。然后，它循環(huán)處理記錄集中的下一個記錄，并將數(shù)據(jù)顯示在第 3 行中。這樣繼續(xù)下去，直到所有的用戶數(shù)據(jù)都添加到電子表格中為止。 換句話說，就是您從Active Directory 中提取數(shù)據(jù)并將其顯示在 Excel 中。您首先檢索數(shù)據(jù)，然后簡單地將指定單元格的值設(shè)置為從 Active Directory 中提取的值。通過使用簡單的 Do Loop 循環(huán)并將行號每次增 1，您可以顯示記錄集中的每一項的信息。就是這么簡單！ 腳本的剩余部分純粹就是點綴了。例如，這段代碼將活動范圍設(shè)置為單元格 A1，選擇整個列，然后使用 Autofit 方法來重新設(shè)置該列的大小，這樣所有的數(shù)據(jù)就都可以顯示在屏幕上。該代碼然后對列 B、C 和 D 重復(fù)這個過程。 ************************************************* Set objRange = objExcel.Range("A1") objRange.Activate Set objRange = objExcel.ActiveCell.EntireColumn objRange.Autofit ************************************************* 代碼的最后四行對所有的返回數(shù)據(jù)進行排序，首先按部門名稱進行排序，然后按姓進行排序。程序首先創(chuàng)建包含所有數(shù)據(jù)的范圍，然后為單元格 C1（按第一列進行排序）和單元格 A1（按第二列進行排序）創(chuàng)建單獨的范圍。此時，程序調(diào)用 Sort 方法，將單獨的范圍對象作為參數(shù)傳入。（相信我：這聽起來雖然有點復(fù)雜，但是它真的非常簡單?。?nbsp; 如果您在填充了電子表格之后想要自動保存它，該怎么做呢？如果與這個例子的情況一樣，就只要在腳本的末尾加上下面這段代碼就行了；這三行代碼將電子表格保存為 C:\Scripts\Phone_Directory.xls 并退出Excel 應(yīng)用程序： ************************************************* Set objWorkbook = objExcel.ActiveWorkbook objWorkbook.SaveAs("C:\Scripts\Phone_Directory.xls") objExcel.Quit ************************************************* 如果不想退出Excel 實例，只要不加上最后一行就可以了。 記住，您并不限于顯示 Excel 中的 Active Directory 信息。例如，下面是一個非常簡單的 WMI 腳本程序，它檢索運行在計算機上的所有服務(wù)的狀態(tài)和名稱，然后將這些信息顯示在 Excel 中。 ************************************************* Set objExcel = CreateObject("Excel.Application") objExcel.Visible = True objExcel.Workbooks.Add x = 1 strComputer = "." Set objWMIService = GetObject _ ("winmgmts:\\" & strComputer & "\root\cimv2") Set colServices = objWMIService.ExecQuery _ ("Select * From Win32_Service") For Each objService in colServices objExcel.Cells(x, 1) = objService.Name objExcel.Cells(x, 2) = objService.State x = x + 1 Next |
|
|
