Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel 2007 problem deleting an onsheet scrollbar | Excel Programming | |||
Deleting custom menu item createt in VBA | Excel Programming | |||
Item numbers result in item description in next field in Excel | Excel Worksheet Functions | |||
deleting line item in import function causes REF# error | Excel Worksheet Functions | |||
Excel VBA - deleting populated folder problem | Excel Programming |