asked on

Executing Javascript Function from VBA

I am trying to download an Excel File from a company website. I am able to navigate to the correct WebPage but once I get there I don't know what to do. There is a form at the top of the webpage with buttons that act as references to JavaScript functions. I would like to select the Excel button and save the excel download to a temporary file. I see that the I need to trigger/ execute the runthefex1(Option1) javascript function below but I don't know how to refer to it in the VBA CODE (See VBA CODE Below) Thank you so much.
************************************ HTML SOURCE ************************************************
<html>
<head>
<Script Language="JavaScript">

</Script>
<title>[Untitled]</title>
</head>
<body>

<table width="100% align="left" border="0">
<tr><td width="100%">
<form name="Other">
<a href="javascript:runthefex1('2')">
<img src="/images/exl2000.gif"></a>
<img src="/images/rptbtn.gif"></a>
</tr>

******************************************************* VBA ***************************************
Public Sub GetExcelReport()

Dim oIE As Object
Set oIE = CreateObject("internetexplorer.application")
sUrl = "Some URL"
oIE.Navigate2 sUrl
oIE.Visible = True

Do While oIE.Busy
VBA.DoEvents
Loop
Do While oIE.ReadyState <> 4
VBA.DoEvents
Loop

'***********************************************
DownLoad the Excel Report. 'NEED HELP

End Sub

justyndallmann

I'm not positive on this, but if you try one of the below lines, I think they should work. If after clicking the button it bring up a save as dialog box, I'm not sure what you can do with that other than maybe sendkeys which usually isn't a good solution.

oIE.Document(0).Forms(0).Item("hidevar1").Click
or
oIE.Document(0).Forms(0).Item("hidevar1").Submit

If that doesn't work, I think that the button should be named somewhere in the source, but I don't see it above. In that case you would replace "hidevar1" with the name of the button. Hope that works for you.

ASKER CERTIFIED SOLUTION

Emad Gawai

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cations

ASKER

Thanks so much for the responses.... I have managed to call the function. Excellent.

Now the problem is how do I automatically save it to my harddrive without being prompted to save it and for the directory to save it in?

Thanks so much Expets Exchange

Emad Gawai

users interactivity is required to save the file to their computer harddrive. you cant save any file directly from the website

cations

ASKER

You can I implemented this into my macro.

I get the URL that the Excel File has when it is opened in the webbrowser and then I use this URL to open it within Excel.