Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel + user input + external data (URL manipulation)
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel + user input + external data (URL manipulation)
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 wrote in message ps.com... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel will not automatically refresh external data on open | Excel Discussion (Misc queries) | |||
Excel 97 external data tables in Excel 2003 | Excel Discussion (Misc queries) | |||
Inserting a new line when external data changes | Excel Discussion (Misc queries) | |||
How do I use Excel to post data to an external database? | Excel Discussion (Misc queries) | |||
Excel 97/2000 - Help with looking up external data. | Excel Worksheet Functions |