Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4546
  • Last Modified:

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">
<!--
 
function runthefex1(option1){
 document.hidevar1.FMT.value=option1;
 document.hidevar1.submit();
}
 
 
//-->
</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
0
cations
Asked:
cations
  • 2
  • 2
1 Solution
 
justyndallmannCommented:
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.
0
 
GawaiCommented:
you can just add javascript function inside the vbscript function or sub.

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
   
'call javascript function here
runthefex1(option1)

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

End Sub

0
 
cationsAuthor Commented:
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
0
 
GawaiCommented:
users interactivity is required to save the  file to their computer harddrive. you cant save any file directly from the website
0
 
cationsAuthor Commented:
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.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now