Can I call an Excel VBA function from within a WebBrowser control?

[John Brock]

I have an Excel workbook which can pop up a form that contains a
WebBrowser control. When the user clicks on a button in the web
page that's being displayed in the control, I want a JavaScript
function in the page to call one of the VBA functions in the workbook
and pass it some arguments. Can anyone tell me whether this is
possible, and if so how to do it?

One reason I think it may be possible is that I already know how
to call back to a VB.NET program from a .NET WebBrowser control,
by using the control's ObjectForScripting property and calling
window.external.whatever() in the JavaScript code. It's possible
this is a new feature with .NET, but I am hoping there is some way
to do the same thing from a VBA WebBrowser control, without .NET
being involved.

 

[joel]

Wouldn't the parent of the web browser control be the workbook

 

[John Brock]

Wouldn't the parent of the web browser control be the workbook?

I guess so.

But how do I get access to that parent in the JavaScript code? I
found one link where someone claimed to be doing something like
this (with PowerPoint):

http://www.pcreview.co.uk/forums/thread-3675697.php

But when I adapt for Excel the JavaScript function used in that
link:

function CallVBA(VBAMacroName)
{
var App;

App = new ActiveXObject("Excel.Application");
App.Run(VBAMacroName);
}

what it does is open up a new instance of Excel, which doesn't
help. I need the App variable to point to the already open instance
of Excel that opened up the WebBrowser, but that's what I can't
figure out. It may be that there is a very simple way to do this,
and if so (or even if it's complicated!) I hope someone will
enlighten me.

 

[joel]

I looked at the source code for you posting and found this


onclick='javascript:this.parentNode.parentNode.style.display="none";



I believe if you displayed in Java "this.parentNode.Name" it would b
the workbook name. If not then try the 2nd parent

"this.parentNode.parentNode.Name"

Once you find the workbook name then the application is what you nee
to run


from
App.Run(VBAMacroName);
to
this.parentNode.Application.Run(VBAMacroName);


what also may work is
this.Application.Run(VBAMacroName);


I not an expert on Java and not sure how to test my theory

 

[John Brock]

Thanks for the suggestions, but unfortunately they just seem to
throw errors.

Where did you get the "onclick=..." line??? It doesn't seem to be
in the link I posted.

 

[joel]

Debug the code by add messge boxes. You need to verify the
I just went to the IE menu View - Source while responding to this emai
and searched for the word "THIS"

Inot a java programmer and not familarr with th eobects in Java.
Itried a few things using the IE Developer tools but didn't get very fa

 

[Tim Williams]

I've tried this without success in the past, but your question got me
searching again....

Here's a page explaining how to fire a method in VB from javascript in a
hosted browser control:
http://msdn.microsoft.com/en-us/library/aa752045(VS.85).aspx

It's a bit complex, but easy enough to follow: you can just copy and paste
the code from the link.

One difference in VBA: you can't set the default member of a class via the
Object browser in VBA, so use the method described here:
http://www.cpearson.com/excel/DefaultMember.aspx
It involves exporting the class to a file, then editing and re-importing it.


Worked for me when I tried in XL. I can send you my file if you're having
problems.

Tim

timjwilliams
at
gmail dot com

 

[joel]

Tim : Just for curiosity sake. Can a Java Script inside the webpage get
workbook application object that started the web application?

 

[Tim Williams]

Hi Joel,

I haven't tried it, but if it were possible it would be a pretty big
security problem: once you have a reference to the Excel application
object, you could use it to do all sorts of things...

The links I posted show how to call a method in excel from the web
page, but I'm not sure if there's any support for passing parameters
or returning values or objects to the page. I'll check it out when I
have some time.


Tim