1

I'm trying to join an excel table to a shapefile and then calculating those fields using vb.net. The code I have written has no errors, but won't work when I try it in ArcGIS 10.1 and I'm out of ideas. If anybody can find an error in my code or direct me to other sources, I'd gladly appreciate it.

ModuleQ3 Code:

Public Sub JoinCalc(ByVal excelXLS As String, ByVal fromExcelTable As String, ByVal toFeatureClass As String, ByVal toField As String, ByVal fromField As String, ByVal calculateField As String, ByVal calculatorExpression As String)
    Dim pMxDoc As IMxDocument
    Dim pMap As IMap
    Dim pFeatureLayer As IFeatureLayer
    Dim pFeatureClass As IFeatureClass
    Dim featureIndex As Integer

    pMxDoc = My.ArcMap.Application.Document
    pMap = pMxDoc.FocusMap

    featureIndex = FindLayer(toFeatureClass)

    If featureIndex = -1 Then
        MsgBox("The join feature class is not in the current map document.")
        Exit Sub
    End If

    pFeatureLayer = pMap.Layer(featureIndex)
    pFeatureClass = pFeatureLayer.FeatureClass

    Dim pWorkspaceFactory As IWorkspaceFactory
    pWorkspaceFactory = New ExcelWorkspaceFactory

    'open excel workbook 
    Dim pFworkspace As IFeatureWorkspace
    pFworkspace = pWorkspaceFactory.OpenFromFile(excelXLS, 0)

    'open excel table 
    Dim pFromTable As ITable
    pFromTable = pFworkspace.OpenTable(fromExcelTable)

    'Join the table to the feature class. 
    Dim pMemRelFact As IMemoryRelationshipClassFactory
    Dim pRelClass As IRelationshipClass
    Dim pDispRC As IDisplayRelationshipClass

    ' Create a memory relationship class. 
    pMemRelFact = New MemoryRelationshipClassFactory
    pRelClass = pMemRelFact.Open("Table-Layer", pFromTable, fromField, pFeatureClass, toField, "FeatureClassName", "TableName", ESRI.ArcGIS.Geodatabase.esriRelCardinality.esriRelCardinalityOneToOne)

    ' Perform a join. 
    pDispRC = pFeatureLayer
    pDispRC.DisplayRelationshipClass(pRelClass, ESRI.ArcGIS.Geodatabase.esriJoinType.esriLeftInnerJoin)

    MsgBox("The join is complete")

    Dim pDisplayTable As IDisplayTable
    pDisplayTable = pFeatureLayer

    Dim pFields As IFields
    pFields = pFeatureClass.Fields

    Dim fieldIndex As Integer
    fieldIndex = pFields.FindField(calculateField)

    If fieldIndex <> -1 Then
        Dim pCursor As ICursor
        pCursor = pDisplayTable.SearchDisplayTable(Nothing, True)

        Dim pCalculator As ICalculator
        pCalculator = New Calculator
        With pCalculator
            .Cursor = pCursor
            .Expression = calculatorExpression
            .Field = calculateField
        End With

        pCalculator.Calculate()

        MsgBox("It should be calculated.")

    Else
        MsgBox("The field does not exist")
        Exit Sub
    End If
End Sub

These are the parameters that I am passing through:

ModuleQ3.JoinCalc("ONT_Climate_Normals.xlsx", "ONT_Annual", "ONT_Stations_Shape", "CLIMATE_ID", "CLIMATE_ID", "TotalAnnua", "NORMAL_52 + NORMAL_54")
  • Can you edit your question and show us what the parameters are that you are passing into this procedure? – Hornbydd Mar 20 '14 at 9:39
  • I've always found the interface between ESRI and Microsoft Excel to be a bit flaky; I've always had better results importing into a personal geodatabase using Microsoft Access. Of course you're in VB so you can use Excel objects and get the values by row and column thereby coding your own join-and-calc from scratch - a lot of extra code but more robust. – Michael Stimson Mar 28 '14 at 1:28
1

I think the error maybe your calculateField. When you join tables you get a qualified field name this is:

Tablename.fieldname

So in your case it will be the FeatureClass name and the field name you are calculating into and in your code I think this would be:

ONT_Stations_Shape.TotalAnnua

So you need to be passing in as the CalculateField parameter "ONT_Stations_Shape.TotalAnnua"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.