VBA Hyperlink calling ASP page

[David J. Miller]

Is it possible for the HYPERLINK() function to call and ASP page, including
passing arguments (e.g http://myisp/mypage.asp?data=xxx&moredata-yyyy). I
can't seem to get this to work, or find reasonable examples googling. Can
someone point me in the right direction.

Thanks-
Dave Miller
(e-mail address removed)

 

[Robin Hammond]

David,

I'm not sure about the hyperlink function but I am regularly posting and
retrieving information to asp pages using the MSXML4 library.

e.g.

Public Function Test(SecurityID As String) As Variant
'Accesses an asp page to retrieve information from the asp response object
'Requires a reference to MSXML4, preferably SP2, available on msdn
Dim strURL As String
Dim strParams as string
Dim vValue As Variant
Dim lRandom As Long
Dim oXML As MSXML2.XMLHTTP40
'check valid parameter and net connection state here
Set oXML = New MSXML2.XMLHTTP40
'create a variable that will force a data refresh
'because the asp server can cache return values
lRandom = Int(10000 * Rnd)
strParams = "?SecID=" & SecurityID & "&Random=" & lRandom
strURL = "http://nnn.nnn.nnn.nnn/whatever.asp" & strParams
oXML.Open "GET", strURL, False
oXML.setRequestHeader "cache-control", "no-cache"
oXML.setRequestHeader "pragma", "no-cache"
'send the information
oXML.Send
'you need to know what the asp page sends back in its response object
'here to decipher the results
vValue = oXML.responseText
'check for an error response here
'I am getting numeric values, so convert the datatype
Test= CDbl(vValue)
Set oXML = Nothing
End Function

HTH,

Robin Hammond
www.enhanceddatasystems.com

 

[David J. Miller]

In this case, I just want a web browser window to open pointing to the page
that was calcuated based on the excel sheet. I don't need to retrieve data,
it's for "reference" only on screen.

Is there some easier / better way to open a browser window?

Thanks-
Dave

 

[Robin Hammond]

That is far simpler. You could set up a hidden sheet, or if you are in an
add-in use a worksheet in the add in, and do something like this.

Sub TestHyp()
With ThisWorkbook.Sheets(1)
.Hyperlinks.Delete
.Hyperlinks.Add .Range("a1"), _
"http://finance.yahoo.com/q?s=^dji"
.Hyperlinks(1).Follow
End With
End Sub

Robin Hammond
www.enhanceddatasystems.com