吳乙飛

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

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

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

1 宏的基本概念

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

2 實(shí)例分析

2.1 實(shí)例的功能

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

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

2.2 Excel中的線纜布放表格式

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

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

2.3 Visio中的線纜布放表

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

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

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

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

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

幾個常用的名稱如表3所示。

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

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

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

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

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

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

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

整個程序邏輯如圖2所示。

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

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

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

程序12?21句為一個循環(huán)語句，它尋找“序號”，如果找到，表明一個新的線纜布放表的開始，第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)識

“序號”

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

j = l

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

IfCells（l，l）.Value= “序號” 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版本可以支持的程序略有不同。在這個Vision中有兩個程序。

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

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

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

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

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