Can't find XML Node when editing Element Value in simple Excel sheet

Question

I'm working with a simple XML file for generating Excel files. The problem I'm having is editing Worksheet names. Every time I try to update the value, I get a NullReferenceException.

Here's the XML:

<?xml version="1.0"?>
    <?mso-application progid="Excel.Sheet"?>
<Workbook
   xmlns="urn:schemas-microsoft-com:office:spreadsheet"
   xmlns:o="urn:schemas-microsoft-com:office:office"
   xmlns:x="urn:schemas-microsoft-com:office:excel"
   xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
   xmlns:html="http://www.w3.org/TR/REC-html40">
  <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
  <Author>Me</Author>
  <Company>RMyCo, LLC</Company>
  <Version>1.0</Version>
  </DocumentProperties>
  <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
    <WindowHeight>6795</WindowHeight>
    <WindowWidth>8460</WindowWidth>
    <WindowTopX>120</WindowTopX>
    <WindowTopY>15</WindowTopY>
    <ProtectStructure>False</ProtectStructure>
    <ProtectWindows>False</ProtectWindows>
  </ExcelWorkbook>
  <Styles>
    <Style ss:ID="Default" ss:Name="Normal">
      <Alignment ss:Vertical="Bottom" />
      <Borders />
      <Font />
      <Interior />
      <NumberFormat />
      <Protection />
    </Style>
    <Style ss:ID="s21">
      <Font x:Family="Swiss" ss:Bold="1" />
    </Style>
  </Styles>
  <Worksheet ss:Name="Sheet1">
    <Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="5"
       x:FullColumns="1" x:FullRows="1">
     </Table>
    <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
      <Print>
        <ValidPrinterInfo />
        <HorizontalResolution>600</HorizontalResolution>
        <VerticalResolution>600</VerticalResolution>
      </Print>
      <Selected />
      <Panes>
        <Pane>
          <Number>3</Number>
          <ActiveRow>5</ActiveRow>
          <ActiveCol>1</ActiveCol>
        </Pane>
      </Panes>
      <Table>
      </Table>
      <ProtectObjects>False</ProtectObjects>
      <ProtectScenarios>False</ProtectScenarios>
    </WorksheetOptions>
  </Worksheet>
</Workbook>

Here's the code:

Imports System
Imports System.Reflection
Imports System.Xml
Imports System.Xml.Linq

Public Class ExcelXML


Public Function RenameSheet(FileNamePath As String, OldSheetName As String, NewSheetName As String) As Integer

        Dim xDoc As New XmlDocument
        Dim xRead As New XmlTextReader(FileNamePath)
        Dim xNode As XmlNode

        xDoc.Load(xRead)

        xNode = xDoc.SelectSingleNode("/Workbook/Worksheet[@Name='Sheet1']")

        xNode.Attributes("Name").Value = NewSheetName

        xDoc.Save(FileNamePath)
        xRead.Close()


        Return 0

    End Function

End Class

It dies whenever I try to rename a sheet.

xNode.Attributes("Name").Value = NewSheetName

Answer 1

Your SelectSingleNode() returned Nothing, and operating on a Nothing guarantees a NullReferenceException (read the linked question for more explanation on this topic).

Your XML has various namespaces and they need to be considered in your XPath. Two relevant namespaces in this case are the default namespace (the one without prefix, like xmlns="..."), and the ss namespace prefix.

To get elements/attribute in namespace using XmlDoocument you need to register prefix-to-namespace_uri mapping to an XmlNamespaceManager, then use the registered prefixes properly in the XPath :

......

'initialize namespace manager'
Dim nsManager As New XmlNamespaceManager(New NameTable())

'register namespace prefixes'
nsManager.AddNamespace("d", "urn:schemas-microsoft-com:office:spreadsheet")
nsManager.AddNamespace("ss", "urn:schemas-microsoft-com:office:spreadsheet")

'modify the XPath to utilize prefixes, and pass namespace manager as 2nd args'
Dim xNode = xDoc.SelectSingleNode("/d:Workbook/d:Worksheet[@ss:Name='Sheet1']", nsManager)
xNode.Attributes("ss:Name").Value = NewSheetName

.......