writing a FUNCTION that returns value from web page

[torsteri]

Hi.
I'm trying to write a function that I can use to access information from an online database. I want to be able to write a formula in excel that retrieves company names from registration numbers.
Ie. "=regnumber(1234567890)" and return the name.

When written as a sub the code runs flawlessly,; however, when rewriting it as a function, it cannot read in from the webpage.

Can anyone help me?

[torsteri]

Sub test(orgnr As String)

Dim str As String
Dim counter As Double
Dim found As Integer
Dim text As String

counter = 1
found = 0

str = "http://hotell.difi.no/api/json/brreg/enhetsregisteret?orgnr=" & orgnr
'Workbooks.Open (str)


Set br = Workbooks.Open(str)


'text = Cells("A1").Value
'MsgBox text

Range("A1", "A2").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=True, OtherChar _
:=":", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12 _
, 1), Array(13, 1)), TrailingMinusNumbers:=True
MsgBox "Hit"


Do While found <> 1 And counter < 50
If Cells(1, counter).Value = "name" Then
found = 1
name = Cells(1, counter + 1).Value
End If
MsgBox counter & " " & name
counter = counter + 1
Loop

'Workbooks.Close


br.Close

End Sub

[torsteri]

Changed the code so that it actually works, sorry about that. But the problem still persists. The Set br = .... line gives br="" when the code is run as a function:

Sub test()

Dim str As String
Dim counter As Double
Dim orgnr As String
Dim found As Integer
Dim text As String

counter = 1
found = 0

orgnr="982463718"

str = "http://hotell.difi.no/api/json/brreg/enhetsregisteret?orgnr=" & orgnr

Set br = Workbooks.Open(str)


Range("A1", "A2").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=True, OtherChar _
:=":", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12 _
, 1), Array(13, 1)), TrailingMinusNumbers:=True


Do While found <> 1 And counter < 50
If Cells(1, counter).Value = "name" Then
found = 1
name = Cells(1, counter + 1).Value
End If
counter = counter + 1
Loop


br.Close

End Sub

[torsteri]

Really need help with this. Anyone have any idea?

[MrShorty]

As per forum rules (see the button at the top of the page), you need to put code tags around your code.

UDF's (Function procedures called from a spreadsheet cell) like you are describing have certain limitations. They cannot modify the spreadsheet environment. In your case, operations like Workbooks.open cannot be performed by a UDF. If you must do this as a UDF, you will need some way to do this without these kinds of commands.

Here's my thought: Separate the web query part of the operation from the lookup part of the operation. If you do this, you may not even need a UDF at all. Basic idea:

1) Run a web query and import into spreadsheet. I haven't ever done this from the web, but I've used the Import External Data command to get data from local text files all the time. From the help files and other sources, I'm reasonably certain that a variation on this command will allow you to import data from the web and even have it refresh periodically so you are always using the most recent information. This might be a useful resource http://office.microsoft.com/en-us/ex...010218472.aspx
2) Perform the lookup on this data table. A UDF can do the lookup using code like you've created, but, with the import operation separate, the lookup might be more efficiently done using VLOOKUP, or INDEX(Match()) functions.