extract "nCT" tag data from xml file to excel used vba

<cteProc xmlns="http://www.portalfiscal.inf.br/cte" versao="4.00" ipTransmissor="54.233.101.205" nPortaCon="26851" dhConexao="2025-03-14T19:57:55-03:00">
  <CTe xmlns="http://www.portalfiscal.inf.br/cte">
    <infCte versao="4.00" Id="CTe13250347705660000565570020000617541000617540">
      <ide>
        <cUF>13</cUF>
        <cCT>00061754</cCT>
        <CFOP>6352</CFOP>
        <natOp>PRESTACAO DE SERVICO DE TRANSPORTE A ESTABELECIMENTO INDUSTR</natOp>
        <mod>57</mod>
        <serie>2</serie>
        <nCT>61754</nCT>
        <dhEmi>2025-03-14T18:56:00-03:00</dhEmi>
        <tpImp>1</tpImp>
        <tpEmis>1</tpEmis>
        <cDV>0</cDV>
        <tpAmb>1</tpAmb>
        <tpCTe>0</tpCTe>
        <procEmi>0</procEmi>
        <verProc>3.0.7.9</verProc>
        <cMunEnv>1302603</cMunEnv>
        <xMunEnv>MANAUS</xMunEnv>
        <UFEnv>AM</UFEnv>
        <modal>06</modal>
        <tpServ>0</tpServ>
        <cMunIni>1302603</cMunIni>
        <xMunIni>MANAUS</xMunIni>
        <UFIni>AM</UFIni>
        <cMunFim>1100122</cMunFim>
        <xMunFim>JI-PARANA</xMunFim>
        <UFFim>RO</UFFim>
        <retira>1</retira>
        <indIEToma>1</indIEToma>
        <toma3>
          <toma>0</toma>
        </toma3>
      </ide>

I'm trying to extract the data from the "nCT" tag to Excel with the VBA codes below, but I can't.

Could you help improve the codes to extract data from the "nCT" tag from the above xml, thank you very much

Sub ExtrairXML()

Dim arquivo, arquivos
Dim declaração As New DOMDocument60

Dim nota As IXMLDOMNode
Dim notas As IXMLDOMNodeList

Dim dicionario As New Dictionary


Dim Id As String, nCT As String, serie As String

Dim chave As String


arquivos = Application.GetOpenFilename("Arquivos XML(*.xml),*.xml", , "Selecionar os arquivos XML", , True)

For Each arquivo In arquivos

declaração.Load (arquivo)

'On Error Resume Next

nCT = declaração.SelectSingleNode("//nCT").Text
serie = declaração.SelectSingleNode("//serie").Text
Id = declaração.SelectSingleNode("//@Id").Text


Set notas = declaração.SelectNodes("//infDoc")

For Each nota In notas

chave = nota.SelectSingleNode("infNFe/chave").Text


dicionario(dicionario.Count + 1) = Array(Id, nCT, serie, chave)


Next nota

Next arquivo

Planilha1.Range("A1:e1").Value = Array("Id", "nCT", "serie", "chave")


With Application

Planilha1.Range("a2").Resize(dicionario.Count, 5).Value = .Transpose(.Transpose(dicionario.Items))

End With


End Sub

above the vba code used to try to extract data from the xml file.

I tested the codes in other XML files and they worked perfectly, only in this XML file I am unable to extract the data from the mentioned tag.

generated the error below in debugging.

"the object variable or the with block variable was not defined."

Solution

Add a dummy namespace x:

Option Explicit

Sub ExtrairXML()

    Dim arquivo, arquivos
    Dim declaração As New DOMDocument60
    Dim nota As IXMLDOMNode
    Dim notas As IXMLDOMNodeList
    Dim Id As String, nCT As String, serie As String
    Dim chave As String, r As Long

    arquivos = Application.GetOpenFilename("Arquivos XML(*.xml),*.xml", , "Selecionar os arquivos XML", , True)

    ' add namespace
    declaração.SetProperty "SelectionNamespaces", "xmlns:x=""http://www.portalfiscal.inf.br/cte"""
    
    'header
    Planilha1.Range("A1:D1").Value = Array("Id", "nCT", "serie", "chave")
    
    For Each arquivo In arquivos

        declaração.Load arquivo
    
        nCT = declaração.SelectSingleNode("//x:nCT").Text
        serie = declaração.SelectSingleNode("//x:serie").Text
        Id = declaração.SelectSingleNode("//@Id").Text
        
        Set notas = declaração.SelectNodes("//x:infDoc")
        If notas.Length > 0 Then
            For Each nota In notas
                r = r + 1
                chave = nota.SelectSingleNode("x:infNFe/x:chave").Text
                Planilha1.Range("A1:D1").Offset(r) = Array(Id, nCT, serie, chave)
            Next nota
        Else
            r = r + 1
            Planilha1.Range("A1:C1").Offset(r) = Array(Id, nCT, serie)
        End If

    Next arquivo

End Sub