Javascript out od Excel

C

chs245

New Member
Joined
Oct 6, 2002
Messages
24
Hi

I would like to automatise a process whereby a request data from the internet and import it into excel. Unfortunately, the website in question uses javascript to generate my data request when I do it through the Internet Explorer.
Is it possible to write a macro in Excel that pulls the data from the webserver ? Currently, if I try to do it with the WebQuery tool from Excel, I receive the error message that the server returned no data.
Many thanks

Oliver
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
Thanks Mike.
I had a look at the example you mentioned. I think the code probably has to be different for my problem, since the source code (attached below uses the following post method:


The site is protected by username and password, so unfortunately I can't give anyone access to the web page in queston. The source code is attached below.
I usually press the search button on the page which starts the runSeach() javascript. The result will be a new webpage with a long list of document references.

How could I do that from Excel with the code that published ? In this example I request documents for "AA" for the last 730 days, with a maximum of 300 results.

Regards,

Oliver


Code:
Rich (BB code): 
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2 Final//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html"; > 
	<TITLE>FCAdvancedSearchCriteriaC</TITLE>
<link rel="stylesheet" type="text/css" href="/FIRSTCALL/APPS/FC622/eng/FCStyle.css">

<SCRIPT LANGUAGE="JavaScript">
function changePage(contentsID, contentsValue)
{
	newURL = "/cgi-bin/gx.cgi/AppLogic+FIRSTCALL.APPS.FC622.FCAdvancedSearch.FCAdvancedSearchInterface?PRODUCT=" +
            escape(document.advSearch.PRODUCT.value) +
            "&CMD=EDITCRIT&webToken=" + contentsID + "&webValue=" + escape(contentsValue);

	// add timestamp to URL so that URL will appear different to
	// IE browser, thus fixing cache issue
	tempDate = new Date ();
	newURL += "&DONT_CACHE=" + (tempDate.getTime());

	if (contentsID.indexOf('|') > 0)
		contentsID = contentsID.substring(0, contentsID.indexOf('|'));

	if (parent.A.document.pick_it.pick_a_criteria)
	{
		for (i = 0; i < parent.A.document.pick_it.pick_a_criteria.length; i++)
		{
			if (contentsID == parent.A.document.pick_it.pick_a_criteria.options.value)
			{
				parent.A.document.pick_it.pick_a_criteria.selectedIndex = i;
			}
		}
	}

	 parent.B.location.href=newURL;
}

// send form parameters to saved search applogic to save them.
function popupDialog()
{
	// add timestamp to URL so that URL will appear different to
	// IE browser, thus fixing cache issue
	tempDate = new Date ();

    newURL = "/cgi-bin/gx.cgi/AppLogic+FIRSTCALL.APPS.FC622.FCStoredSearch.FCStoredSearchInterface?CMD=SAVE_DIALOG&DONT_CACHE=" + tempDate.getTime();
    window.open(newURL, "savesearch", "width=500,height=150");
}

// send form parameters to saved search applogic to save them.
function saveSearch(myname)
{
    newURL = "/cgi-bin/gx.cgi/AppLogic+FIRSTCALL.APPS.FC622.FCStoredSearch.FCStoredSearchInterface";
    document.forms.advSearch.action = newURL;
    document.forms.advSearch.CMD.value="SAVEAS";
    document.forms.advSearch.NEW_SS_NAME.value=myname;
    document.forms.advSearch.submit();
}

function runSearch()
{
    document.forms.advSearch.action="/cgi-bin/gx.cgi/AppLogic+FIRSTCALL.APPS.FC622.FCSearch.Searcher";
    document.advSearch.submit();
}

function deleteCrit(token, value)
{
	 // add timestamp to URL so that URL will appear different to
	// IE browser, thus fixing cache issue
	tempDate = new Date ();

	document.advSearch.action = "/cgi-bin/gx.cgi/AppLogic+FIRSTCALL.APPS.FC622.FCAdvancedSearch.FCAdvancedSearchInterface?DONT_CACHE=" + (tempDate.getTime());
	document.advSearch.DELETE_TOKEN.value = token;
	document.advSearch.DELETE_VALUE.value = value;
	document.advSearch.CMD.value = "DELETECRIT";
	
	document.advSearch.submit();
}

function printMsg()
{
	msg = "";
	if (msg != "")
	{
		alert (msg);
	}
}
</script>
</HEAD>

<BODY bgcolor="white" onLoad="printMsg(); return true;">
<form method="post" name="advSearch" action="/cgi-bin/gx.cgi/AppLogic+FIRSTCALL.APPS.FC622.FCSearch.Searcher">


<input type="hidden" name="DATERANGE" value="rel">

<input type="hidden" name="DATEFROM" value="0">

<input type="hidden" name="TFNID" value="200">

<input type="hidden" name="COM" value="AA">

<input type="hidden" name="SUB" value="REC,INI,">

<input type="hidden" name="MAXDATA" value="300">

<input type="hidden" name="PRODUCT" value="PRDEN,PFCUSAL,PFCUSEN,">

<input type="hidden" name="PRIMARY" value="on">

<input type="hidden" name="DATETO" value="730">

<input type="hidden" name="COM2" value="1">


<input type="hidden" name="NEW_SS_NAME" value="">
<input type="hidden" name="NEW_SS_ID" value="-1">
<input type="hidden" name="CMD" value="">

<input type="hidden" name="DELETE_TOKEN" value="">
<input type="hidden" name="DELETE_VALUE" value="">
<input type="hidden" name="BUILD_DEFAULTS" value="">

<input type="hidden" name="addWebToken" value="">
<input type="hidden" name="addWebValue" value="">
<input type="hidden" name="addWebFlag" value="">
<input type="hidden" name="inc_exc_flag" value="">
<input type="hidden" name="OFFSET" value="">
<SCRIPT>
x = new Date();
currentTimeZoneOffsetInHours = x.getTimezoneOffset()/60;
document.advSearch.OFFSET.value = currentTimeZoneOffsetInHours;
</SCRIPT>

  <table border="0" width="500" cellspacing="0" cellpadding="0">
    <tr>
      <td width="294"> <span class="smblktxt8pt">Your Current Advanced Search Criteria:</span></td>
      <td width="105"> </td>
      <td>/FIRSTCALL/APPS/FC622/eng/images/search.gif</td>
      <td>/FIRSTCALL/APPS/FC622/eng/images/savesearch.gif</td>
    </tr>
  </table>




  <table border="0" cellpadding="0" cellspacing="0">

    <tr>
      <td class="smblktxt8pt" width="45" bgcolor="#C0C0C0"></td>
      <td class="smblktxt8pt" width="53" bgcolor="#C0C0C0"></td>
      <td class="smblktxt8pt" width="150" bgcolor="#C0C0C0">Criteria</td>
      <td class="smblktxt8pt" width="303" bgcolor="#C0C0C0">Value</td>
      <td class="smblktxt8pt" width="147" bgcolor="#C0C0C0">Included or Excluded:</td>
    </tr>

    <tr>
      <td></td>
      <td></td>
      <td class="smblktxt8pt">Products searched</td>
      <td class="smblktxt8pt">Equity Reports, Alerts, Equity Notes/News</td>
      <td class="smblktxt8pt">Included</td>
    </tr>


    <tr>
      <td>
	    
      </td>
      <td>
      /FIRSTCALL/APPS/FC622/eng/images/deletesm.gif
      </td>
      <td class="smblktxt8pt">Ticker/Company</td>
      <td class="smblktxt8pt">AA</td>
      <td class="smblktxt8pt">
      Included      
	  
      </td>
    </tr>

    <tr>
      <td>
	    
      </td>
      <td>
      /FIRSTCALL/APPS/FC622/eng/images/deletesm.gif
      </td>
      <td class="smblktxt8pt">Subject</td>
      <td class="smblktxt8pt">Change in Recommendation</td>
      <td class="smblktxt8pt">
      Included      
	  
      </td>
    </tr>

    <tr>
      <td>
	    
      </td>
      <td>
      /FIRSTCALL/APPS/FC622/eng/images/deletesm.gif
      </td>
      <td class="smblktxt8pt">Subject</td>
      <td class="smblktxt8pt">Initiating Coverage</td>
      <td class="smblktxt8pt">
      Included      
	  
      </td>
    </tr>

    <tr>
      <td>
	    
	      /FIRSTCALL/APPS/FC622/eng/images/editsm.gif
        
      </td>
      <td>
      /FIRSTCALL/APPS/FC622/eng/images/deletesm.gif
      </td>
      <td class="smblktxt8pt">Number of Headlines</td>
      <td class="smblktxt8pt">300</td>
      <td class="smblktxt8pt">
      Included      
	  
      </td>
    </tr>

    <tr>
      <td>
	    
	      /FIRSTCALL/APPS/FC622/eng/images/editsm.gif
        
      </td>
      <td>
      /FIRSTCALL/APPS/FC622/eng/images/deletesm.gif
      </td>
      <td class="smblktxt8pt">Date Range</td>
      <td class="smblktxt8pt">0 days back to 730 days back</td>
      <td class="smblktxt8pt">
      Included      
	  
      </td>
    </tr>


  </table>
</form>
</BODY>
</HTML>
 
Upvote 0
Well, I can't test it but you could try this. You will need to create a UserForm and drop a Webbrowser control on it.

Code: 
Dim navdone As Boolean 
Dim mdata As String 

Private Sub UserForm_Activate() 

    Do 
        DoEvents 
    Loop Until navdone = True 
    
    Do 
        DoEvents 
    Loop Until InStr(WebBrowser1.Document.documentelement.innerhtml, "deletesm.gif") <> 0 

    navdone = False 

    document.forms.advSearch.action="/cgi-bin/gx.cgi/AppLogic+FIRSTCALL.APPS.FC622.FCSearch.Searcher"
    document.advSearch.submit()
    
    Do 
        DoEvents 
    Loop Until navdone = True 
    
    Do 
        DoEvents 
    Loop Until InStr(WebBrowser1.Document.documentelement.innerhtml, "Something unique on the page") <> 0 
    
    mydata = WebBrowser1.Document.documentelement.innerhtml 

    'Add code to parse your currency exchange data from the mydata string here 

End Sub 

Private Sub UserForm_Initialize() 

    navdone = False 
    
    WebBrowser1.Navigate "Your URL Here" 

End Sub 

Private Sub WebBrowser1_NavigateComplete2(ByVal pDisp As Object, URL As Variant) 

    navdone = True 

End Sub

-Mike
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question Question
Create data connection to javascript url
Replies
1
Views
274
PeteWright
PeteWright
T
  • Question Question
How do I write Code for an API key call using Blank Query on legacy Excel/Mac
Replies
1
Views
52
Ron de Bruin
Ron de Bruin
M
  • Question Question
Hit a wall with aggregate function
Replies
0
Views
103
MZING81
M
N
  • Question Question
How to import specific data from Windows Explorer details list? (Image focal length information)
Replies
1
Views
140
felixstraube
F
E
  • Solved
Deleting contents of Json object in VBA
Replies
5
Views
245
Edsancar
E

Forum statistics

Threads
1,224,564
Messages
6,179,543
Members
452,924
Latest member
JackiG

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top