吳乙飛

摘要：文章探討了在Excel/Visio如何使用VBA宏從Excel自動(dòng)拷貝大量表格到Visio中。隨著Visio在設(shè)計(jì)工作的大量使用，實(shí)現(xiàn)自動(dòng)化拷貝粘貼的方法可以使設(shè)計(jì)工作更加有效和有趣。

關(guān)鍵詞：Visio 2010； Excel VBA；表格；網(wǎng)絡(luò)設(shè)計(jì)；自動(dòng)化

近年來(lái)，設(shè)計(jì)工作的畫圖常常使用Visio/Excel，它們都帶有VBA。VBA是Visual Basic for Applications的縮寫，是Visual Basic的一種宏語(yǔ)言，是微軟開發(fā)出來(lái)在其桌面應(yīng)用程序中執(zhí)行通用的自動(dòng)化任務(wù)的編程語(yǔ)言。VBA使用方便，容易推廣> 本文主要討論如何在Visio/Excel中使用宏來(lái)簡(jiǎn)化設(shè)計(jì)工作中重復(fù)性的勞動(dòng)。

1 宏的基本概念

宏是將Visio或Excel當(dāng)中的操作通過(guò)錄制或者編寫程序，變成可重復(fù)執(zhí)行的程序。比如，對(duì)于反復(fù)拷貝或粘貼或計(jì)算的工作，不需要每次都手工做同樣的事情，只要運(yùn)行已經(jīng)做好的宏，就可達(dá)到效果，可以提高工作效率，實(shí)現(xiàn)操作自動(dòng)化。

2 實(shí)例分析

2.1 實(shí)例的功能

在網(wǎng)絡(luò)設(shè)計(jì)制表中，會(huì)把在每個(gè)節(jié)點(diǎn)的Excel中的某個(gè)節(jié)點(diǎn)的線纜布放表（見表1），粘貼到Visio（見圖1），網(wǎng)絡(luò)設(shè)計(jì)中節(jié)點(diǎn)非常多，可能一個(gè)地市有60?150張線纜布放表，如果都是手工粘貼到Visio就非常繁瑣。

這樣的工作簡(jiǎn)單重復(fù)性高，非常適合使用VBA宏來(lái)優(yōu)化，提高工作效率。筆者對(duì)自動(dòng)粘貼和手工粘貼做了對(duì)比，80個(gè)局點(diǎn)手工粘貼需要30 min，使用程序以后只需要80 s，效率為原來(lái)的30倍。

2.2 Excel中的線纜布放表格式

線纜布放表每列內(nèi)容和信息如表2所示，每個(gè)線纜布放表共16列，其中第一列以序號(hào)作為開頭，第15列為節(jié)點(diǎn)名稱，作為后面命名名稱的需要，Excel工作表的名稱為線纜布放表匯總，示意用的地市線纜布放匯總表有59個(gè)局點(diǎn)信息，共有670行，依次排列，每個(gè)節(jié)點(diǎn)的線纜布放表以序號(hào)開始，以空行分隔結(jié)束，每行共有16列。

一個(gè)節(jié)點(diǎn)的線纜布放如表2所示。程序以序號(hào)來(lái)測(cè)試是否是一個(gè)新的局點(diǎn)，并且定義一個(gè)新的表格名稱來(lái)定義該節(jié)點(diǎn)的線纜布放表范圍，名稱就是在Visio中的圖紙名稱，為節(jié)點(diǎn)名稱+數(shù)據(jù)局線纜布放表。

2.3 Visio中的線纜布放表

圖1提供了已經(jīng)完成的粘貼在Visio中的線纜布放表，每個(gè)節(jié)點(diǎn)一頁(yè)Visio，每個(gè)Visio有一個(gè)表名，表名為節(jié)點(diǎn)名稱+線纜布放表。目標(biāo)文件如圖1所示。

因此，我們程序的目標(biāo)就是把Excel中某節(jié)點(diǎn)的線纜布放表粘貼到Visio，然后調(diào)整好格式大小，并且自動(dòng)生成表名。

Excel名稱的概念：名稱是一個(gè)Excel中有意義的簡(jiǎn)略表示法，便于了解單元格引用、常量、公式或表的用途?？梢詣?chuàng)建和使用的名稱類型有以下幾種。

（1）已定義名稱。一個(gè)表示單元格、區(qū)域的單元格、公式或常量值的名稱。您可以創(chuàng)建您自己的已定義的名稱，Microsoft Office Excel有時(shí)創(chuàng)建已定義的名稱，比如當(dāng)設(shè)置打印區(qū)域時(shí)。

（2）表名稱。Excel表格的名稱，Excel表格是存儲(chǔ)在記錄（行）和字段（列）中的有關(guān)特定主題的數(shù)據(jù)集合。

幾個(gè)常用的名稱如表3所示。

在程序中使用了表格名稱（NAME）。線纜布放匯總表的表格中有多個(gè)節(jié)點(diǎn)的線纜布放表，每個(gè)線纜布放表開始的標(biāo)識(shí)為A列的內(nèi)容為序號(hào)，我們根據(jù)序號(hào)找到節(jié)點(diǎn)對(duì)應(yīng)的表格名稱范圍，并且把它們加入到名稱管理器中，以該節(jié)點(diǎn)名稱加上線纜布放表作來(lái)命名，如下面語(yǔ)句所示：

ActiveWorkbook.Names.Add Name：=location-name，RefersTo：=Range（Cells（l， 2）， Cells（l，14））.CurrentRegion

其中l(wèi)ocation_name為15列名稱作為該表格的名稱。

部分線纜布放表的命名名稱如表4所示，出于對(duì)設(shè)計(jì)信息的保密，不列出所有的節(jié)點(diǎn)名稱。

2.4 程序邏輯結(jié)構(gòu)

跟程序相關(guān)的文件共有3個(gè)（見表5）。

程序分成兩部分：第一部分為在Excel中的宏。宏的名稱為線纜布放表命名，主要功能為在名稱管理器中為每個(gè)節(jié)點(diǎn)定義名稱，規(guī)定其范圍，以便于Visio中的宏去找到各個(gè)節(jié)點(diǎn)的線纜布放表；第二部分為在Visio中的宏，有兩個(gè)，一個(gè)宏為“線纜布放表粘貼”宏，主要功能為查找線纜布放表.xlsx中的名稱管理器，將名稱管理器中所有的名稱所對(duì)應(yīng)的表格找出來(lái)并且粘貼到Visio中，每個(gè)表格一個(gè)Visio頁(yè)面；另外一個(gè)宏為adjusttable，主要為調(diào)整Visio中的所粘貼線纜布放表的大小，使得它在一個(gè)合適的范圍內(nèi)。

整個(gè)程序邏輯如圖2所示。

2.5 Excel中的線纜布放表命名宏

Excel中的宏的名稱為線纜布放表命名。作用是在Excel的表格中給每一張線纜布放表命名。

下面是線纜布放表命名源程序，程序中2?6行為變量申明。srcname為每張線纜布放表的標(biāo)識(shí)符，我們用序號(hào)表示，如果是不一樣的表格，可以更改標(biāo)識(shí)符的內(nèi)容。L為行數(shù)，在我們的表格中，最多不超過(guò)670行，可以根據(jù)大家的情況進(jìn)行調(diào)整。LOCATION_NAME用于標(biāo)識(shí)節(jié)點(diǎn)名稱。Location_name_pos是指節(jié)點(diǎn)名稱是在序號(hào)出現(xiàn)以后多少行，這里設(shè)置為3。即在找到表頭行后加上2行的第15列為節(jié)點(diǎn)名稱，J為名稱序號(hào)，每一個(gè)節(jié)點(diǎn)有一個(gè)表格名稱對(duì)應(yīng)。

程序12?21句為一個(gè)循環(huán)語(yǔ)句，它尋找“序號(hào)”，如果找到，表明一個(gè)新的線纜布放表的開始，第14句為計(jì)算所在表格的行數(shù)，第15句使用了Range（Cells（l，1），Cells（l， 15））.CurrentRegion找出該局點(diǎn)線纜布放表表格范圍，location_name為節(jié)點(diǎn)名稱，16句根據(jù)該節(jié)點(diǎn)線纜布放表的范圍定義表格名稱。

Sub線纜布放表命名。

Dim srcname As String ‘用于區(qū)分不同地區(qū)的標(biāo)識(shí)

“序號(hào)”

Dim 1 As IntegerDim location-name As StringDim location-name-pos As IntegerDim j As Integer

j = l

Sheets（“線纜布放表匯總” ）.Selectsrcname 二“序號(hào)”location-name-pos = 2table_col-num =15For 1 = 1 To 670

IfCells（l，l）.Value= “序號(hào)” Thentable row num = Range（Cells（l，1）， Cells（l，16））.CurrentRegion. Rows .Count

location-name = Cells（l + location-name-pos ， 15）.Value & “數(shù)^局線纜布放表”

ActiveWorkbook.Names.Add Name：=location-name，RefersTo：=

Range（Cells（l， 1）， Cells（l， 15））.CurrentRegion1 = 1 + table_row-num -1

Sheets（“系纜^名稱匯總”）.Cells（j，l）.Value =location-name

j = j + 1

End If

Next 1

End Sub

2.6 Visio中的宏

程序使用了Visio 2010，不同的Visio版本可以支持的程序略有不同。在這個(gè)Vision中有兩個(gè)程序。

第一個(gè)程序：線纜布放表粘貼到Visio的程序，主要是每個(gè)節(jié)點(diǎn)增加一個(gè)新的Visio頁(yè)面，設(shè)置好頁(yè)面背景，讀取Excel文件，根據(jù)Excel中的名稱管理器中的名稱，讀取相關(guān)的線纜布放表范圍、內(nèi)容，并且復(fù)制表格以圖元圖片的形式粘貼到Visio頁(yè)面中，然后在每頁(yè)右下角產(chǎn)生線纜布放表的圖紙名稱，并且調(diào)整字體。

下面為線纜布放表粘貼到Visio的程序，程序1?15為變量聲明。其中poslx，posly為x，y坐標(biāo)，該位置用于設(shè)置節(jié)點(diǎn)頁(yè)面名稱，17?20語(yǔ)句是主要打開同個(gè)目錄下面的線纜布放表.xlsx文件，16?45語(yǔ)句為一個(gè)循環(huán)語(yǔ)句，語(yǔ)句根據(jù)線纜布放表.xlsx名稱，27?29是根據(jù)名稱增加一個(gè)Visio頁(yè)面，頁(yè)面名稱設(shè)置為XX節(jié)點(diǎn)線纜布放表。30?40語(yǔ)句是增加一個(gè)文本框，加入XX節(jié)點(diǎn)線纜布放表名稱，并且設(shè)置文本的字符、位置、文本框線型等。語(yǔ)句40?42為線纜布放表相應(yīng)節(jié)點(diǎn)表格拷貝粘貼的功能。45語(yǔ)句把所生成的Visio保存為線纜布放表.vsd文件。

Sub線纜布放表粘貼到Visio的程序。

Dim xlApp As Object

Dim xlname As Name

Dim filepath As String

Dim vsoapplication As Visio.Application

Dim pagename As String

Dim vsoDocument As Visio.Document

Dim vsopages As Visio.Pages

Dim vsoShapel As Visio.Shape

Dim i As Integer

Dim location-num As Integer

Dim poslx As Double

Dim posly As Double

Set vsoapplication = Visio.Application

i = 1

poslx = 30posly = 2

filepath = ActiveDocument.Path

strExcelfile = filepath & “線纜布方文表匯總.xlsx”

‘prepare for the Excel sheet read and write

Set xlApp = GetObject（，“Excel.application”）

Set xlApp = GetObject（strExcelfile）

xlApp. Application-Visible = True

xlApp.Parent.Windows ⑴.Visible = True

location-num = xlApp.Names.Count

Set vsopages = ActiveDocument.Pages

For i = 1 To location num ' LOOP for NAME

Set xlname = xlApp.Names.Item（i）

Set vsopage = vsopages.Addvsopage.Name = xlApp.Names.Item（i）.Name

Set vsoShapel = ActivePage.DrawRectangle（poslx，posly， poslx + 5.2， posly + 0.43）

vsoShapel .Text = vsopage .Name

vsopage.BackPage = “A4 模板”

vsoShapel.Cells（“PinX”）.Result（69）= poslx

vsoShapel .Cells（“PinY”）.Result（69）= posly

vsoShapel.Cells（“width”）.Result（69）= 4

vsoShapel.Cells（“height”）.Result（69）= 0.43

vsoShapel.Cells（“l(fā)inecolor”）= visWhite

vsoShapel.Characters.CharProps（visCharacterFont）=216#

vsoShapel .Characters.CharProps（visCharacterSize）=

11#

vsoShapel.Characters.CharProps（visCharacterAsianFont）= 216#

xlnamc.RcfcrsIbRangc.Copy

vsoapplication. ActiveWindow.Page.Paste SpecialvisPasteEMF， False， False

ActiveDocument.DiagramServicesEnabled =DiagramServices

Next i

ActiveDocument.SaveAs（filepath & “線纜布放表.vsd”）

End Sub

第二個(gè)程序：adjusttable（）自動(dòng)調(diào)整每張Visio中圖片的大小，使得圖片居中，不過(guò)長(zhǎng)，不過(guò)寬，程序如下面程序，

1?17為變量聲明。其中cx為衡量實(shí)際粘貼線纜布放表的寬度是指定不能超過(guò)的寬度（table_width）的倍數(shù)，如果cx>l，則說(shuō)明實(shí)際的粘貼后的寬度過(guò)全，cy為衡量實(shí)際粘貼線纜布放表的高度是指定不能超過(guò)的高度（table_height）的倍數(shù)，如果cy>l則說(shuō)明實(shí)際粘貼后的線纜布放表i于高，需要調(diào)整。table_centerx，table_centery為線纜布放表的中心位置。該位置用于設(shè)置線纜i放表的中心位置，18?38為一個(gè)循環(huán)語(yǔ)句，語(yǔ)句打開所有粘貼了線纜布放表的頁(yè)面，設(shè)置粘貼位置，調(diào)整大小。調(diào)整大小的主要參數(shù)為size， size是選擇cx或者cy超過(guò)1中比較大的一個(gè)值進(jìn)行設(shè)置。22?31語(yǔ)句為設(shè)置size值。32?36調(diào)整線纜布放表中心點(diǎn)以及大小。

Sub adjusttable（）

Dim vsoapplication As Visio.Application

Dim vsopages As Visio.Pages

Dim vsoShapel As Visio.Shape

Dim i As Integer

Dim cx As Double

Dim cy As Double

Dim size As Double

Dim table_width As Double

Dim table_height As Double

Dim table_centerx As Double

Dim table_centery As Double

table_width = 253

table_height =130

table_centerx = 147

table_centery = 117

Set vsoapplication = Visio.Application

i = 1

Set vsopages = ActiveDocument.Pages

For i = 1 To vsopages.Count

Set vsopage = vsopages.Item（i）

If InStr（vsopage.Name， “A4模板”）=0 Then

Set vsoShapel = vsopage. Shapes.Item（2）

cx = vsoShapel.Cells（“width”）.Result（70）/ table_width

cy = vsoShapel.Cells（“height”）.Result（70）/ page_height

If cx >= cy And cx > 1 Then

size = cx

Elself cy >= cx And cy > 1 Then

size = cy

Else

size = 1

End If

vsoShapel.Cells（“pinx”）.Result（70）= table_centerx

vsoShapel .Cells（“piny”）.Result（70）= table_centery

vsoShapel.Cells（“width”）.Result（70）= vsoShapel.Cells（“width”）.Result（70）/ size

vsoShapel.Cells（“height” ）.Result（70）= vsoShapel.Cells（“height”）.Result（70）/ size

End If

Next i

End Sub

3 結(jié)語(yǔ)

隨著通信設(shè)計(jì)行業(yè)的競(jìng)爭(zhēng)日益激烈，提高工作效率和正確率是一個(gè)發(fā)展趨勢(shì)，對(duì)設(shè)計(jì)工作進(jìn)行自動(dòng)化處理可以實(shí)現(xiàn)這個(gè)目標(biāo)。對(duì)Excel/Visio/CAD/word的編程可以使用在很多地方，希望能在設(shè)計(jì)行業(yè)中更多地引入自動(dòng)化應(yīng)用。