rikstechblogTo open Excel with ArcGIS VBA,
First, add Microsoft Object Library 12.0 in Tools>Reference.
Then use this code:
‘vba
Dim ExcelApp As Object
On Error Resume Next
Set ExcelApp = GetObject(, “Excel.Application”)
If ExcelApp Is Nothing Then ‘Excel isn’t already open
Set ExcelApp = CreateObject(“Excel.Application”)
End If
ExcelApp.Workbooks.Open (“C:\temp\New Microsoft Office Excel Worksheet.xls”)
ExcelApp.Visible = True
ExcelApp.Range(“A1”).value = 100
Sub CopyRenderer()
Dim pMxDoc As IMxDocument
Dim pMap As IMap
Set pMxDoc = Application.Document
Set pMap = pMxDoc.FocusMap
Dim pFromLayer As IGeoFeatureLayer
Dim pToLayer As IGeoFeatureLayer
Set pFromLayer = pMap.Layer(modCommon.GetLayerIndex(“Line10”))
Dim i As Integer
For i = 30 To 143
If TypeOf pMap.Layer(modCommon.GetLayerIndex(“Line” & i)) Is IGeoFeatureLayer Then
Set pToLayer = pMap.Layer(modCommon.GetLayerIndex(“Line” & i))
Set pToLayer.Renderer = pFromLayer.Renderer
End If
Next
pMxDoc.UpdateContents
End Sub
😦
Here are some tips when you use VBA to write codes:
1. SAVE ALL THE TIME. VBA does not save your codes automatically upon debug. When ArcGIS crashes, all your files are LOST.
2. DO NOT OPEN TWO VBA if possible. Because this is what happened to me: I overwrote I file with the other, and all my changes are lost! A whole day of work!
3. SAVE MODULES UNDER PROJECT, so that individual VBAs that open different projects will not overwrite each other.
😦
Dim pMyField3 As IFieldEdit
Set pMyField3 = New Field
With pMyField3
.Name = “Topo”
.Type = esriFieldTypeDouble
End With
Dim pTable3 As ITable
Set pTable3 = pFeatureLayer
pTable3.AddField pMyField3
Environment: VBA
Function: To reverse a collection
Dim Reversed As Collection
Set Reversed = New Collection ‘DONT FORGET THIS
For i = 1 To Collection.Count ‘Collection Starts with 1!
Reversed.Add(Collection.Item(Collection.Count – i + 1))
Next
WHAT! VBA collection does not allow you to alter an item!!!
And its Array object is weird. I still have not found out how to use it yet.
To substitute an item in a VBA collection, make this call:
pFeat.Add Item:=”Item”, Before:=j
pFeat.Remove j + 1
pFeature.Value(pFeature.Fields.FindField(“Topo”)) = 3
pFeature.Store
Dim pQueryFilter As IQueryFilter
Set pQueryFilter = New QueryFilter
pQueryFilter.WhereClause = “X > 1”
Dim pFeatureSelection As IFeatureSelection
Set pFeatureSelection = pFeatureLayer
pFeatureSelection.SelectFeatures pQueryFilter, esriSelectionResultNew, False
pmxDoc.ActiveView.PartialRefresh esriViewGeoSelection, Nothing, Nothing
How do you change the symbology of features in ArcGIS with VBA?
First, we have to work on IGeoFeatureLayer.
Cast the normal IFeatureLayer As IGeoFeatureLayer.
Then we can call the renderer by
IGeofeatureLayer.Renderer = pSimpleRenderer
where
With pSimpleRenderer
.Label = “State Border”
Set .Symbol = pSFSymbol
End With
and where
With pSFSymbol
.Color = pColor
End With
and where
Dim pColor As IRgbColor
Set pColor = New RgbColor
pColor.RGB = RGB(255, 255, 128)
http://edndoc.esri.com/arcobjects/8.3/?URL=/ArcObjectsOnline/Samples/ArcMap/ChangeRenderer.htm
Dim pDoc As IMxDocument
Set pDoc = ThisDocument
Dim pMap As IMap
Set pMap = pDoc.FocusMap
Dim pLayer As ILayer
Set pLayer = pMap.Layer(0)
Dim pFLayer As IFeatureLayer
Set pFLayer = pLayer
Dim pLyr As IGeoFeatureLayer
Set pLyr = pFLayer
‘Create a color
Dim pColor As IRgbColor
Set pColor = New RgbColor
pColor.RGB = RGB(255, 255, 128)
‘Create a symbol
Dim pSFSymbol As ISimpleMarkerSymbol
Set pSFSymbol = New SimpleMarkerSymbol
With pSFSymbol
.Color = pColor
End With
‘Create a renderer
Dim pSimpleRenderer As ISimpleRenderer
Set pSimpleRenderer = New SimpleRenderer
With pSimpleRenderer
.Label = “State Border”
Set .Symbol = pSFSymbol
End With
‘Set the layer’s renderer
Set pLyr.Renderer = pSimpleRenderer
‘ redraw the TOC and the map
pDoc.UpdateContents
pDoc.ActiveView.Refresh
rikstechblog 