Excel + user input + external data (URL manipulation)

[tehbloo]

Is there any way for excel to manipulate the values sent with the
external data (URL) request?

Here's the basic concept:
- User opens survey excel file on their desktop.
- Excel displays default data. Some of which is defined from a URL
(www.foo.com/search.php)
- Excel populates a drop down with part of the returned data (Company A
=> 1, Company B => 2, etc)
- User makes selection from drop down (Company B)
- Excel does a lookup to determine actual numeric value of Company B
(2)

--- Here's my sticking point, everything up till now is working as
needed ---
- Excel loads external data via modified URL
(www.foo.com/search.php?company=1)

I can build the string out, if needed, but I'm thinking there must be
an easier way. The search.php page can handle having or not having the
parameters.

Any thoughts on this? I've done an armload of searches, both google and
in these groups, to no avail, at least not what I believe I need answer
wise. I'm hoping to manipulate the parameters of the URL used to link
to external data. Doing something like this??

='http://www.foo.com/search.php?company='$Companies.$D2.

Make sense? Doable? Damn I hope so.
Thanks in advance!
Joe

 

[Bernie Deitrick]

Joe,

I don't know if this will help, but below is a UDF that queries a web-based form to return Zip plus
4 for an address.

When you do the URL manipulation, make sure you create a valid string:

strURL = "http://www.foo.com/search.php?company=" & Worksheets("SheetName").Range("D2").Value


HTH,
Bernie
MS Excel MVP

Option Explicit
Function ZipPlusFour(sAdd1 As String, _
sCity As String, _
sState As String _
) As String

Dim ie As Object
Dim sResult As String
Dim sCityState As String
Dim lStartCity As Long
Dim dtTimer As Date
Dim lAddTime As Long

Const sUPSURL As String = "http://zip4.usps.com/zip4/welcome.jsp"
Const lREADYSTATE_COMPLETE As Long = 4

Set ie = CreateObject("InternetExplorer.Application")
ie.silent = True
ie.navigate "http://zip4.usps.com/zip4/welcome.jsp"

dtTimer = Now
lAddTime = TimeValue("00:00:20")

Do Until ie.readystate = lREADYSTATE_COMPLETE And Not ie.busy
DoEvents
If dtTimer + lAddTime > Now Then Exit Do
Loop

ie.document.form1.address1.Value = sAdd1
ie.document.form1.City.Value = sCity
ie.document.form1.State.Value = sState
ie.document.form1.submit

dtTimer = Now
lAddTime = TimeValue("00:00:20")

Do Until ie.readystate = lREADYSTATE_COMPLETE And Not ie.busy
DoEvents
If dtTimer + lAddTime > Now Then Exit Do
Loop

sResult = ie.document.body.innertext
sCityState = sCity & " " & sState

lStartCity = InStr(1, sResult, sCityState, vbTextCompare)
lStartCity = InStr(lStartCity + 1, sResult, sCityState, vbTextCompare)

If lStartCity > 0 Then
ZipPlusFour = Mid(sResult, lStartCity + Len(sCityState) + 2, 10)
Else
ZipPlusFour = "Not Found"
End If

ie.Quit
Set ie = Nothing

End Function

 

[vezerid]

=HYPERLINK("http://www.foo.com/search.php?company="&'Companies"!D2)

Does this help?
Kostis Vezerides