Excel deleting xml item problem
 

I have an excel macro (in an add-in) that is called by the main excel macro
that is deleting various items in an xml file

When it gets to the last line as below it generates the following message

€œAn unhandled error occurred: Object variable or with block not set€�

the variable is dimensioned see Dim statement that I have copied here. This
has worked for some time with no problem but suddenly out of the blue
generates this error. I have tried replacing the xml file with a copy, also
tried replacing the add-in with a copy (in case either have been corrupted).
The error description is per €œErr.Description€�

Dim oInstNode As IXMLDOMNode

Set oReplacedNode = oxml.selectSingleNode("/InstrumentList")

Set oCheckNode = oReplacedNode.selectSingleNode("Instrument[OMR='" &
OMRCode & "']")

Any ideas will be gratefully received

--
with kind regards

Spike

Excel deleting xml item problem
 

I think the whole piece of code might help

the row varialble Set oReplacedNode shows "Nothing" which i guess is the
problem

Public Sub delInstrumentCode(ByVal OMRCode As String, ByVal filename As
String)
Dim oxml As DOMDocument
Dim oInstNode As IXMLDOMNode
Dim oReplacedNode As IXMLDOMNode
Dim bLoadSuccess As Boolean
Dim ofs As FileSystemObject

'InstrumentList/Instrument/OMR
'InstrumentList/Instrument/desc

On Error GoTo errHandle

Set ofs = New FileSystemObject
Set oxml = New DOMDocument

If Not ofs.FileExists(filename) Then
MsgBox "The file " & filename & "Cannot be found"
Exit Sub
End If

bLoadSuccess = oxml.Load(filename)

If Not bLoadSuccess Then
MsgBox "The file " & filename & " failed to load correctly." & vbCr
& _
"Error: " & oxml.parseError.reason, vbCritical, "Save Ticker
History Error"
Exit Sub
End If

Set oReplacedNode = oxml.selectSingleNode("/InstrumentList")

Set oInstNode = oReplacedNode.selectSingleNode("Instrument[OMR='" &
OMRCode & "']")
If Not oInstNode Is Nothing Then
Call oReplacedNode.removeChild(oInstNode)
oxml.Save filename
End If

Set oxml = Nothing
Exit Sub
errHandle:
MsgBox "An unhandled error occured: " & Err.Description, vbCritical
End Sub
--
with kind regards

Spike


"Spike" wrote:

I have an excel macro (in an add-in) that is called by the main excel macro
that is deleting various items in an xml file

When it gets to the last line as below it generates the following message

€œAn unhandled error occurred: Object variable or with block not set€�

the variable is dimensioned see Dim statement that I have copied here. This
has worked for some time with no problem but suddenly out of the blue
generates this error. I have tried replacing the xml file with a copy, also
tried replacing the add-in with a copy (in case either have been corrupted).
The error description is per €œErr.Description€�

Dim oInstNode As IXMLDOMNode

Set oReplacedNode = oxml.selectSingleNode("/InstrumentList")

Set oCheckNode = oReplacedNode.selectSingleNode("Instrument[OMR='" &
OMRCode & "']")

Any ideas will be gratefully received

--
with kind regards

Spike

Excel deleting xml item problem
 

You may of changing your error trapping option there are 3 option in
the following VBA menu


Tools - Options - General - Error trapping.

I usually set my PC to trap on all errors. Selecting unhandled errrors
often causes proplems because athe code skips past 1 error and gets
stuck on a 2nd error and people can't figure out how to solve resolve
the 2nd errror. I like to fix the rott causes of problems and fix the
1st error.


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=182129

Microsoft Office Help

Excel deleting xml item problem
 

found the error - it was referencing the wrong file. Fairly basic i guess,
it had been hard coded in by someone!
--
with kind regards

Spike


"Spike" wrote:

I think the whole piece of code might help

the row varialble Set oReplacedNode shows "Nothing" which i guess is the
problem

Public Sub delInstrumentCode(ByVal OMRCode As String, ByVal filename As
String)
Dim oxml As DOMDocument
Dim oInstNode As IXMLDOMNode
Dim oReplacedNode As IXMLDOMNode
Dim bLoadSuccess As Boolean
Dim ofs As FileSystemObject

'InstrumentList/Instrument/OMR
'InstrumentList/Instrument/desc

On Error GoTo errHandle

Set ofs = New FileSystemObject
Set oxml = New DOMDocument

If Not ofs.FileExists(filename) Then
MsgBox "The file " & filename & "Cannot be found"
Exit Sub
End If

bLoadSuccess = oxml.Load(filename)

If Not bLoadSuccess Then
MsgBox "The file " & filename & " failed to load correctly." & vbCr
& _
"Error: " & oxml.parseError.reason, vbCritical, "Save Ticker
History Error"
Exit Sub
End If

Set oReplacedNode = oxml.selectSingleNode("/InstrumentList")

Set oInstNode = oReplacedNode.selectSingleNode("Instrument[OMR='" &
OMRCode & "']")
If Not oInstNode Is Nothing Then
Call oReplacedNode.removeChild(oInstNode)
oxml.Save filename
End If

Set oxml = Nothing
Exit Sub
errHandle:
MsgBox "An unhandled error occured: " & Err.Description, vbCritical
End Sub
--
with kind regards

Spike


"Spike" wrote:

I have an excel macro (in an add-in) that is called by the main excel macro
that is deleting various items in an xml file

When it gets to the last line as below it generates the following message

€œAn unhandled error occurred: Object variable or with block not set€�

the variable is dimensioned see Dim statement that I have copied here. This
has worked for some time with no problem but suddenly out of the blue
generates this error. I have tried replacing the xml file with a copy, also
tried replacing the add-in with a copy (in case either have been corrupted).
The error description is per €œErr.Description€�

Dim oInstNode As IXMLDOMNode

Set oReplacedNode = oxml.selectSingleNode("/InstrumentList")

Set oCheckNode = oReplacedNode.selectSingleNode("Instrument[OMR='" &
OMRCode & "']")

Any ideas will be gratefully received

--
with kind regards

Spike