I am trying to run as a macro a custom Excel function XpathOnUrl
from the add-in called SeoTools by Niels Bosma. The function runs fine and it seems that I am able to store its result in a variable. This variable can then be correctly output to an Excel cell, but when I try to look for a string in it in the next part of the macro, I get the error Run-time error '13': Type mismatch
. From what I understand from here, the function returns an array, but when I try to access it as the first item of the array, I get the same error. I tried to convert the variable into a string with CStr
, but no luck there either. What am I missing?
Here's the problematic part of the code:
WebSite = Sheet1.Range("A1")
contactPage = Application.Run("XPathOnUrl", WebSite, "//a[contains(translate(@href, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', 'abcdefghijklmnopqrstuvwxyz'),""contact"")]", "href")
MsgBox TypeName(contactPage) 'trying to find out the data type results in 'Error'
If Left(contactPage(0), 4) = "http" Then
Sheet1.Range("B1").Value = contactPage
ElseIf InStr(contactPage, "/") = 1 Then
Sheet1.Range("B1").Value = WebSite & contactPage
End If
Just to make it clear: the problem starts only with conditional statements. If I assign the value of the variable directly to a cell like this Sheet1.Range("B1").Value = contactPage
, it outputs the correct result.
Here's a easy workaround:
Make XpathURL
spit its return to a range. Then, use Range.value
to assign the return to a contactpage
and clear the range using .Clearcontents
property. I think Application.Run
is not letting XpathURL
return get to contactpage
.
Edit: Added the comment below:
Sheet1.Range("B1").Value = Application.Run("XPathOnUrl", WebSite, "//a[contains(translate(@href, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', 'abcdefghijklmnopqrstuvwxyz'),""contact"")]", "href")
contactPage = Sheet1.Range("B1").Value
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments