Solved How to fix 'Run time error 424' in vb script

September 17, 2016 at 09:01:14
Specs: Windows 7
im not a familiar with programming basics.

im getting 'Run time error 424 ' object required, when executing the script provided in #5

im using the script as macro in Excel, any idea what im doing wrong?

Sub XMLFind()
'==== begin vbscript "xmlfind"
If wscript.arguments.Count < 1 Then
wscript.echo "Usage: XMLFIND C:\Users\vik\Documents\XMLTest\CD_catalog.xml targetstring"
wscript.Quit
End If
targ = wscript.arguments(1)
Set fso = CreateObject("scripting.filesystemobject")
Set test = fso.opentextfile(wscript.arguments(0), 1)
Z = test.readall
'remove lcase() from foll line to observe case in search
point1 = InStr(LCase(Z), LCase(targ))
If point1 = 0 Then
wscript.echo "NOT FOUND: string: " & targ
wscript.Quit
End If
point1 = point1 + Len(targ)
found = LTrim(Mid(Z, point1))
quote = Left(found, 1)
found = Mid(found, 2)
point2 = InStr(found, quote)
If point2 > 0 Then
found = Left(found, point2 - 1)
End If
wscript.echo Chr(34) & found & Chr(34)
'===== end vbscript
End Sub

Script obtained from below link:
http://www.computing.net/answers/pr...


End result:
what i'm trying to do is to create macro or batch to extract a field name & its value from a xml file.

The XML file im using is contain 4 or 5 level nodes.

message edited by vennpura


See More: How to fix Run time error 424 in vb script

Reply ↓  Report •


✔ Best Answer
September 19, 2016 at 15:09:28
So your biggest problem is you're taking VBS and trying to use it as VBA. As you can clearly see, they're 33% different.

I don't have easy access to Excel at the moment, but from memory, something like this?:

Sub XMLFind()
  attributeName = "//TITLE"
  fullPath = CreateObject("Scripting.FileSystemObject") _
   .GetAbsolutePathName("Path and name to XML file here")
  Set XML = CreateObject("Msxml2.DOMDocument.6.0")
  XML.setProperty "SelectionLanguage", "XPath"
  XML.setProperty "ProhibitDTD", False
  XML.ValidateOnParse = False
  
  If Not XML.Load(fullPath) Then
    MsgBox "Failed to parse: " & fullPath _
    & vbNewLine & XML.parseError.reason
    Exit Sub
  End If
  
  For Each Node In XML.SelectNodes(attributeName)
    Selection = Node.Text
    Selection.Offsest(1, 0).Select
  Next 'node
End Sub

How To Ask Questions The Smart Way



#1
September 17, 2016 at 09:30:27
Read the last entry on that page. There's another script written by me. See if that version works for you. If it doesn't, you'll need to specify which line is giving you an error, so we can see which object is required.

How To Ask Questions The Smart Way

message edited by Razor2.3


Reply ↓  Report •

#2
September 17, 2016 at 10:59:26
i used the following script:
I updated this name on this line : attributeName = "//@TITLE

where do i define the xml file name?

when i run the script below after compiling it, it failing at this line for object required:
attributeName = "//@TITLE" & wscript.Arguments(1)

when i move the cursor to the line, its showing below value:
attributeName = Empty

Sub XMLFind()
attributeName = "//@TITLE" & wscript.Arguments(1)
fullPath = CreateObject("Scripting.FileSystemObject") _
.GetAbsolutePathName(wscript.Arguments(0))
Set XML = CreateObject("Msxml2.DOMDocument.6.0")
XML.setProperty "SelectionLanguage", "XPath"
XML.setProperty "ProhibitDTD", False
XML.ValidateOnParse = False

If Not XML.Load(fullPath) Then
wscript.StdErr.WriteLine "Failed to parse: " & fullPath _
& vbNewLine & XML.parseError.reason
wscript.Quit 1
End If

For Each Node In XML.SelectNodes(attributeName)
wscript.StdOut.WriteLine Node.Text
Next 'node


End Sub


This is the simple xml im using to test:

<?xml version="1.0" encoding="utf-8" ?>
- <CATALOG>
- <CD>
<TITLE>Empire Burlesque</TITLE>
<ARTIST>Bob Dylan</ARTIST>
<COUNTRY>USA</COUNTRY>
<COMPANY>Columbia</COMPANY>
<PRICE>10.90</PRICE>
<YEAR>1985</YEAR>
</CD>
- <CD>
<TITLE>Hide your heart</TITLE>
<ARTIST>Bonnie Tylor</ARTIST>
<COUNTRY>UK</COUNTRY>
<COMPANY>CBS Records</COMPANY>
<PRICE>9.90</PRICE>
<YEAR>1988</YEAR>
</CD>
</CATALOG>



Reply ↓  Report •

#3
September 17, 2016 at 21:59:24
First I suggest trying out any script from the command prompt to see if it works there. I'm not familiar enough with excel macros to trust that the script is being executed as intended (by the author of the script). It also makes debugging easier since you can plant signals and messages into the script.
Second, I got Razors script to work on another xml file, but only if it had "non-top-level" elements. I think this means it has to precede an equals sign, because it extracted those just fine (this from my own test file):
<Main display="NORMAL">
<Port name="read_abc" exe="NO">
It would not take Port or Main, considered "top level" tags. It WOULD take "name" and "display" and give me the assoc. value. Your file does not have any non-top-level elements - no equals signs in the file.
Third, as for the "object required" error, in my script you referred to it can only be one of these lines:
set fso=createobject("scripting.filesystemobject")
set test=fso.opentextfile(wscript.arguments(0),1)
and since the first is "almost" universal, the second line, which expects a path+filename
as its argument, is probably deficient or defective. You can test by replacing "wscript.arguments(0)" with an existing path+filename in quotes. The file is opened for read-access, so it should be safe.
This prototype vbscript attempts to separate tags from value in basic html/xml files:
'==== begin vbscript "htmex.vbs": usage: CSCRIPT /NOLOGO HTMEX.VBS path+filename
fil=wscript.arguments(0)
set fso=createobject("scripting.filesystemobject")
if not fso.fileexists(fil) then
wscript.echo "file not found: "&fil
wscript.quit
end if
x=fso.opentextfile(fil).readall
'-- the following is to handle random line-breaks which can occur in html
x=replace(x,vbcrlf,"")
z=split(x,">")
'wscript.echo ubound(z)
for i=0 to ubound(z)
'wscript.echo z(i)
a=split(z(i)+"<","<")
'-- debugging display next two lines
wscript.echo "value: "&a(0)
wscript.echo "tag: "&a(1)
'-- for tags, use 1 instead of zero in foll. line
oput=oput+a(0)+vbcrlf
next
'wscript.echo "----------------- final output:"
wscript.echo oput
'fso.opentextfile("testout",2,true).write oput
'----------- end vbscript htmex.vbs
This is intended for running from the command prompt. probably won't work inside of excel. It is not a "solution", just a foundation on which a possible solution could be built, output format is "bare bones".

Reply ↓  Report •

Related Solutions

#4
September 19, 2016 at 15:09:28
✔ Best Answer
So your biggest problem is you're taking VBS and trying to use it as VBA. As you can clearly see, they're 33% different.

I don't have easy access to Excel at the moment, but from memory, something like this?:

Sub XMLFind()
  attributeName = "//TITLE"
  fullPath = CreateObject("Scripting.FileSystemObject") _
   .GetAbsolutePathName("Path and name to XML file here")
  Set XML = CreateObject("Msxml2.DOMDocument.6.0")
  XML.setProperty "SelectionLanguage", "XPath"
  XML.setProperty "ProhibitDTD", False
  XML.ValidateOnParse = False
  
  If Not XML.Load(fullPath) Then
    MsgBox "Failed to parse: " & fullPath _
    & vbNewLine & XML.parseError.reason
    Exit Sub
  End If
  
  For Each Node In XML.SelectNodes(attributeName)
    Selection = Node.Text
    Selection.Offsest(1, 0).Select
  Next 'node
End Sub

How To Ask Questions The Smart Way


Reply ↓  Report •

#5
September 26, 2016 at 20:08:19
Nbrane, thanks for helping out. i did try the script in command line, the 'Windows Script Host' keep on popping up each xml fields & values. Had to kill the process to exit.


Thank You Razor. I assumed that Vbs & VBA script is same..Thanks for pointing it out.

VBA script is working as expected. i will play around with different node levels and so.

Thank you


Reply ↓  Report •


Ask Question