VBA and macros carry the load
Keith is a software developer for Staff IT in Oak Ridge, TN. He can be contacted at [email protected].
Geographical Information Systems (GIS) are making inroads into applications ranging from utilities and facility management, to city planning and security, among others. In this article, I present a GIS application that lets you embed a map into a Microsoft Word document simply by highlighting an address in the text. As such, this article is a convergence of several distinct technologies: GIS, Visual Basic for Applications (VBA), web services, and word processing.
To build the sample program (actually, a Word macro), you need to have installed on your PC Microsoft Word 2002 (or higher), Internet Explorer 5.x (or higher), and the Microsoft SOAP Toolkit 3.0. However, to use the code I present here, you need to set up a web service on a protected intranet. You can easily replace my web service with your own or use a third-party subscription web service such as Microsoft's MapPoint (http://www .mappoint.com/).
Listing One is the code for the Word macro. The Word document that accompanies this article is available electronically; see "Resource Center," page 5. To view and open the Word document, press ALT+F11. The execution of the sample program is a two-part operation:
- Highlight a street address for Knoxville, Tennessee.
- Right click your mouse and choose "Validate address..." from the context menu.
If an invalid address is highlighted, a message box appears informing you that the address is invalid; otherwise, you are presented with a dialog box that lets you select various map "themes" (an aerial photo, zoning restrictions, and so on) as well as specifying a map size and scale.
Nearly all the heavy lifting in this program is performed by a web service that lives on the Internet (or an intranet) and performs discrete operations; in this example, the web service first validates an address, then returns a map at the request of users. The beauty of web services is that you don't have to know anything about their inner workings—you simply use them. Web services expose their methods, which makes it easy to know what data you have to pass in (in this example, an address as a string), and what you get back (again, a string representing the URL of a map). The other nice thing about web services is that they can be written in one language, but called from any other. My web service is written in Visual Basic; however, you could call it from a C# or C++ application. In short, web services go a long way toward delivering on the promise of seamless, heterogeneous, distributed computing.
The KGIS Address Web Service
The web service used by my Word macro is a product of Knoxville Geographic Information Systems (KGIS) of Knoxville, Tennessee. KGIS is responsible for maintaining all the geographical data for Knox County, and is a consortium of the city of Knoxville, Knox County, and the Knoxville Utilities Board (KUB). KGIS is thus responsible for supporting a wide user base with diverse data needs. The primary GIS tool is ArcIMS from ESRI (http://www.esri.com/). ArcIMS generates the map that gets inserted into a Word document when an address is validated. The web service I use in this article does not actually return a map image, but rather, the URL of the map image. The main point here is that a web service exists that validates an address (for Knox County, Tennessee) and returns you a reference to a map of that address.
The web service itself is integrated in a Microsoft Word document as a macro. This entire problem of inserting a map into Word can be broken down into three steps:
- Create the macro that calls the web service.
- Provide a way for the user to call the web service.
- Do something with the result.
To create a Word macro, select Tools-> Macro->Macros... from the toolbar. This launches a dialog that lists all current macros, plus, displays a Create button. Enter the name of your macro and click this button. This launches the VBA editor, which defaults to editing a subroutine with the same name as your macro from the previous screen. The code for my macro (available electronically; see Validate_Address()) is responsible for reading the address that users select (highlight), and configuring the structures needed by the web service. This is done by using the Selection object; see Example 1.
Saving the selected string is necessary for two reasons. First, you need to read the string containing the address. Second, if the address turns out to be valid and users want to see a map, I insert a blank line immediately below the highlighted address for inserting the map. I then rehighlight the address. Saving the starting/ending locations of the address string lets me do this. It also gives users visual feedback as to which address they selected.
One final caveat regarding macros—you may need to adjust your security settings in Word before this macro executes. Select Tools->Macros->Security... on the toolbar and enable macros.
Calling a Web Service
The next step is to provide a way for users to call the web service from inside Word. For my sample program, I modified the context menu that appears when you right-click in Word; see Figure 1. Begin by clicking Customize under Tools on the main menubar of Word. Select the Toolbars tab and check the box next to Shortcut Menus. That opens the shortcut menu bar editor; see Figure 2.
Next, select Text from the Shortcut Menus toolbar, and add the text for the context menu (Validate Address...). Then, on the Customize dialog, select Commands and then Macros. Add the name of the macro here; see Figure 3.
The Code
The code exists in a macro called Test(). In the sample Word document (available electronically), you can access the code by pressing ALT+F11 to open the macro editor. The code declares many different variables; some of these are related to calling my map service. The generic variables are concerned with getting the text that users have highlighted, namely, sSelection. The contents of this variable must be checked for nonvisible ASCII characters. The only thing you want to pass to the web service is a valid street address. Example 2 does just that.
Once you have the address, you have to validate it against the KGIS database (again, your project would use your database). If valid, the dialog box in Figure 4 appears, letting users select the properties of the map for the given address.
This dialog is called UserForm1 in the project. The Theme combobox is populated with the values Topographic, Aerial Photo, Case Parcel Map, and Zoning. The web service uses this information to return the type of map. Scale and Size are self explanatory. The Owner Data combobox is an additional criterion peculiar to the KGIS web service. Its values are Map ONLY, Owner Info., and Owner Info./Parcel ID. Normally just a map would be returned, but the other options allow information about the owner of the address and the parcel to be returned as well.
After users have made their selections on this dialog and clicked the OK button, the real fun starts. The structures used by the web service are completed and the web service is called using this line:
sMapURL =
myMap.wsm_GetMapByAddressMSLink
(CLng(Val (myAddrInfo.AddrMSLINK)),
myMapProps)
The web service returns the URL of a map that lives on the server. The next programming chore is to get this image into the Word document. This is done by first creating a temporary file in the user's TEMP directory, then copying the JPEG map image to this file. Once on the local machine, it's a snap to insert it into Word; see Example 3. The line in bold is a little-known API call that does all the work. According to the documentation, its function is to "Download bits from the Internet and save them to a file"—exactly what you want. Finally, a Selection object is created for an InLineShape, and the map/JPEG is assigned to the AddPicture method. You now have a map in your Word document.
Conclusion
There are a number of Internet sites that provides maps identifying stores, points of interests, and the like. With the merging of GIS with the Office tools, you can greatly extend and enhance the utility of your GIS-related applications.
DDJ
Private Declare Function URLDownloadToFile Lib "urlmon" Alias _
"URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, _
ByVal szFileName As String, ByVal dwReserved As Long, _
ByVal lpfnCB As Long) As Long
' This is the prototype for calling a Web service from inside a Word doc
Sub Test()
Dim sSelection As String
Dim sValidAddr, sTheme, sScale, sSize As String
Dim iStart, iEnd, iResult, iLastChar, k As Integer
Dim selOrig As Selection
Dim sMapURL As String
Dim bLoop As Boolean
Dim myMap As clsws_GetMap
Dim myMapProps As struct_MapSettings
Dim localFileName, szTempDir, szTempFileName As String
Dim fso As New FileSystemObject
Dim err As Long
'
Set selOrig = Selection
sSelection = Selection
iStart = Selection.Range.Start
iEnd = Selection.Range.End
iLastChar = Asc(Mid(sSelection, (iEnd - iStart), 1))
' Code to strip off a trailing <cr>, comma, space, or period
bLoop = True
k = 0
Do While bLoop '
iLastChar = Asc(Mid(sSelection, ((iEnd - k) - iStart), 1))
If ((iLastChar >= 65 And iLastChar <= 90) Or
(iLastChar >= 97 And iLastChar <= 122)) Then
bLoop = False
Else
k = k + 1
End If
Loop
sSelection = Left(sSelection, ((iEnd - k) - iStart))
Dim myGetAddress As clsws_GetAddress
Dim myAddrInfo As struct_AddressInfo
Dim mySearchInfo As struct_SearchProperties
Set mySearchInfo = New struct_SearchProperties
Set myGetAddress = New clsws_GetAddress
mySearchInfo.AddressStyle = "FullAddress"
mySearchInfo.SearchMethod = "Exact"
mySearchInfo.SpatialSearch = "None"
mySearchInfo.FullAddress = sSelection
Set myAddrInfo = myGetAddress.wsm_GetAddressInfo(mySearchInfo)
sValidAddr = myAddrInfo.MatchStatus
If sValidAddr = "Exact" Then
'
UserForm1.Show (vbModal) 'show UserForm1
iResult = UserForm1.sResult
sTheme = UserForm1.sTheme
sScale = UserForm1.sScale
sSize = UserForm1.sSize
Unload UserForm1 ' transfered values, so safe to unload the Form
' does user want a map or an owner card?
Select Case iResult
Case 1
' *************************
' * User wants a map *
' *************************
Selection.Paragraphs(1).Range.InsertParagraphAfter
Selection.MoveDown ' this is required!
'now get the map
Set myMap = New clsws_GetMap
Set myMapProps = New struct_MapSettings
myMapProps.MapScale = sScale ' set scale user picked
myMapProps.MapType = sTheme ' set theme user picked
Select Case sSize
Case "Small"
myMapProps.MapHeight = 300
myMapProps.MapWidth = 400
'
Case "Medium"
myMapProps.MapHeight = 400
myMapProps.MapWidth = 500
'
Case "Large"
myMapProps.MapHeight = 500
myMapProps.MapWidth = 600
End Select
'myMapProps.UseExtents = False
myMapProps.UseScale = True
sMapURL = myMap.wsm_GetMapByAddressMSLink(CLng(Val
(myAddrInfo.AddrMSLINK)), myMapProps)
Dim sMapParams() As String
sMapParams = Split(sMapURL, "|")
Dim obj As InlineShape
Dim sNewImage As String
'
szTempDir = Environ("TEMP") ' get temp. folder
szTempFileName = fso.GetTempName() ' get temp file name
' remove the .tmp extension and change to .jpg
szTempFileName = Left(szTempFileName, 9)
szTempFileName = szTempDir & "\" & szTempFileName & "jpg"
sNewImage = sMapParams(0) '
err = URLDownloadToFile(0,sNewImage,szTempFileName,0,0)
Selection.InlineShapes.AddPicture
FileName:=szTempFileName, SaveWithDocument:=True
Selection.MoveLeft Unit:=wdCharacter, Count:=1,
Extend:=wdExtend
With Selection.InlineShapes(1)
With .Borders(wdBorderLeft)
.LineStyle = wdLineStyleSingle
.LineWidth = wdLineWidth050pt
.Color = wdColorAutomatic
End With
With .Borders(wdBorderRight)
.LineStyle = wdLineStyleSingle
.LineWidth = wdLineWidth050pt
.Color = wdColorAutomatic
End With
With .Borders(wdBorderTop)
.LineStyle = wdLineStyleSingle
.LineWidth = wdLineWidth050pt
.Color = wdColorAutomatic
End With
With .Borders(wdBorderBottom)
.LineStyle = wdLineStyleSingle
.LineWidth = wdLineWidth050pt
.Color = wdColorAutomatic
End With
.Borders.Shadow = False
End With
With Options
.DefaultBorderLineStyle = wdLineStyleSingle
.DefaultBorderLineWidth = wdLineWidth050pt
.DefaultBorderColor = wdColorAutomatic
End With
' restore the highlight to orig. addr string
ActiveDocument.Range(iStart, iEnd).Select
' now delete the file in the TEMP directory
fso.DeleteFile szTempFileName, False
Case 2 ' user wants OWNER CARD info, no Parcel ID
Selection.Paragraphs(1).Range.InsertParagraphAfter
Selection.MoveDown ' this is required!
Set myMap = New clsws_GetMap
Set myMapProps = New struct_MapSettings
myMapProps.MapScale = sScale ' set scale user picked
myMapProps.MapType = sTheme ' set theme user picked
myMapProps.MapHeight = 500
myMapProps.MapWidth = 600
myMapProps.UseScale = True
sMapURL = myMap.wsm_GetMapByAddressMSLink(CLng(
Val(myAddrInfo.AddrMSLINK)), myMapProps)
Selection.InsertAfter Text:=vbCrLf
Selection.InsertAfter Text:=CStr(myAddrInfo.Owner) & vbCrLf
Selection.InsertAfter Text:=
CStr(myAddrInfo.OwnerMailingAddr_1) & vbCrLf
Selection.InsertAfter Text:=
CStr(myAddrInfo.OwnerMailingAddr_2) & vbCrLf
'
ActiveDocument.Range(iStart, iEnd).Select ' restore original selection
Case 3 ' user wants OWNER CARD info, and Parcel ID
Selection.Paragraphs(1).Range.InsertParagraphAfter
Selection.MoveDown ' this is required!
Set myMap = New clsws_GetMap
Set myMapProps = New struct_MapSettings
myMapProps.MapScale = sScale ' set scale user picked
myMapProps.MapType = sTheme ' set theme user picked
myMapProps.MapHeight = 500
myMapProps.MapWidth = 600
'myMapProps.UseExtents = False
myMapProps.UseScale = True
sMapURL = myMap.wsm_GetMapByAddressMSLink(CLng(
Val(myAddrInfo.AddrMSLINK)), myMapProps)
Selection.InsertAfter Text:=vbCrLf
Selection.InsertAfter Text:=CStr(myAddrInfo.Owner) & vbCrLf
Selection.InsertAfter Text:=
CStr(myAddrInfo.OwnerMailingAddr_1) & vbCrLf
Selection.InsertAfter Text:=
CStr(myAddrInfo.OwnerMailingAddr_2) & vbCrLf
Selection.InsertAfter Text:=CStr(myAddrInfo.ParcelID)
Selection.InsertAfter Text:=vbCrLf
'
ActiveDocument.Range(iStart, iEnd).Select
' restore original selection
End Select
Else
MsgBox "That address is NOT VALID!", vbCritical Or vbOKOnly
End If
End Sub
Back to article
